How to Detect Deadlocks

MySQL logs deadlock information internally. To view the most recent deadlock:

Syntax:

SHOW ENGINE INNODB STATUS;

This command returns a detailed report including:

  • The conflicting transactions
  • The SQL statements involved
  • The lock types and affected rows

Deadlock Prevention Strategies

  • Use Consistent Locking Order: Always acquire locks in the same sequence across transactions.
  • Keep Transactions Short and Fast: Minimize the time locks are held by optimizing queries and avoiding unnecessary operations.
  • Choose Appropriate Isolation Levels: Use READ COMMITTED instead of REPEATABLE READ when strict consistency isn’t required.
  • Avoid Manual Locking Unless Necessary: Let InnoDB handle row-level locking automatically unless explicit control is required.
  • Limit Rows Affected: Use WHERE clauses and LIMIT to reduce the scope of locked data.
  • Implement Retry Logic in Application Code: If a deadlock occurs, retry the transaction after a short delay using exponential backoff.

Example: Deadlock Detection and Resolution

The following example demonstrates how a deadlock can occur when two transactions attempt to lock rows in different orders. MySQL’s InnoDB engine will automatically detect this situation and resolve it by rolling back one transaction.

Syntax & Example:

-- Transaction A
START TRANSACTION;
SELECT * FROM accounts WHERE id = 1 FOR UPDATE;

-- Transaction B
START TRANSACTION;
SELECT * FROM accounts WHERE id = 2 FOR UPDATE;

-- Transaction A waits for row 2
SELECT * FROM accounts WHERE id = 2 FOR UPDATE;

-- Transaction B waits for row 1
SELECT * FROM accounts WHERE id = 1 FOR UPDATE;

In this scenario, both transactions wait on each other, creating a deadlock.
InnoDB automatically detects the deadlock and rolls back one of the transactions to allow the other to proceed.

PreviousNext