Deadlock detection and prevention in MySQL - Cheat Sheet & Quick Revision

Recall & Review

beginner

What is a deadlock in a database?

A deadlock happens when two or more transactions wait for each other to release locks, so none can proceed. It's like two people blocking each other in a narrow hallway.

intermediate

How does MySQL detect deadlocks?

MySQL detects deadlocks by checking for cycles in the lock wait graph. When it finds a cycle, it chooses one transaction to roll back to break the deadlock.

beginner

Name one common method to prevent deadlocks.

One common method is to always acquire locks in the same order in all transactions. This avoids circular waiting and prevents deadlocks.

intermediate

What is the role of InnoDB in deadlock handling in MySQL?

InnoDB storage engine automatically detects deadlocks and rolls back one transaction to resolve it, allowing others to continue.

beginner

Why is it important to keep transactions short to prevent deadlocks?

Short transactions hold locks for less time, reducing the chance that other transactions will wait and cause deadlocks.

What causes a deadlock in a database?

A A database backup running

B A transaction running too fast

C A query with syntax error

D Two transactions waiting on each other’s locks

Deadlocks happen when transactions wait for each other’s locks, causing a cycle that stops progress.

How does MySQL resolve a detected deadlock?

A It ignores the deadlock

B It rolls back one transaction to break the deadlock

C It restarts the database server

D It pauses all transactions

MySQL chooses one transaction to roll back, freeing locks and resolving the deadlock.

Which practice helps prevent deadlocks?

A Acquiring locks in a consistent order

B Running transactions without locks

C Using very large transactions

D Ignoring error messages

Consistent lock order avoids circular waiting, preventing deadlocks.

What does InnoDB do when it detects a deadlock?

A Rolls back one transaction automatically

B Shuts down the database

C Sends an email alert

D Locks all tables

InnoDB automatically rolls back one transaction to resolve deadlocks.

Why should transactions be kept short to avoid deadlocks?

A Short transactions increase deadlocks

B Short transactions use more CPU

C Short transactions hold locks less time, reducing conflicts

D Short transactions slow down queries

Short transactions reduce lock holding time, lowering deadlock chances.

Explain what a deadlock is and how MySQL detects and resolves it.

Think about transactions waiting for each other and how MySQL breaks the cycle.

Describe two ways to prevent deadlocks in database transactions.

Consider how to avoid circular waiting and reduce lock time.