Transactions & ACID Interview Questions

InnoDB isolation defaults to REPEATABLE READ, which is stricter than PostgreSQL's READ COMMITTED default. Consistent reads see a snapshot; writes still take record and next-key locks. Phantom prevention is via gap locks, not via true SERIALIZABLE predicate locks. WRITE skew can still happen because two transactions may update disjoint rows based on the same snapshot.

Autocommit=1 wraps every statement in its own transaction, so a multi-statement money move can commit the debit and leave the credit unapplied. BEGIN / START TRANSACTION plus COMMIT is the durable unit. Mixing DDL inside the transaction triggers an implicit commit in MySQL. Check @@autocommit on pooled connections; drivers often reset it inconsistently.

Deadlocks abort the transaction that InnoDB judges cheaper to roll back (usually fewer undo records) and return 1213. The winner continues; the loser must retry the whole transaction, not just the last statement. innodb_deadlock_detect=ON finds cycles; turning it off waits until innodb_lock_wait_timeout. SHOW ENGINE INNODB STATUS latest detected deadlock is the first debug dump.

Long transactions pin undo history because MVCC must keep old versions until the snapshot ends. Purge stalls, the history list length in SHOW ENGINE INNODB STATUS climbs, and secondary-index change-buffer merge slows. Disk fills with undo even if the transaction only did one SELECT. Keep write transactions short; move reporting to a replica snapshot.

XA transactions (two-phase commit) enlist InnoDB with an external TM; a prepared XA that never commits holds locks and undo forever. After a crash, XA RECOVER lists dangling XIDs you must COMMIT or ROLLBACK. For most apps, a single InnoDB transaction plus idempotent retries is safer than XA across MySQL and a message queue. binlog and XA together need log_bin_trust_function_creators care on some paths.

SAVEPOINTs let you roll back part of an InnoDB transaction without losing earlier work in the same snapshot. Nested app loops that try an INSERT and catch a duplicate key can ROLLBACK TO SAVEPOINT instead of aborting the whole order. They still hold undo until the outer COMMIT. Implicit commits from DDL wipe savepoints without an error you might expect.

Implicit commits (DDL, LOCK TABLES, most CREATE/DROP) silently end the current transaction in MySQL, which surprises people who wrapped ALTER in BEGIN. The statements before the DDL are already durable; statements after start a new transaction. This is a MySQL server rule, not an InnoDB MVCC rule. Never mix schema changes with money moves in one session script.

Write skew can still appear under REPEATABLE READ because InnoDB does not take predicate locks on the SELECT that decided there was a free slot. Two doctors both read one on-call row, both insert a shift, both commit. SERIALIZABLE or an explicit lock on a constraint row (SELECT ... FOR UPDATE of a counter) closes it. Snapshot isolation is not serializability.

The undo log plus the history list length (SHOW ENGINE INNODB STATUS) is the first place to look when purge lags. A forgotten replica with autocommit=0 and an open snapshot, or a mysqldump --single-transaction that never finishes, pins history. innodb_purge_threads can help CPU-bound purge, not a still-open read view. Kill the oldest trx from INFORMATION_SCHEMA.INNODB_TRX.

FLUSH TABLES WITH READ LOCK plus a long snapshot is the classic way a backup stalls DDL and MyISAM, and even InnoDB metadata. --single-transaction dump avoids FTWRL after the initial flush for pure InnoDB. LOCK INSTANCE FOR BACKUP (8.0) blocks DDL while allowing DML. Replica lag during backup is still a transaction-duration problem if the dump session holds a read view.

Group commit batches redo of several transactions into one innodb_flush_log_at_trx_commit fsync, so durability=1 is not one fsync per client. binary log group commit aligns binlog and InnoDB redo (sync_binlog=1). Setting flush_log_at_trx_commit=2 trades a crash window for throughput. Semi-sync waits sit after that fsync path and can dominate commit latency.

Foreign-key checks run inside the same InnoDB transaction and take shared locks on parent rows, which deadlocks with concurrent parent UPDATEs. SET FOREIGN_KEY_CHECKS=0 skips that for loads but is not transactional safety. Cascades execute as part of the child write. Missing indexes on FK columns turn those parent lookups into scans while holding locks.

A consistent snapshot is taken at the first consistent read in REPEATABLE READ; later SELECTs in that transaction see the same MVCC view. SELECT ... FOR UPDATE is a locking read, not a consistent snapshot. In READ COMMITTED the snapshot is rebuilt per statement. Long-lived snapshots pin undo until COMMIT, which is why history list length grows.

Row locks in InnoDB live on index records, not on 'the table row' as an abstract object. If the UPDATE uses a secondary index, the lock is on that index record plus the clustered PK. A table with no usable index falls back to a gap or even a table-level intent lock. That is why a missing WHERE index turns a tiny UPDATE into a lock storm.

READ COMMITTED re-evaluates the snapshot per statement, so non-repeatable reads are allowed. Gap locks are mostly not taken for ordinary searches, which reduces deadlock rate on hot ranges. You lose phantom protection that REPEATABLE READ next-key locks provide. Many OLTP apps prefer it once they stop assuming a snapshot lasts the whole request.

Gap locks and next-key locks (record plus gap) under REPEATABLE READ prevent phantom inserts into a scanned range. A unique equality on an existing record takes only a record lock. Empty ranges still lock the gap, which is why INSERT into a busy numeric key deadlocks with SELECT ... FOR UPDATE WHERE id > ?. READ COMMITTED largely avoids those gaps.

SELECT ... FOR UPDATE on a unique index locks the record; on a non-unique or range it also gap-locks neighbors under REPEATABLE READ. FOR SHARE (LOCK IN SHARE MODE) allows other readers but still blocks writers. SKIP LOCKED and NOWAIT (8.0) let queues skip hot rows. Using FOR UPDATE on a column with no index escalates toward many clustered records.

innodb_lock_wait_timeout kills a waiter; innodb_deadlock_detect finds cycles faster than waiting. High timeout plus detection OFF turns deadlocks into multi-second stalls. PERFORMANCE_SCHEMA data_locks / data_lock_waits (8.0) shows who holds the record. Application retries must be the full transaction with backoff, or you amplify the pile-up.

SERIALIZABLE in InnoDB is REPEATABLE READ plus converting plain SELECTs into locking reads (equivalent to FOR SHARE). That closes some write-skew windows at the cost of far more record locks. It is not full predicate locking like some textbooks describe. Most teams stay on REPEATABLE READ and lock the rows they will update explicitly.

Next-key locking on an empty range (WHERE id > 100 FOR UPDATE with no matching row) still locks the gap until the supremum record. Concurrent INSERT of id=101 waits. Under READ COMMITTED that gap is often not held. Unique-check gap locks on INSERT ... ON DUPLICATE KEY also surprise people during bulk loads. Trace with PERFORMANCE_SCHEMA data_locks.