Deadlocks & Locking — Concept Check
Questions on Deadlocks & Locking. Deadlocks occur when two transactions hold locks each other needs; InnoDB detects the cycle and rolls back the lighter transaction; access rows in consistent order to avoid them.
Questions in this quiz
8 questions on Deadlocks & Locking — Concept Check. Work through them here, then start the quiz to answer them and see your score.
1.Transaction A locks row 1 and tries to lock row 2. Transaction B locks row 2 and tries to lock row 1. What happens next?
- ATransaction B waits for A, then completes successfully.
- BDeadlock occurs and InnoDB rolls back the lighter transaction.
- CBoth transactions proceed without issue.
- DTransaction A waits for B, then completes successfully.
2.What is a key strategy to prevent deadlocks in transactions?
- AAvoid using indexes.
- BIncrease transaction time.
- CAccess resources in a consistent order.
- DUse more complex queries.
3.A transaction is rolled back with error 1213. What should be done in the application code?
- AIncrease the transaction timeout.
- BSwitch to a different database engine.
- CRetry the transaction as deadlocks are transient.
- DAbort the operation completely.
4.Why might an UPDATE operation without an index increase deadlock probability?
- AIt decreases the chance of lock escalation.
- BIt ensures consistent row access order.
- CIt speeds up the transaction, reducing lock time.
- DIt forces InnoDB to lock many rows, increasing contention.
5.In a database transaction, what does InnoDB do when it detects a deadlock?
- ARolls back the transaction with the least undo log.
- BPauses both transactions indefinitely.
- CContinues both transactions after a delay.
- DRolls back the transaction with the most undo log.
6.What is a common cause of deadlocks related to indexes?
- AIndexes cause transactions to lock rows in random order.
- BIndexes always prevent deadlocks by optimizing access.
- CMissing indexes lead to full-table locks or large gap locks.
- DIndexes slow down transactions, increasing deadlock risk.
7.How does InnoDB choose which transaction to roll back in a deadlock?
- AIt rolls back the transaction that started first.
- BIt rolls back the transaction with the most locks held.
- CIt rolls back the transaction that started last.
- DIt rolls back the transaction with the least undo log.
8.What should be prioritized to minimize deadlock windows in application code?
- AIncrease transaction complexity.
- BKeep transactions short and avoid external calls.
- CHold application-level locks longer.
- DUse longer transaction timeouts.