1Z0-909: Transactions and Locking study guide

What the domain tests

Transactions and Locking tests InnoDB's ACID behavior, isolation levels, explicit transaction control, locking reads, and deadlock handling. Questions often present two sessions and ask what each sees or whether a statement blocks.

Core rules

InnoDB is transactional; MyISAM is not. Autocommit is on by default, so each statement commits unless you use START TRANSACTION or BEGIN.
COMMIT makes changes durable. ROLLBACK undoes them. SAVEPOINT names a point for partial rollback with ROLLBACK TO SAVEPOINT.
Isolation levels are READ UNCOMMITTED, READ COMMITTED, REPEATABLE READ, the InnoDB default, and SERIALIZABLE. REPEATABLE READ uses a consistent snapshot established at the first read, so repeated nonlocking SELECTs see the same data.
SELECT ... FOR UPDATE takes exclusive row locks. SELECT ... FOR SHARE takes shared locks. A plain SELECT is a consistent nonlocking read.
InnoDB uses next-key locking, record plus gap locks, under REPEATABLE READ, reducing phantoms but increasing deadlock potential.
DDL such as CREATE TABLE or ALTER TABLE causes an implicit commit.
Deadlocks are detected automatically. One transaction receives ER_LOCK_DEADLOCK (1213) and should be retried.

Worked example

Session A begins, updates row id=1, and does not commit. Session B begins and updates the same row; B blocks waiting for A's exclusive lock. If A then updates a row that B already locked, the cycle is detected and one session is rolled back with error 1213. The reasoning is that locks are held until COMMIT or ROLLBACK, and a wait-for cycle cannot be resolved except by aborting a transaction.

Traps

Using SELECT ... FOR UPDATE outside a transaction, where autocommit releases the lock immediately.
Assuming REPEATABLE READ prevents all phantoms for locking reads; gap-locking behavior matters.
Mixing InnoDB and nontransactional tables in one transaction, so ROLLBACK cannot undo all changes.
Forgetting that session variables and temporary tables are not transactional.
Treating a deadlock as a fatal application error instead of retrying the transaction.