How does the InnoDB storage engine handle deadlocks when they are detected?

Question

Options

• ABoth the affected transactions will be rolled back.
• BThe affected transactions wait for innodb_lock_wait_timeout seconds, and then roll back.
• COne of the affected transactions will be rolled back, the other is allowed to proceed.
• DThe transaction isolation level determines which transaction is rolled back.
• EThe innodb_locks_unsafe_for_binlog setting determines which transaction is rolled back.

• B
  4% (2)
• C
  91% (43)
• D
  2% (1)
• E
  2% (1)

Explanation

When InnoDB detects a deadlock, it automatically selects one transaction as the "victim" and rolls it back, allowing the other transaction to proceed - this is the engine's built-in deadlock resolution mechanism, making C correct. Option A is wrong because rolling back both transactions is unnecessary and wasteful; only one needs to be sacrificed to break the cycle. Option B describes lock wait timeout behavior, which is a separate mechanism that triggers when a transaction waits too long for a lock, not when a deadlock is actively detected. Option D is incorrect because transaction isolation level controls visibility of data changes (e.g., dirty reads), not deadlock resolution logic. Option E is wrong because innodb_locks_unsafe_for_binlog relates to gap locking behavior for binary log safety, not deadlock handling.

Memory tip: Think of InnoDB as a referee that spots two players deadlocked - it doesn't eject both (A) or make them wait (B), it picks the cheaper player to sit out (the transaction with the least undo cost) and lets the game continue.

No community discussion yet for this question.