MySQL Transactions, Locking, and Concurrency

Transactions make sure a multi-step operation happens all the way or not at all. Concurrency control is about what happens when two requests try to change the same thing at the same moment. On a quiet dev machine you'll never see these problems. In production you will.

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;

Always check that the update actually changed one row. And keep transactions short. Never call an outside API or send email while you're holding locks.

Optimistic concurrency

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

Zero rows affected means somebody else changed the record first. Reload it and let the user try again, rather than silently overwriting their coworker's changes.

Deadlocks

A deadlock is two transactions each waiting on the other. InnoDB notices and kills one of them. They're normal on a busy system, not a sign something's broken. If the operation is safe to repeat, retry a few times with a short backoff.

• 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 the data, show the form, end the request. When they submit, start a fresh transaction, re-check the current state, make the change, commit. If you held a transaction open while someone stared at a form, you'd be holding locks while they went to get coffee.