Database Transactions and Locking in MySQL: Avoiding Race Conditions

Most applications run fine for a long time without anyone thinking hard about concurrency, right up until two requests touch the same row at nearly the same moment and the numbers stop adding up. Inventory counts go negative, double-spent credits appear, or a balance update silently overwrites another one. Transactions and locking are the tools for making sure that does not happen, but they only help if used correctly.

What a Transaction Actually Guarantees

A transaction groups statements so they either all commit or all roll back together. That solves atomicity, but it does not by itself solve concurrency between two separate transactions touching the same data. Two transactions can each read a stock count of 5, each decrement it by 3, and each commit — leaving the final value at 2 instead of the correct -1 that should have triggered an out-of-stock error. Transactions prevent partial writes; they do not automatically prevent this kind of lost update.

START TRANSACTION;

SELECT stock FROM products WHERE id = 42;
-- application checks stock >= quantity, then:
UPDATE products SET stock = stock - 3 WHERE id = 42;

COMMIT;

Without an explicit lock, the SELECT here does not stop a second transaction from reading the same stale value before this one commits.

Pessimistic Locking with SELECT ... FOR UPDATE

Pessimistic locking assumes contention is likely and blocks other transactions from reading the locked row for update until the current transaction finishes. SELECT ... FOR UPDATE acquires an exclusive lock on the matching rows for the duration of the transaction.

START TRANSACTION;

SELECT stock FROM products WHERE id = 42 FOR UPDATE;
-- any other transaction's FOR UPDATE on id = 42 now blocks here

UPDATE products SET stock = stock - 3 WHERE id = 42;

COMMIT;

In Laravel, the same pattern is available through the query builder without writing raw SQL:

DB::transaction(function () use ($productId, $quantity) {
    $product = Product::where('id', $productId)->lockForUpdate()->first();

    if ($product->stock < $quantity) {
        throw new OutOfStockException();
    }

    $product->decrement('stock', $quantity);
});

DB::transaction() handles the commit and rollback automatically, including rolling back if the closure throws, which is exactly what you want when the stock check fails partway through.

Optimistic Locking with Version Columns

Pessimistic locking works well for short, fast operations, but it does not scale to situations with high contention and longer processing, such as a user editing a document for several minutes. Optimistic locking assumes conflicts are rare: it lets reads proceed without blocking and only checks for conflict at write time, using a version number or timestamp.

UPDATE documents
SET content = 'new content', version = version + 1
WHERE id = 7 AND version = 4;

If another transaction updated the row first, version no longer equals 4, zero rows are affected, and the application knows the write lost a race. It can then reload the current state and ask the user to resolve the conflict, rather than silently overwriting someone else's change.

$updated = Document::where('id', $id)
    ->where('version', $expectedVersion)
    ->update([
        'content' => $newContent,
        'version' => $expectedVersion + 1,
    ]);

if ($updated === 0) {
    throw new StaleDocumentException();
}

Deadlocks and How to Avoid Them

Locking introduces a new failure mode: two transactions each hold a lock the other needs, and neither can proceed. MySQL's InnoDB engine detects this and kills one transaction automatically, which your application must be prepared to catch and retry.

• Lock rows in a consistent order. If transfers between accounts always lock the lower account ID first, two transactions can never deadlock on each other's locks.
• Keep transactions short. The longer a lock is held, the more likely something else needs it at the same time.
• Catch and retry deadlock errors (MySQL error 1213) a small, bounded number of times rather than letting them bubble up as a hard failure.

Locking isn't about preventing concurrency — it's about making sure concurrency doesn't silently produce the wrong answer.

Wrapping Up

Reach for pessimistic locking when operations are short and conflicts are likely, such as decrementing inventory. Reach for optimistic locking when operations are longer-lived and conflicts are rare, such as collaborative document edits. Either way, keep transactions as short as possible, lock resources in a consistent order, and handle deadlocks as an expected, retryable outcome rather than a surprise.