Shopify reserves checkout stock in MySQL by locking one row per unit

A dev.to analysis of Shopify's engineering account describes the fix for the hot row as a change in what gets locked. Each available unit gets its own row in an eligible pool, and a reservation takes as many rows as the quantity it needs ^[9]. Two checkouts for the same item can then lock separate units instead of contending for one counter ^[9]. Selection stays inside the eligible item and location group. It cannot borrow a row from an ineligible group ^[8]. The ledger can hold more stock than the pool's cap ^[2]. We think the bounded pool is good engineering. The set of lockable rows stays small, and the ledger remains the source of truth ^[6].

SKIP LOCKED is what sends concurrent checkouts to different rows. According to MySQL's locking-read documentation, the option excludes rows whose locks cannot be acquired immediately and returns other rows instead of waiting ^[10]. The manual describes it as a mechanism for queue-like tables ^[10]. It also warns that queries which skip locked rows return an inconsistent view of the data ^[11].

The transaction boundary matters as much as the schema. Reserve and claim each run as short database transactions ^[13]. The reserve transaction releases its row locks, and the stored reservation state keeps the hold in place while payment is processed ^[13]. In our view this is the right call for checkout. Holding a unit's lock for the length of a payment would put the next buyer back in a queue. The cost is a hold that outlives its lock. The application has to release abandoned attempts without leaving stock permanently unavailable ^[14].

The connection limit fits the same picture. SKIP LOCKED governs row-lock acquisition only. Other locks, connection queues and replenishment coordination can still make a request wait ^[15]. We'd expect a checkout that keeps a pooled connection open during slow work elsewhere to hold up reservations, however fast the reservation query itself runs. The write-up frames the case as an interaction between schema, transaction boundaries and connection occupancy ^[19].

The write-up does not include throughput or latency figures. Its four-unit diagram is a conceptual illustration and does not show a measured Shopify inventory count ^[18]. For the gains to carry over to another checkout system, two things have to be true. Contention has to spread across many units of the same item at the same location ^[9]. And the rest of checkout has to return database connections as promptly as the reservation transactions do ^[3].