Designing a High‑Performance, Consistent Flash‑Sale System with MySQL

Problem Origin

Flash‑sale (秒杀) systems require extremely high throughput, strong consistency (no overselling), and per‑user purchase limits. The discussion assumes MySQL InnoDB.

Naïve Design (No Consistency Guarantees)

Table Structure

A junction table UserDeal records user_id, deal_id and buy_count.

Basic Operations

Before inserting a purchase record the application checks:

Per‑user limit: SELECT SUM(buy_count) FROM UserDeal WHERE deal_id = ? Duplicate purchase:

SELECT COUNT(*) FROM UserDeal WHERE user_id = ? AND deal_id = ?

If both checks pass, insert:

INSERT INTO UserDeal (user_id, deal_id, buy_count) VALUES (?, ?, ?);

Issues

Concurrent purchases can both see remaining stock and oversell.

Concurrent requests from the same user can bypass the duplicate‑check, creating duplicate rows.

Ensuring Single Purchase per User

Add a unique index on (user_id, deal_id):

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

The index prevents duplicate inserts and improves query performance; the application should catch the duplicate‑key error.

Preventing Oversell with Transactions

Row‑Level Lock

Wrap the checks and insert in a transaction and lock the rows:

START TRANSACTION;
SELECT SUM(buy_count) FROM UserDeal WHERE deal_id = ? FOR UPDATE;
SELECT COUNT(*) FROM UserDeal WHERE user_id = ? AND deal_id = ?;
INSERT INTO UserDeal (user_id, deal_id, buy_count) VALUES (?, ?, ?);
COMMIT;

The FOR UPDATE clause acquires a row lock, making the operation atomic.

Index Requirement

If deal_id is not indexed, InnoDB may fall back to a table lock. Create the index:

ALTER TABLE UserDeal ADD INDEX ix_deal_id (deal_id);

Performance vs. Consistency Trade‑off

Under massive concurrency (tens of thousands of users) the transactional approach can become a bottleneck. Removing the transaction improves throughput but sacrifices strict consistency.

Performance‑First Design (Accepting Minor Inconsistency)

Atomic Stock Update

Store the current sold count in the Deal table and update it atomically:

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

The statement returns the number of affected rows; 1 means the purchase succeeded, 0 means out of stock.

Rollback on Failure

If inserting into UserDeal fails (e.g., duplicate purchase), decrement the stock:

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

Complete Flow

Execute the atomic UPDATE Deal statement.

If affected rows = 1, insert a row into UserDeal.

If the insert fails, run the rollback UPDATE Deal to restore the count.

This approach guarantees that oversell does not occur; however, it does not guarantee that the “who bought what” information is always consistent in the presence of failures.

Typical Outcomes

Successful purchase.

Out‑of‑stock (no rows updated).

Rollback succeeded after duplicate‑purchase error.

Rare inventory loss due to network or DB failure.

Guidance

The transactional solution with row‑level locking is sufficient for most flash‑sale scenarios. Only when the system must handle nationwide spikes of hundreds of thousands of concurrent users should the performance‑first design be considered, with an understanding of the acceptable inconsistency.