Analyzing MySQL Deadlocks Through Insert Locking Source Code

Problem Phenomenon

During a stress test, a system encountered deadlock errors during concurrent INSERT operations:

Deadlock found when trying to get lock; try restarting transaction

‍

The SHOW ENGINE INNODB STATUS log revealed two transactions competing for locks on the unique index ua of table dl_tab:

*** (1) TRANSACTION:
TRANSACTION 56118, ACTIVE 6 sec inserting
LOCK WAIT
...
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 11 page no 5 n bits 72 index ua of table `testdb`.`dl_tab` trx id 56118 lock_mode S waiting
...

*** (2) TRANSACTION:
TRANSACTION 56113, ACTIVE 12 sec inserting
LOCK WAIT
...
*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 11 page no 5 n bits 72 index ua of table `testdb`.`dl_tab` trx id 56113 lock_mode X locks gap before rec insert intention waiting
...

‍

Root Cause Analysis

Key Observations

1. ​Lock Modes:
   • Transaction T1 held an ​exclusive lock (X)​​ on record (10,26) with LOCK_REC_NOT_GAP.
   • Transaction T2 waited for a ​shared lock (S)​​ on the same record, triggering a deadlock.
2. ​Isolation Level:
   • The database operated under ​Read-Committed (RC)​​ isolation, where phantom reads are prevented but non-repeatable reads are allowed.
3. ​Index Structure:
   • The table dl_tab had a unique index ua on column name, leading to gap locks during conflict checks.

Source Code Insights

The deadlock arose from InnoDB’s ​next-key locking​ mechanism combined with ​insert intention locks:

1. ​T1’s Workflow:
   • Inserted (26,10) → Acquired X lock on record (10,26).
   • Later attempted to insert (40,8) → Checked for conflicts on (10,26) and acquired INSERT INTENTION lock.
2. ​T2’s Workflow:
   • Inserted (30,10) → Blocked by T1’s X lock on (10,26).
   • Timed out and converted its lock to ​waiting mode, creating a cyclic dependency.

Deadlock Sequence

1. T1 holds X lock on (10,26).
2. T2 requests S lock on (10,26) and waits.
3. T1 requests INSERT INTENTION lock on (10,26) but is blocked by T2’s waiting lock.
4. ​Deadlock detected: Both transactions wait indefinitely.

Solution

1. ​Avoid Duplicate Inserts:
   • Modify business logic to prevent conflicting INSERT operations on indexed columns.
2. ​Reduce Concurrency:
   • Implement batching or rate-limiting for high-concurrency INSERTs.
3. ​Adjust Isolation Levels:
   • Use ​Read Uncommitted (RU)​​ or ​Serializable​ isolation if feasible, though with trade-offs.
4. ​Index Optimization:
   • Remove unnecessary unique constraints or replace them with non-unique indexes.

‍