Designing High‑Performance Flash‑Sale (秒杀) Systems with MySQL: Balancing Consistency and Performance

Many developers struggle with flash‑sale (秒杀) designs; the core requirements are high performance, strong consistency (e.g., inventory of 100 must never be sold as 101), and per‑user purchase limits.

Naïve Design Without Consistency Guarantees

Table Structure

+-----------+------------------+------+-----+---------+----------------+
| Field     | Type             | Null | Key | Default | Extra          |
+-----------+------------------+------+-----+---------+----------------+
| id        | int(11) unsigned | NO   | PRI | NULL    | auto_increment |
| user_id   | int(11)          | NO   |     | NULL    |                |
| deal_id   | int(11)          | NO   |     | NULL    |                |
| buy_count | int(11)          | NO   |     | NULL    |                |
+-----------+------------------+------+-----+---------+----------------+

The table simply records which user bought how many items. The naïve workflow checks the incoming buy_count against per‑user limits, sums existing purchases with SELECT SUM(buy_count) FROM UserDeal WHERE deal_id = ?, verifies the user hasn't bought before, and finally inserts a new row.

Problems with the Naïve Approach

When inventory is low, concurrent requests can both pass the checks and cause overselling; a single user sending two simultaneous requests can also lead to duplicate purchases.

Ensuring a Single User Cannot Purchase Multiple Times

Adding a unique index on (user_id, deal_id) makes the check atomic and lets the database reject duplicate inserts.

ALTER TABLE UserDeal ADD UNIQUE user_id_deal_id(user_id, deal_id);

Application code should catch the duplicate‑key exception and present a friendly message.

Preventing Overselling with Transactions

Solution

Wrap the checks and insert in a transaction and lock the rows with FOR UPDATE:

SELECT SUM(buy_count) FROM UserDeal WHERE deal_id = ? FOR UPDATE;

Because the original SELECT lacked FOR UPDATE, it did not acquire row locks. Adding an index on deal_id ensures the lock is row‑level rather than a table lock.

ALTER TABLE UserDeal ADD INDEX ix_deal_id(deal_id);

Performance vs. Consistency Trade‑off

When the traffic reaches tens of thousands of concurrent users, the transaction‑based solution may become a bottleneck. One can sacrifice strict consistency for higher throughput.

Design Without Transactions

Remove the transaction and rely on an atomic UPDATE on the Deal table that stores the remaining stock ( buy_max) directly:

UPDATE Deal SET buy_count = buy_count + 1 WHERE id = ? AND buy_count + 1 <= buy_max;

The statement acquires a row lock; the first buy_max successful requests affect the row (affected‑rows = 1), the rest receive 0 affected rows, indicating failure.

After a successful update, insert a record into UserDeal. If the insert fails (duplicate purchase), roll back the stock by decrementing buy_count:

UPDATE Deal SET buy_count = buy_count - 1 WHERE id = ? AND buy_count - 1 >= 0;

Remaining Inconsistencies

This approach tracks the remaining quantity but does not record who bought it atomically; a failure after the stock update could lead to a small amount of “lost inventory” in case of network or DB failures.

When to Use Which Solution

If your business can tolerate occasional lost inventory or a small amount of overselling, the lock‑free UPDATE method may be sufficient. Otherwise, the transaction‑based solution with row‑level locking is recommended.

Final Advice

Do not over‑optimize; the first transaction‑based design already provides a good balance. Only consider sacrificing consistency when you truly expect massive concurrent flash‑sale traffic (e.g., nationwide events like Double‑11).

Recommended further reading:

What Does "State" Mean in Stateless HTTP?

MySQL Optimization Principles You Must Know

From Backend Architecture Evolution to Cloud‑Native: A Complete Guide