Analyzing Insert Concurrency Deadlocks Through Source Code Logic

Foreword

Deadlocks, a common concurrency challenge in databases, are notoriously difficult to resolve due to:

1. ​Business complexity: Involving two, three, or more transactions.
2. ​Database-specific mechanisms: Varying lock implementations across engines like MySQL, Oracle, etc.
3. ​Post-mortem limitations: Deadlocks terminate transactions abruptly, leaving no trace of pre-deadlock states.

This article analyzes an INSERT-related deadlock in MySQL using a four-step approach—problem identification, reproduction, root cause analysis, and resolution—to provide a systematic framework for troubleshooting.

Problem Phenomenon

During a pre-production load test, the application logs revealed frequent deadlock errors:

Deadlock found when trying to get lock; try restarting transaction

‍

The SHOW ENGINE INNODB STATUS output highlighted the deadlock details (sanitized):

*** (1) TRANSACTION:  
TRANSACTION 56118, ACTIVE 6 sec inserting  
LOCK WAIT  
2 lock struct(s), heap size 1192, 1 row lock(s), undo log entries 1  
MySQL thread id 9, query id 57 localhost root  
insert into dl_tab(id,name) values(30,10)  

*** (1) HOLDS THE LOCK(S):  
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  

*** (1) WAITING FOR THIS LOCK:  
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  
3 lock struct(s), heap size 1192, 2 row lock(s), undo log entries 2  
MySQL thread id 8, query id 58 localhost root  
insert into dl_tab(id,name) values(40,8)  

*** (2) HOLDS THE LOCK(S):  
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 rec but not gap  

*** (2) WAITING FOR THIS LOCK:  
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  
WE ROLL BACK TRANSACTION (1)

‍

Key Observations

• ​Table Structure:

CREATE TABLE `dl_tab` (
  `id` int NOT NULL AUTO_INCREMENT,
  `name` int NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `ua` (`name`)
) ENGINE=InnoDB AUTO_INCREMENT=41 DEFAULT CHARSET=utf8mb4;

‍

• ​Isolation Level: Read-Committed (RC).
• ​Disposition: Both transactions acquired conflicting locks on the ua unique index, leading to a cyclic dependency.

Reproducing the Issue

To isolate the problem, developers recreated the workload in a controlled environment:

1. ​Data Setup: Inserted sample records to trigger duplicate key checks.
2. ​Concurrency Simulation: Ran concurrent INSERT statements targeting overlapping name values.
3. ​Monitoring: Used tools like performance_schema to trace lock acquisition and wait events.

Key SQL commands used for analysis:

-- Trace active threads and their SQL history
SELECT PROCESSLIST_ID, THREAD_ID, PROCESSLIST_INFO 
FROM performance_schema.threads 
WHERE PROCESSLIST_ID IN (8, 10);

-- Extract detailed lock information
SELECT * FROM performance_schema.data_locks 
WHERE ENGINE_TRANSACTION_ID IN (<trx_ids_from_threads>);

‍

Root Cause Analysis

The deadlock arose due to ​incompatible lock modes​ during concurrent INSERT operations on a unique index under RC isolation:

1. ​Transaction T1:
   • Acquired an ​exclusive lock (X)​​ on record (10, 26) via INSERT.
   • Blocked when attempting a ​gap lock​ for subsequent inserts.
2. ​Transaction T2:
   • Held a ​shared lock (S)​​ on the same record (10, 26) during its own INSERT.
   • Entered a wait state due to T1’s exclusive lock.
3. ​Cyclic Dependency:
   • T1 waited for T2’s shared lock to release while T2 awaited T1’s exclusive lock, creating a deadlock.

​Technical Insight:
Under RC, MySQL converts implicit row locks to explicit locks during uniqueness checks. This behavior, combined with gap lock contention on the unique index, triggered the deadlock.

Resolution

To mitigate the issue:

1. ​Avoid Duplicate Inserts: Enforce application-level validation to prevent conflicting name values.
2. ​Optimize Workload: Sequentialize high-concurrency writes or use sharding to reduce lock contention.
3. ​Adjust Isolation Levels: Consider READ COMMITTED (already in use) or SNAPSHOT ISOLATION if supported.

​Code Adjustment Example:

-- Add conditional check before INSERT
INSERT INTO dl_tab (id, name)
SELECT 30, 10 
FROM dual 
WHERE NOT EXISTS (SELECT 1 FROM dl_tab WHERE name = 10);

‍

Key Takeaways

• ​Deadlock Prevention: Prioritize unique index design and isolation level selection.
• ​Debugging Workflow: Leverage performance_schema and innodb_status for lock visualization.
• ​Source Code Insight: Understanding InnoDB’s lock conversion logic (e.g., lock_rec_convert_impl_to_expl) is critical for root-cause analysis.

For further exploration, refer to MySQL’s source code or resources like Percona’s Deadlock Troubleshooting Guide.