MySQL/InnoDB Deadlock Analysis: Detection, Handling, and Prevention

This article provides a comprehensive technical guide on MySQL/InnoDB deadlocks, covering definition, detection mechanisms, lock types, and prevention strategies.

1. What is Deadlock

Deadlock is a common issue in concurrent systems, including InnoDB. It occurs when two or more transactions are waiting for each other to release locks they already hold, creating a circular wait. For example, Transaction A holds lock x1 and requests x2, while Transaction B holds x2 and requests x1 - this creates a deadlock.

Four essential elements of deadlock in MySQL:

a) Two or more transactions b) Each transaction holds locks and requests new locks c) Lock resources can only be held by one transaction or are incompatible d) Circular wait occurs between transactions due to holding and requesting locks

2. MySQL Deadlock Mechanism

Deadlock mechanism consists of detection and handling. InnoDB performs wait-for graph detection when transactions attempt to acquire locks and need to wait. If a cycle is detected, the transaction with fewer undo logs is rolled back.

Deadlock detection process:

1) When InnoDB transaction attempts to acquire a lock and needs to wait, deadlock detection begins

2) lock_deadlock_check_and_and_resolve() function performs detection

3) Counter limits detection - stops if timeout or threshold exceeded

4) Wait-for graph processing detects cycles in the lock information and transaction wait chain

5) Transaction with fewer undo logs is rolled back

Deadlock handling methods from database system implementation:

1) Timeout-based detection: Rollback transactions exceeding active time limits

2) Wait-for graph: Detect cycles in transaction lock wait relationships

3) Element ordering to prevent deadlocks

4) Timestamp-based deadlock detection with rollback strategies

3. InnoDB Lock Types

Two conventional lock modes in MySQL:

LOCK_S (Shared/Read Lock)

LOCK_X (Exclusive/Write Lock)

Lock attributes that can combine with lock modes:

LOCK_REC_NOT_GAP (Record Lock)

LOCK_GAP (Gap Lock)

LOCK_ORDINARY (Next-Key Lock - locks record and gap before it)

LOCK_INSERT_INTENTION (Insert Intention Lock - special GAP lock)

Lock attribute combinations:

lock->type_mode can be Lock_X or Lock_S

locks gap before rec: lock->type_mode & LOCK_GAP

locks rec but not gap: lock->type_mode & LOCK_REC_NOT_GAP

insert intention: lock->type_mode & LOCK_INSERT_INTENTION

waiting: lock->type_mode & LOCK_WAIT

4. Transaction Locking Behavior in Different Scenarios

For UPDATE operations like update tab set x=1 where id=1 :

1) Primary key column, RC isolation: X lock on the record

2) Unique secondary index, RC: Two X locks - one on unique index, one on clustered index

3) Non-unique secondary index, RC: All matching records locked, plus their primary key records

4) No index, RC: Full table scan with X locks on all records (optimization releases locks for non-matching records)

5) Primary key, RR: X lock on record

6) Unique secondary index, RR: Two X locks

7) Non-unique secondary index, RR: Records locked with GAP locks, plus primary key locks

8) No index, RR: Full table lock

INSERT Locking Process (with unique index):

1) Find first record greater than N (M) and previous record (P)

2) If M has no gap/next-key lock, proceed to step 3; otherwise wait

3) Check P: if P != N, complete insertion; if P == N:

If no lock on P: Report error 1062 (duplicate key)

If S-lock exists: Record marked as deleted, transaction committed but not purged

If lock exists: S-lock added, record marked as deleted, transaction not committed

DELETE Locking Behavior:

1) Non-unique index: Gap lock on record and gap before it

2) Unique index/primary key: No gap lock for existing records

3) All cases for non-existing records: Gap lock on the range

4) Deleting marked-deleted records via non-unique/unique index: Row lock + gap lock

5) RC has no gap locks except for unique key conflicts

5. How to View Deadlocks

Query transaction lock status:

select * from information_schema.innodb_locks;

select * from information_schema.innodb_lock_waits;

select * from information_schema.innodb_trx;

Query database wait situation:

select r.trx_id wait_trx_id,
 r.trx_mysql_thread_id wait_thr_id,
 r.trx_query wait_query,
 b.trx_id block_trx_id,
 b.trx_mysql_thread_id block_thrd_id,
 b.trx_query block_query
from information_schema.innodb_lock_waits w
 inner join information_schema.innodb_trx b on b.trx_id = w.blocking_trx_id
 inner join information_schema.innodb_trx r on r.trx_id = w.requesting_trx_id

Enable detailed deadlock logging:

innodb_print_all_deadlocks = ON

innodb_status_output_locks = ON

View InnoDB status with deadlock logs:

show engine innodb status;

6. How to Avoid Deadlocks

Use READ COMMITTED isolation level and binlog_format=ROW to avoid gap lock contention in RR mode

Design indexes properly with high-selectivity columns at the front of composite indexes to reduce lock contention

Adjust SQL execution order to avoid long-running update/delete statements holding locks early in transactions

Keep transaction sizes small to reduce lock conflict probability

Access tables in consistent order across transactions to avoid deadlocks

Use innodb_deadlock_detect parameter (available after 5.7.15) to disable deadlock detection and improve concurrent TPS