How MySQL's SELECT FOR UPDATE Prevents Overselling in Concurrent Transactions

A common race condition in online stores occurs when two customers simultaneously attempt to purchase the last item in stock, with both transactions reading the same available quantity before either completes. Standard SELECT queries in MySQL use non-locking reads, meaning even under the default REPEATABLE READ isolation level, they do not prevent another transaction from modifying a row after it has been read. MySQL's SELECT FOR UPDATE addresses this by placing an exclusive lock on the queried row, forcing any competing transaction to wait until the first one commits or rolls back. This ensures that the read, condition check, and update happen as a protected sequence rather than as separate, interruptible steps. Replacing plain SELECT with SELECT FOR UPDATE in purchase flows is a practical way to eliminate overselling without restructuring application logic.