Transactions

InnoDB provides ACID transactions with row-level locking and MVCC-style consistent reads. `autocommit=1` wraps each statement unless you explicitly `START TRANSACTION`.

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

-- Pessimistic lock for read-modify-write
START TRANSACTION;
SELECT balance FROM accounts WHERE id = 1 FOR UPDATE;
-- application logic
UPDATE accounts SET balance = ? WHERE id = 1;
COMMIT;
				

Isolation defaults to REPEATABLE READ with next-key locking in InnoDB — phantom protection at the cost of more locks than READ COMMITTED. Deadlocks happen; InnoDB picks a victim and rolls back one transaction — application code should retry idempotent writes.

On interviews: contrast autocommit vs explicit transactions, explain FOR UPDATE vs optimistic concurrency, and describe deadlock retry strategy.

Common pitfalls: long transactions holding locks; mixing MyISAM (no transactions) with InnoDB; assuming READ COMMITTED behavior on default RR; no idempotency on deadlock retry.

The trade-off is stronger isolation guarantees versus lock contention and retry complexity under concurrent writers.

Checklist:

• Wrap multi-step invariants in explicit transactions.
• Choose isolation level for anomaly tolerance.
• Use FOR UPDATE only when necessary.
• Retry deadlocks with idempotent operations.