Mastering Flash‑Sale (秒杀) Logic: Using Unique Indexes and SQL for Concurrency Control

Scenario 1: Single‑Item Flash Sale Controlled by a Unique Index

The first scenario handles a flash‑sale where many users compete for a single product. Two tables are used:

commodity : stores the products that can be seized during the flash‑sale.

commodity_log : records which users successfully obtained which product.

During initialization five products (IDs 1‑5) are inserted into commodity, and a unique index is created on the commodity_log table to ensure that each user can only insert one successful record per product.

When user 20 attempts to seize product 1, the process follows these steps (illustrated in the following diagram):

If the operation completes without exceptions, the purchase succeeds; otherwise it fails, as shown in the subsequent screenshots.

This method is simple to implement but suffers from blocking under high concurrency because the unique index can become a contention point.

Scenario 2: Multi‑Item Flash Sale with Quantity Tracking

The second scenario extends the model so that each product has a limited stock. The commodity table now includes a Quantity column indicating the total number of items available for the flash‑sale. The commodity_log table adds a state column (0 = failed, 1 = successful) to record the outcome of each attempt.

The core logic proceeds as follows (illustrated below):

The algorithm first checks whether the number of successful logs already reaches the product’s total quantity; if so, it aborts immediately. If not, it inserts a new log entry. Because no explicit locking is used, concurrent inserts can exceed the stock, so the system relies on the auto‑increment ID of commodity_log to infer the order of operations.

When a purchase succeeds, the product’s status is updated. This lock‑free approach eliminates blocking but dramatically increases the number of SQL statements executed:

Failed attempts may involve one SELECT or two SELECT statements plus an INSERT.

Successful attempts involve two SELECT statements, one INSERT, and one UPDATE.

While the method avoids locks, the overhead of many SQL statements can become a performance concern.

Optimized Version

The author later refined the implementation by removing explicit transaction commits and moving control logic into the application layer, further reducing database round‑trips.

Overall, the two approaches illustrate the trade‑off between simplicity (using a unique index) and scalability (lock‑free, quantity‑aware logic) when designing a flash‑sale system.