MySQL Deadlock Analysis: A Case Study of Concurrent Inserts on a Unique Index

Introduction : Deadlocks are a common concurrency problem in databases. This article uses a MySQL deadlock triggered by concurrent INSERT statements on a unique index as a concrete example to illustrate a systematic analysis and resolution approach.

Problem Phenomenon : During a pre‑release load test the application log showed the message “Deadlock found when trying to get lock; try restarting transaction”. The corresponding SHOW ENGINE INNODB STATUS output displayed detailed lock information.

Deadlock found when trying to get lock; try restarting transaction<br/>------------------------<br/>LATEST DETECTED DEADLOCK<br/>------------------------<br/>2023-03-24 19:07:50 140736694093568<br/>*** (1) TRANSACTION:<br/>TRANSACTION 56118, ACTIVE 6 sec inserting<br/>... (truncated for brevity) ...<br/>*** WE ROLL BACK TRANSACTION (1)<br/>------------

Deadlock Information Analysis : The article extracts the involved transactions, lock types, and the table definition. It notes version‑specific differences in InnoDB status output (e.g., MySQL 8.0.18+ includes both transactions' lock information).

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 COLLATE=utf8mb4_0900_ai_ci

Reproducing the Issue : The deadlock was reproduced in a development environment by running the same INSERT statements, confirming identical log and InnoDB status output.

Tracking the Deadlock : By leveraging performance_schema tables (e.g., events_statements_history, threads) and a custom conversion function, the author captured the exact SQL statements, timestamps, and thread IDs involved.

-- Convert timer to UTC
CREATE DATABASE IF NOT EXISTS perf_db;
USE perf_db;
DELIMITER //
CREATE FUNCTION f_convert_timer_to_utc(pi_timer BIGINT) RETURNS TIMESTAMP(6)
DETERMINISTIC
BEGIN
    DECLARE value_utc_time TIMESTAMP(6);
    SELECT FROM_UNIXTIME((UNIX_TIMESTAMP(SYSDATE()) - variable_value) + pi_timer/1000000000000)
    FROM performance_schema.global_status WHERE variable_name = 'Uptime' INTO value_utc_time;
    RETURN value_utc_time;
END;//
DELIMITER ;

SELECT PROCESSLIST_ID, THREAD_ID, PROCESSLIST_INFO
FROM performance_schema.threads
WHERE PROCESSLIST_ID IN (8,10);

SELECT THREAD_ID, f_convert_timer_to_utc(TIMER_START) run_start_time,
       f_convert_timer_to_utc(TIMER_END) run_end_time,
       TIMER_WAIT/1000000000000 wait_time_s,
       CURRENT_SCHEMA, SQL_TEXT
FROM performance_schema.events_statements_history
WHERE thread_id=51
UNION ALL
SELECT THREAD_ID, f_convert_timer_to_utc(TIMER_START), f_convert_timer_to_utc(TIMER_END),
       TIMER_WAIT/1000000000000, CURRENT_SCHEMA, SQL_TEXT
FROM performance_schema.events_statements_current
WHERE thread_id=51;

Additional Tracking Scripts : The article provides scripts to periodically capture lock data and to temporarily disable MySQL's deadlock detector for deeper investigation.

innodb_deadlock_detect = off
innodb_lock_wait_timeout = 10
innodb_rollback_on_timeout = on

Root Cause Analysis : The deadlock stems from the unique‑index insertion logic. Transaction T1 acquires an X lock on record (10,26). Transaction T2 attempts to acquire an S lock on the same record and blocks. When T1 later inserts another row, it needs a gap‑insert‑intention lock, which conflicts with T2’s waiting S lock, creating a circular wait.

Solution & Recommendations : • Avoid over‑reliance on unique indexes for high‑concurrency batch inserts. • Ensure batch inserts are ordered to reduce lock contention. • Prefer the Read‑Committed isolation level where appropriate. • Use the provided scripts and performance_schema data to trace deadlocks in production. • Apply the described lock‑conflict analysis when similar issues arise.