InnoDB Transactions and Isolation

ACID in InnoDB

InnoDB delivers atomic statements, consistent reads, durable commits through the redo log, and row-level locking. Transactions group operations so they commit or roll back as one unit.

Using Transactions

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

Isolation Levels

SET TRANSACTION ISOLATION LEVEL READ COMMITTED;

REPEATABLE READ is the default and gives snapshot reads. READ COMMITTED shows newly committed rows on each read. SERIALIZABLE locks whole ranges for full consistency at a concurrency cost.

Locking Notes

SELECT ... FOR SHARE reads with shared locks, while SELECT ... FOR UPDATE locks rows for writing. Deadlocks surface as errors, and InnoDB rolls back one victim automatically.

Key Points

• Group related writes in transactions.
• REPEATABLE READ is the MySQL default.
• Use FOR UPDATE carefully on hot rows.
• Retry transactions that hit deadlocks.