Transactions and Locking

Starting, committing, and rolling back#

A transaction groups multiple statements into a single all-or-nothing unit — either every statement in it takes effect, or none do. In InnoDB:

START TRANSACTION;

UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;

COMMIT;

If anything goes wrong partway through — an application error, a constraint violation, a manual decision to abort — ROLLBACK undoes every change made since START TRANSACTION, as if none of it had happened:

START TRANSACTION;

UPDATE accounts SET balance = balance - 100 WHERE id = 1;
-- something goes wrong here
ROLLBACK;   -- the UPDATE above never happened, as far as the database is concerned

This is exactly why a money transfer must be wrapped in a transaction: without one, a crash between the two UPDATE statements would leave $100 deducted from one account and never credited to the other.

By default, MySQL runs in autocommit mode — every individual statement is its own implicit transaction, committed immediately. START TRANSACTION (or BEGIN) suspends autocommit until the next COMMIT or ROLLBACK.

Isolation levels#

Isolation level controls how much one transaction can "see" of another transaction's uncommitted or concurrently-changing work. Stricter isolation gives stronger guarantees at the cost of more locking (and therefore less concurrency).

*MySQL's InnoDB prevents phantom reads at REPEATABLE READ via a mechanism called next-key locking (a combination of row locks and gap locks), which is stricter than the SQL standard technically requires at that level.

• Dirty read — reading another transaction's uncommitted changes, which might still be rolled back.
• Non-repeatable read — re-reading the same row twice within one transaction and getting different values, because another transaction committed a change in between.
• Phantom read — re-running the same range query twice within one transaction and getting a different set of rows, because another transaction inserted or deleted rows matching that range in between.

SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;

REPEATABLE READ vs READ COMMITTED#

MySQL's default, REPEATABLE READ, guarantees that every read within a single transaction sees a consistent snapshot taken at the transaction's start — re-running the same SELECT twice returns identical results, even if other transactions commit changes in between. READ COMMITTED (the default in PostgreSQL and SQL Server) takes a fresh snapshot on every statement within the transaction, so two SELECTs in the same transaction can see different data if another transaction committed between them.

Neither is universally "better" — REPEATABLE READ gives simpler, more predictable behavior within a long-running transaction (useful for reports or multi-step logic that must see a consistent view); READ COMMITTED gives more up-to-date data at the cost of that consistency, and it's a lighter locking burden in some contended workloads. Applications ported from PostgreSQL to MySQL (or vice versa) occasionally hit subtle bugs from assuming the other database's default isolation level.

Row-level locking#

InnoDB locks at the row level, not the table level — two transactions can update different rows of the same table simultaneously without blocking each other. A transaction acquires a lock on a row the moment it modifies (or, depending on isolation level, reads with SELECT ... FOR UPDATE) that row, and holds it until COMMIT or ROLLBACK.

-- Transaction A
START TRANSACTION;
SELECT * FROM products WHERE id = 1 FOR UPDATE;   -- locks this row
UPDATE products SET stock = stock - 1 WHERE id = 1;
-- ... not yet committed ...

-- Transaction B, running concurrently, blocks here until A commits or rolls back:
UPDATE products SET stock = stock - 1 WHERE id = 1;

SELECT ... FOR UPDATE explicitly locks the selected rows against concurrent modification — the standard pattern for "read a value, then update it based on what was read" (like decrementing stock) without a race condition between two concurrent transactions both reading the same starting value.

Deadlocks#

A deadlock happens when two transactions each hold a lock the other needs, and each is waiting for the other to release it — neither can proceed.

-- Transaction A                          -- Transaction B
START TRANSACTION;                        START TRANSACTION;
UPDATE accounts SET balance = balance-10  UPDATE accounts SET balance = balance-10
  WHERE id = 1;   -- locks row 1            WHERE id = 2;   -- locks row 2
UPDATE accounts SET balance = balance+10  UPDATE accounts SET balance = balance+10
  WHERE id = 2;   -- blocks: B holds it      WHERE id = 1;   -- blocks: A holds it
                                                              -- DEADLOCK

InnoDB detects this automatically, picks one transaction as the "victim," and rolls it back with an error (ERROR 1213: Deadlock found when trying to get lock), letting the other proceed. The application must be prepared to catch that error and retry the rolled-back transaction.

The standard prevention strategy is simple: always acquire locks (update rows) in a consistent order across every transaction that touches the same rows — e.g., always update the lower account id first. If both transactions above had updated account 1 before account 2, no deadlock could occur.

Common mistakes#

• Leaving a transaction open (uncommitted) for a long time while waiting on something external (a slow API call, user input) — it holds locks the whole time, blocking other transactions unnecessarily.
• Assuming REPEATABLE READ behaves like PostgreSQL's READ COMMITTED (or vice versa) after switching databases — the same isolation level name doesn't guarantee identical behavior across systems.
• Not handling deadlock errors in application code — a deadlock is a normal, expected occurrence under concurrent load, not a bug, and the correct response is to retry the rolled-back transaction.
• Updating rows in different orders across different code paths, which is the single most common cause of deadlocks in real applications.