MySQL Overselling: Why SELECT Isn't Enough and How SELECT . . . FOR UPDATE Solves It
A single SELECT can let two customers buy the same item, causing overselling. Using SELECT FOR UPDATE locks the row, ensuring only one transaction can modify the stock at a time. The article explains the problem, the solution, and best practices.
When an online shop runs out of a popular product, two customers may click the buy button at the same moment. Both requests reach the application, each executing a query that reads the current stock level. If the stock is still listed as one, both transactions believe the item is available and proceed to deduct the quantity and create an order. The result? One order is fulfilled, the other is left waiting or fails, but the system has already sold more items than it has in inventory. This phenomenon is known as overselling, and it stems from a race condition between concurrent transactions.
Why a Plain SELECT Fails
In MySQL’s default isolation level, REPEATABLE READ, a plain SELECT is a consistent, non‑locking read. It reads from a snapshot of the database at the start of the transaction, but it does not prevent other transactions from modifying the same row afterward. The typical purchase flow might look like this:
START TRANSACTION;
SELECT stock FROM products WHERE id=10;
-- application checks: stock > 0?
UPDATE products SET stock = stock - 1 WHERE id=10;
INSERT INTO orders (product_id, user_id) VALUES (10, 1001);
COMMIT;
When two transactions run this code simultaneously, they both read the same stock value before either has updated it. The race condition occurs between the read and the update, and MySQL’s isolation level does not protect that window.
Locking the Row with SELECT FOR UPDATE
MySQL offers a locking read: SELECT … FOR UPDATE. This statement acquires a write lock on the rows it returns. If a second transaction attempts to acquire a conflicting lock, it will wait until the first transaction commits or rolls back.
Example:
START TRANSACTION;
SELECT stock FROM products WHERE id=10 FOR UPDATE;
-- the row is now locked by this transaction
IF stock > 0 THEN
UPDATE products SET stock = stock - 1 WHERE id=10;
INSERT INTO orders (product_id, user_id) VALUES (10, 1001);
COMMIT;
ELSE
ROLLBACK;
END IF;
Because the row is locked, the second transaction cannot read or modify it until the first transaction finishes. This guarantees that only one purchase can succeed when the stock is limited to one item.
An Alternative: Conditional UPDATE
In some cases, you can avoid a separate read entirely by embedding the business rule in the UPDATE statement. The UPDATE can include a condition that ensures stock is positive:
START TRANSACTION;
UPDATE products SET stock = stock - 1 WHERE id = 10 AND stock > 0;
IF ROW_COUNT() = 1 THEN
INSERT INTO orders (product_id, user_id) VALUES (10, 1001);
COMMIT;
ELSE
ROLLBACK;
END IF;
This pattern is efficient because it performs the check and the decrement in a single atomic operation. The database enforces the condition, eliminating the need for a separate SELECT.
When to Use SELECT FOR UPDATE
SELECT FOR UPDATE is ideal when:
- You need to read a row’s current state.
- You make a decision based on that state.
- You must modify the same row before another transaction can change it.
Typical scenarios include inventory reservations, seat bookings, wallet balance updates, account transfers, job queue claiming, and order processing.
Understanding Locking Behavior
InnoDB uses row‑level locking, but the exact locks depend on the query, indexes, and isolation level. A simple primary‑key lookup locks the matching index record. Range queries may acquire gap or next‑key locks, which can affect concurrency. Therefore, designing indexes thoughtfully is crucial for predictable locking behavior.
Deadlocks and Transaction Design
While locks prevent overselling, they can introduce deadlocks if two transactions lock resources in opposite order. To mitigate this, keep the critical section short, lock rows in a consistent order, and consider using optimistic concurrency or database constraints when appropriate.
In summary, overselling occurs when a plain SELECT is followed by an UPDATE without a lock, allowing concurrent transactions to interfere. Using SELECT FOR UPDATE or a conditional UPDATE ensures that the read‑modify sequence is atomic, keeping inventory accurate even under high load.
Why it matters
Ensuring accurate inventory prevents lost sales, customer frustration, and financial discrepancies in e‑commerce systems.
Key points
- Plain SELECTs can cause overselling due to race conditions.
- SELECT FOR UPDATE locks rows, preventing concurrent modifications.
- Conditional UPDATEs embed business rules directly in the database operation.
- Row‑level locking depends on indexes and query patterns.
- Deadlocks can arise if locks are acquired in inconsistent order.
- Designing transactions with short, locked critical sections improves reliability.
Frequently asked questions
Does SELECT FOR UPDATE lock the entire table?
No. InnoDB uses row‑level locks; the lock scope depends on the query and indexes.
Can I use SELECT FOR UPDATE without a transaction?
The statement should be executed within a transaction; otherwise the lock is released immediately.
What happens if the UPDATE condition fails?
The UPDATE affects zero rows, and the application can roll back or retry the operation.
