MySQL Transactions

InnoDB transactions are ACID, use MVCC for reads, and take row-level locks for writes.

Autocommit

MySQL runs with autocommit = 1 by default: every statement is its own transaction. Explicit transactions require START TRANSACTION or BEGIN.

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

Locks InnoDB takes

• Record lock — on an index record.
• Gap lock — on the space between index records, preventing inserts into that gap.
• Next-key lock — a record lock plus the gap before it. This is the default under REPEATABLE READ and is what prevents phantoms.
• Insert intention lock — a gap lock signalling intent to insert.

Gap locking surprises people migrating from PostgreSQL: an UPDATE matching no rows can still lock a range and block unrelated inserts. If a statement cannot use an index, InnoDB locks every row it examines — which is another reason a missing index becomes a concurrency problem, not just a speed problem.

Inspecting locks

-- Who is blocking whom (MySQL 8.0).
SELECT r.trx_id AS waiting_trx, r.trx_mysql_thread_id AS waiting_thread,
       r.trx_query AS waiting_query,
       b.trx_id AS blocking_trx, b.trx_mysql_thread_id AS blocking_thread,
       b.trx_query AS blocking_query
FROM performance_schema.data_lock_waits w
JOIN information_schema.innodb_trx r ON r.trx_id = w.requesting_engine_transaction_id
JOIN information_schema.innodb_trx b ON b.trx_id = w.blocking_engine_transaction_id;
 
-- Long-running open transactions.
SELECT trx_id, trx_state, trx_started,
       TIMESTAMPDIFF(SECOND, trx_started, NOW()) AS age_s,
       trx_rows_locked, trx_query
FROM information_schema.innodb_trx
ORDER BY trx_started;

Deadlocks

InnoDB detects deadlocks and rolls back the transaction that has done the least work. The application must retry.

SHOW ENGINE INNODB STATUS\G   -- LATEST DETECTED DEADLOCK section

Enable innodb_print_all_deadlocks = ON so every deadlock is logged rather than only the most recent one being visible.

Reduce deadlock frequency by acquiring locks in a consistent order, keeping transactions short, and indexing the columns used in WHERE clauses of updating statements.

Long transactions are the real hazard

An open transaction holds locks and keeps the read view alive, so undo records cannot be purged and the history list grows. The symptoms — growing disk usage, slower reads — appear long after the cause.