Imagine you’re running an online store, there’s exactly one item left in stock. Two customers click Buy at almost exactly the same time. Both requests reach your application. Both transactions ask the database:
SELECT stock FROM products WHERE id = 10;
And both get:
stock = 1
Now both customers believe the product is available.
So what happens next ? Welcome to the overselling problem.
The Naive Implementation
A simple purchase flow might look like this:
START TRANSACTION;
SELECT stock FROM products WHERE id = 10;
-- Application checks:
-- Is stock > 0 ?
UPDATE products SET stock = stock - 1 WHERE id = 10;
INSERT INTO orders (product_id, user_id) VALUES (10, 1001);
COMMIT;
At first glance, this seems perfectly reasonable. But there’s a race condition.
Imagine two transactions:
Transaction A Transaction B
SELECT stock SELECT stock
| |
v v
stock=1 stock=1
| |
v v
"Available!" "Available!"
Both transactions read the same value before either one has completed the purchase. The problem isn’t necessarily that MySQL is broken. The problem is that our read and decision are not protected as one atomic operation.
Why Doesn’t REPEATABLE READ Solve This ?
This is an important question. InnoDB’s default isolation level is REPEATABLE READ. So you might think:
“If I’m using REPEATABLE READ, shouldn’t MySQL prevent this ?”
Not necessarily.
A normal:
SELECT ...
is a consistent, nonlocking read.
Under REPEATABLE READ, it can read from the transaction's consistent snapshot.
Isolation determines what your transaction sees. It doesn’t automatically mean:
“Nobody else can modify the row after I read it.”
That’s a completely different requirement.
If your business operation is:
“Read this row, verify a condition, and then modify it.”
you often need a locking read.
Enter SELECT … FOR UPDATE
MySQL provides:
SELECT ... FOR UPDATE
For example:
START TRANSACTION;
SELECT stock FROM products WHERE id = 10 FOR UPDATE;
This is not just a normal read. It is a locking read.
MySQL/InnoDB locks the records returned by the query, and another transaction attempting to acquire a conflicting lock on the same records has to wait until the first transaction commits or rolls back.
Now our two transactions look very different.
Transaction A Gets There First
Suppose Transaction A executes:
START TRANSACTION;
SELECT stock FROM products WHERE id = 10 FOR UPDATE;
The database finds:
stock = 1
and locks the relevant record.
Transaction B now tries:
SELECT stock FROM products WHERE id = 10 FOR UPDATE;
But Transaction A is already holding the conflicting lock.
So Transaction B waits.
Transaction A Transaction B
FOR UPDATE
|
v
stock = 1
|
LOCK ROW
|
| FOR UPDATE
| |
| v
| WAIT...
|
UPDATE stock = 0
|
INSERT ORDER
|
COMMIT
|
UNLOCK
|
v
FOR UPDATE succeeds
Now Transaction B gets its turn.
The important part is that Transaction B doesn’t get to make its decision before Transaction A finishes.
The Correct Purchase Flow
A safer implementation looks like this:
START TRANSACTION;
SELECT stock FROM products WHERE id = 10 FOR UPDATE;
Then the application checks the returned value:
if stock > 0:
continue purchase
else:
reject purchase
If stock is available:
UPDATE products SET stock = stock - 1 WHERE id = 10;
INSERT INTO orders (product_id, user_id) VALUES (10, 1001);
COMMIT;
If the product is already sold out:
ROLLBACK;
The important property is that the check and subsequent modification happen while the relevant row is locked.
The Key Idea: Lock Before You Decide
This is the mental model worth remembering:
Unsafe:
READ
↓
CHECK
↓
UPDATE
The problem is that another transaction can interfere between the read and the update.
Safer:
LOCK + READ
↓
CHECK
↓
UPDATE
↓
COMMIT
The lock protects the critical section.
But There’s Another Option
For a simple inventory decrement, you don’t always need to read the row first. You can make the condition part of the update itself:
UPDATE products SET stock = stock - 1 WHERE id = 10 AND stock > 0;
Then check how many rows were affected.
If:
affected_rows = 1
the purchase can proceed.
If:
affected_rows = 0
there wasn’t enough stock.
This approach can be extremely useful because the business condition is enforced directly by the database operation.
For example:
START TRANSACTION;
UPDATE products SET stock = stock - 1 WHERE id = 10 AND stock > 0;
-- If one row was updated:
-- create the order
INSERT INTO orders (product_id, user_id) VALUES (10, 1001);
COMMIT;
In a real implementation, the application should only insert the order when the update actually succeeded.
So When Should You Use SELECT … FOR UPDATE ?
SELECT ... FOR UPDATE is particularly useful when your business logic needs to:
- Read the current state of a row.
- Make a decision based on that state.
- Modify that same data.
- Keep another transaction from changing it between those steps.
Common examples include:
- inventory reservation
- seat reservation
- wallet balance updates
- account transfers
- job claiming
- resource allocation
- order processing
For example:
SELECT balance FROM accounts WHERE id = 123 FOR UPDATE;
Then:
Check balance
↓
Calculate new balance
↓
UPDATE account
↓
COMMIT
The lock protects the critical decision.
A Common Misunderstanding
One common misconception is:
“FOR UPDATE locks the entire table."
That’s not generally how InnoDB works.
InnoDB uses row-level locking, although the exact locks acquired depend on the query, indexes, search conditions, and isolation level. For some range queries, InnoDB can also use gap locks or next-key locks.
For example, a unique-index lookup such as:
SELECT stock FROM products WHERE id = 10 FOR UPDATE;
can lock the matching index record.
Range-based locking is more complicated.
For example:
SELECT * FROM products WHERE price BETWEEN 100 AND 200 FOR UPDATE;
may involve range-related locking behavior depending on the indexes and isolation level.
This is one reason understanding indexes is important when reasoning about MySQL concurrency.
Don’t Forget the Transaction
FOR UPDATE makes sense inside a transaction.
For example:
START TRANSACTION;
SELECT stock FROM products WHERE id = 10 FOR UPDATE;
UPDATE products SET stock = stock - 1 WHERE id = 10;
INSERT INTO orders (product_id, user_id) VALUES (10, 1001);
COMMIT;
The lock is part of the transaction’s concurrency control. You don’t want to acquire a lock, perform a tiny operation, release it, and then perform the critical business operation later. The entire critical section should be designed intentionally.
What About Deadlocks ?
Locks solve one class of concurrency problems, but they introduce another possibility: deadlocks.
Imagine:
Transaction A Transaction B
locks Row 1 locks Row 2
| |
v v
tries Row 2 tries Row 1
| |
+---------- WAIT <-------------+
Now both transactions are waiting for each other.
InnoDB detects deadlocks and rolls back one of the transactions so that the other can continue.
This means production applications using transactions and locks should generally be prepared to retry transactions when appropriate.
Locking isn’t something you simply add without considering transaction boundaries, lock ordering, indexes, and failure handling.
The Real Lesson
The important lesson isn’t simply:
“Use SELECT ... FOR UPDATE."
The deeper lesson is:
Concurrency bugs happen when a business decision depends on data that can change between the read and the write.
You need to identify that critical section and choose an appropriate concurrency-control strategy.
Sometimes that’s:
SELECT ... FOR UPDATE
Sometimes it’s an atomic conditional update:
UPDATE ... WHERE stock > 0;
Sometimes it’s an optimistic concurrency strategy. And sometimes a database constraint is the best solution. The correct choice depends on the business operation.
Final Takeaway
When building a system that handles concurrent requests, don’t ask only:
“Which isolation level should I use ?”
Also ask:
“What happens if two transactions execute these exact statements at the same time ?”
That’s the question that exposes race conditions.
For an inventory operation, this:
SELECT stock FROM products WHERE id = 10;
and this:
SELECT stock FROM products WHERE id = 10 FOR UPDATE;
are not equivalent.
The first reads. The second reads with an intention to modify and acquires a lock.
Once you understand that distinction, MySQL transaction isolation becomes much easier to reason about.
And more importantly, you can start designing systems that remain correct even when thousands of users click Buy at exactly the same time.
Top comments (0)