跳到正文
DEV Community· Pavitar Kumar·· 5 小時前AI 評分34

深入剖析 Shopify 使用 MySQL 管理庫存(第一部分)

Deep Dive into Shopify using mySQL for inventory Part 1

AI 導讀

Shopify 以 MySQL 管理庫存時,將每個 SKU 的庫存數量拆分成獨立資料列,以應對交易更新造成的競爭。文章分析庫存預留的原子性問題、InnoDB 的鎖定與索引設計,並説明 Shopify 採用由 shop_id、inventory_item_id 和 inventory_group_id 等欄位組成的複合主鍵。

正文

Hi

I am Pavitar. A Software Engineer with knack for understanding and owning systems. You will see me larping about Distributed systems in my LeetCode t-shirt, bragging about my Leetcode achievements (not many yet), grinding on Codeforces and writing deep dives on Concurrency and Systems concepts.

Feel free to ping me on LinkedIn if you are also intrigued by the intricacies of computer systems.

What's the agenda?

We are going to try to understand the design decisions made in this blog post.

Read it first.

Shopify Engineering Blog: https://shopify.engineering/scaling-inventory-reservations

I have been seeing lots of post on LinkedIn about this migration blog. I also saw comments asking some deep question. So, I thought of starting my blogging journey with a deep dive on how Shopify did it. In my humble opinion, their article does an amazing job of hiding the complex decision made and the reasoning behind it.

Let's first list down the problem of Inventory reservation, which Shopify was trying to do better on.

All E-commerce apps need to reserve inventory when you make a purchase. Normal purchase flow is Customer adding some X SKUs (items) in varying quantity to my cart. Customer clicks Pay/Checkout and these items are reserved unless I complete the purchase or the transaction times out (let's say 15 minutes after I clicked buy now).

This ain't the only way though. There are other ways to achieve this, go explore some books or ask your favourite AI agent.

The Classic Pattern

Before this migration, Shopify used the classic pattern of Redis as the quantity counter and SQL (mySQL here) database as ultimate source of truth.

Reserving some items means you run a LUA script. Restore if the transaction didn't finish.

One main issue with this is quite classic. How do you make the operation atomic?

Let's try two approach.

  1. When user checkout, Redis counter is permanently reduced. A transactionId is stored in database and a separate job keeps checking recent transactionId to restore related quantity. What if server died after decrementing inventory but transaction is not created.
  2. In reverse order, you created a transactionId in MySQL and your server died. Now, the background job may add more inventory than you have back to redis, allowing payment for a item that you cannot fulfill.

See. It is not as easy as those Youtube Videos seems to imply.

But why? My single server eCommerce app has no such issues when I started to learn (way before AI, lucky me)

So, What to do?

I mean, you can always get an ice-cream and forget that issues happen. We will care, when they happen. Let's just ship.

Ok, seriously. What if mySQL could be used? But everyone knows that single row for each SKU doesn't scale.

Why it doesn't scale?

Each row need to be locked. Suppose 100 people simultaneously want Wired Headphones (I want one). The query would be something like:

BEGIN TRANSACTION;

// Reduce the product quantity
UPDATE product_table
SET quantity = quantity - 1;
WHERE product_id = <PRODUCT_ID>
AND quantity > 0;

// Insert this quantity to some other table, likely a outbox table


COMMIT;

This query looks innocent. But it's not. mySQL (InnoDB) has REPEATBALE_READ as default isolation level.

Under REPEATABLE READ InnoDB ensures that this update is reproducible; it will always affect the same records because it blocks any other statements from impacting the results. While this transaction is open, no other client may INSERT, UPDATE or DELETE a record whose value overlaps this query.

The consequences of the above paragraphs are large. Transaction wait in line to update a SKU. If you purchased 3 SKUs, you are amplify the waiting by 3 rows. More rows you touch, more contention you cause.

Sounds boring, right? We will see shortly.

First Mission: Transaction Contention

Shopify Engineers did something clever. It is so obvious but you won't think of it unless you (not your AI model 😝) have deep knowledge of how Databases work under the hood (read some books, boring things). Shopify split the the SKU quantity into individual rows.

But this is not without problems. Listing some obvious problems.

  1. Table/Index Bloat.
  2. Page Buffers Overflow
  3. I know more, but I won't bore you with jargons. ( Showing off here 😎)

Before we get to how they solved some of these, let's understand their Database Schema for the product table.

They have shop_id , inventory_item_id and inventory_group_id as main fields mentioned.

This is inventory management system. So, you need to have the following queries fast:

  1. List all products of a shop (list of inventory_item_id and details).
  2. Check if a item is out of stock.

There is a obvious hierarchy here. First thing you will search with is shop_id and ask for a specific inventory_item_id. I don't have any clue about usage of inventory_group_id, so I will just keep it at last.

This is where Composite Primary Key fits in. It is created with shop_id , inventory_item_id and inventory_group_id in the same order + ID field (why? Keep reading)

Side Quest: Clustered and Non-Clustered Indexes

Let me speed this up a bit. All the rows need to have a natural order for database to store. If you don't provide a Primay key, it creates it's own internal Primary Key as it is needed to store the data in some order.

This Primary Key is used to create Clustered Index. Clustered Index defines the order in which rows are ordered when stored (physically in the B-tree Pages).

A non-clustered Index is just index on a specific column with pointers to Clustered Index. Thus, row insertion, deletion or updation need to update the clustered index as well as the non-clustered indexes. (See, this is why indexes make write slower databases)

Shopify team mentions that they tried using UUID before settling for the above Composite Primary Key.

Why wouldn't UUID work? Well, there are quite a few issues:

  1. Append order is not predictable.
  2. Each item quantity may get scattered across multiple pages far away.
  3. Two locks needed. Why? Well, you lock the non-clustered index (shop_id , inventory_item_id and inventory_group_id) and the clustered index (UUID)

So, the stage is set for a composite index. But why would the last part of composite key be id?
To satisfy the Uniqueness constraints of Clustered index. And to count the quantities, you cannot have more than a specific number of rows per item. Else, think about 10k rows for a low value high moving item. it will fill up so many pages.

Too much info. Summarise it

I see. Too addicted to those summarisation by AI. Let's go through what we know:

  1. Indexes are not always good. Know when not to use one. They speed up reads at the cost of writes and storage.
  2. Clustered Indexes and non-clustered Indexes. Time based IDs are better as primary key. (think UUIDv7)
  3. Composite Primary key need to be ordered properly. If shop_id is leftmost part, index can be used as for shopId as well. If not, good luck creating new index to search by shop_id
  4. Transaction Isolation Levels are great knobs in SQL databases. Know when to turn one.

Come Back for Part 2

Too much info to digest, right? Let me give you a break and some reference resources to go through, before we get to the 2nd Part.

My favorite is Database Internals by Alex Petrov. Read the first section. Take your time.

If curious, read upto transaction management. You will understand deeply about how row locking work.

As for how InnoDB locking work, Check out this Blog (Mentioned by Shopify Blog)

來源:DEV Community · dev.to