MySQL Transactions, Locking, and Concurrency

Transactions protect multi-step operations from partial completion. Concurrency control protects data when multiple requests change the same records at the same time.

ACID in Practical Terms

Lock a Row Before Changing a Limited Resource

START TRANSACTION;

SELECT available_seats
FROM trip_inventory
WHERE trip_id = 42
FOR UPDATE;

UPDATE trip_inventory
SET available_seats = available_seats - 2
WHERE trip_id = 42
  AND available_seats >= 2;

COMMIT;

The application must verify that the update affected one row. Keep transactions short and avoid waiting on external services while holding locks.

Optimistic Concurrency

UPDATE bookings
SET status = :new_status,
    version = version + 1,
    updated_at = UTC_TIMESTAMP()
WHERE id = :id
  AND version = :expected_version;

If zero rows are affected, another process changed the record first. The application can reload and ask the user to retry.

Deadlocks

A deadlock occurs when transactions wait on each other in a cycle. InnoDB detects the cycle and rolls one transaction back. Applications should retry a small number of times with backoff when the operation is safe to repeat.

• Access tables and rows in a consistent order.
• Keep transactions short.
• Index predicates so updates lock only intended rows.
• Avoid user interaction or API calls inside transactions.
• Capture deadlock details for investigation.

Do Not Hold a Transaction Across a Web Form

Load data, render the form, and end the request. On submission, start a new transaction, re-check the current state, apply the change, and commit. A browser think-time transaction would hold locks for minutes.