How does InnoDB choose deadlock victims in MySQL?

Question

Options

• AIt chooses the transaction with the lowest transaction ID.
• BIt chooses the transaction with the least accumulated CPU time.
• CIt chooses the transaction with the most accumulated CPU time.
• DIt chooses the transaction randomly.
• EIt chooses the transaction with the most modified rows.
• FIt chooses the transaction with the fewest modified rows.

• A
  3% (1)
• B
  3% (1)
• D
  88% (35)
• F
  8% (3)

Explanation

Heads up: the stated correct answer appears to be wrong. MySQL InnoDB does not choose deadlock victims randomly.

According to MySQL documentation, InnoDB uses a cost-based heuristic: it rolls back the transaction with the fewest modified rows (inserts, updates, deletes), making F the correct answer. This minimizes the undo work needed to resolve the deadlock.

Here's why each option is wrong or misleading:

• A (lowest transaction ID): InnoDB ignores transaction ID age when picking victims - that's more relevant to MVCC visibility, not deadlock resolution.
• B/C (CPU time): InnoDB has no mechanism to track or compare accumulated CPU time per transaction for this purpose.
• D (random): This is definitively false - MySQL documentation explicitly states it picks the "cheapest" transaction to roll back, which is deterministic.
• E (most modified rows): The opposite of what InnoDB does; rolling back more work is more expensive, not less.
• F (fewest modified rows): This is the actual documented behavior - rolling back fewer changes is the cheapest path to resolving the deadlock.

Memory tip: Think "fewest = fastest to undo." InnoDB is greedy about minimizing rollback cost, so it sacrifices the transaction that has done the least work, since that's the quickest recovery path.

If this is from a practice exam or course, I'd recommend flagging it as an error - the documented answer is F.

No community discussion yet for this question.