MySQL FOR UPDATE: hold the lock while the decision is being made

An application locks a row, reads its value, ends the transaction, and only then makes the update. The lock no longer protects the decision. Another request can change the row between the read and the write.

Keep the critical operation together

START TRANSACTION;
SELECT stock FROM products WHERE id = 204 FOR UPDATE;
-- Application checks the returned stock while this transaction stays open.
-- If insufficient, ROLLBACK instead of running the next statement.
UPDATE products SET stock = stock - 1 WHERE id = 204;
COMMIT;

This is a transaction sketch, not a script to run blindly: the application must branch on the selected stock and roll back when there is insufficient inventory. Prefer a conditional atomic update when the operation is only a guarded decrement.

Keep the protected section small

Do not call a slow supplier API while holding the row lock. Fetch external information before the transaction where appropriate, then verify the business condition again inside the transaction.

The suitable locking strategy depends on the indexed predicate and isolation level. A query without an appropriate index may protect a much broader range than expected. Inspect the query plan and the actual contention.

Design the failure path

Deadlocks are possible even in a carefully designed application. Use a consistent order when multiple rows are involved and keep bounded retry behavior at a layer that can safely repeat the operation.

Never repeat an external charge simply because a database transaction was retried. Separate durable intent, external effects, and acknowledgement.

Test with separate MySQL connections, including an intentionally competing transaction. A test that only reads back the final stock after one request says little about the lock's lifetime. MySQL's locking-read documentation explains transaction requirements and the protection provided by FOR UPDATE.