Analyzing and Resolving MySQL Next‑Key Lock Deadlocks: A Practical Case Study

Background

Recently a deadlock occurred in the production environment. This article uses a concrete deadlock case to illustrate how MySQL Next-Key Lock works, from discovery and troubleshooting to reasoning and resolution.

Online Issue Traceback

The online service experienced abnormal processing, and investigation revealed a deadlock.

The business is simple and should not normally encounter deadlocks.

Four Necessary Conditions for Deadlock

Mutual exclusion: a resource can be used by only one transaction at a time.

Hold‑and‑wait: a transaction holding a resource may wait for another.

No preemption: held resources cannot be forcibly taken away.

Circular wait: a closed chain of transactions each waiting for the next.

Reconstructing the Scenario from Development Perspective

Multiple transaction threads exist.

Different threads need to hold the same lock.

Locks are held while waiting for other resources.

The order of acquiring locks is inconsistent.

Inconsistent lock order is crucial because it creates the circular‑wait condition.

Thread A holds a Next-Key Lock on temp1 and needs a lock on temp2 . Thread B holds a lock on temp2 and needs a lock on temp1 , forming a classic deadlock.

Deadlock Log

The DBA provides the deadlock log, which records the SQL statements at the exact moment MySQL detects the deadlock, but not all statements that held locks.

From the log we infer that transaction A held temp1 and transaction B held temp2 , even though the log only shows the conflicting statements.

Analyzing the Deadlock SQL

-- temp1
update temp1 set update_at = now(),process_def_key='a_workflow',task_id='549620565',is_active=3
where process_def_key = 'a_workflow' and task_id = '549620565';

-- temp2
insert into temp2( form_id,file_id,`name`,file_type, `size`,creater_id, create_at )
values
( 1000001510745,'1049840804692.jpg','xxxxx.jpg','demandList',241528,2126000000059295,'2022-03-11 11:44:22' );

Resource competition involves temp1 (task status table) and temp2 (attachment table).

Understanding Next‑Key Lock

In REPEATABLE READ (RR) isolation, MySQL uses Next-Key Lock , which is a combination of row lock and gap lock, forming a half‑open interval (‑∞, suprenum].

The lock is applied on index entries, whether primary, unique, or secondary.

Row lock + gap lock = Next-Key Lock , covering the interval (previous_key, current_key].

Example table test_table with an index on num shows how a SELECT ... FOR UPDATE creates seven Next-Key Locks covering intervals (-∞,0], (0,5], …, (25,+suprenum].

Locking Process for the DELETE Statement

The DELETE on temp2 uses the non‑unique index form_id . MySQL first locks the interval (previous_form_id, 1000001510741] and then, because the index is non‑unique, also locks the gap (1000001510741,+suprenum) as a gap lock.

Thus the DELETE does not lock only the single row but the whole range, which blocks the subsequent INSERT that targets a value inside that gap.

Experimental Verification

Two sessions are opened in Navicat:

-- Session 1
BEGIN;
DELETE FROM test_table WHERE num = 18;
SELECT SLEEP(10) FROM test_table LIMIT 1;
COMMIT;

-- Session 2
BEGIN;
INSERT INTO test_table (name,num) VALUES('xxxxx',21);
COMMIT;

Session 2 blocks until Session 1 commits, confirming the gap lock behavior.

Resolving the Deadlock

Since the gap lock originates from a non‑unique index, using a unique index (e.g., deleting by primary key id ) reduces the lock to a simple row lock, eliminating the gap lock.

delete from temp2 where id = 4567;

Therefore, rewrite DELETE/UPDATE statements to target rows via primary‑key IDs whenever possible.

Summary

Deadlocks can arise from seemingly unrelated business logic when they share common tables and non‑unique indexes, leading to intersecting Next-Key Locks . Analyzing logs, tracing code, and understanding lock intervals are essential to pinpoint the cause. Using primary‑key based DML and consistent lock acquisition order are practical ways to prevent such deadlocks.

Key locking principles:

The basic lock unit is Next-Key Lock , a half‑open interval.

Only objects accessed during the query are locked.

Equality queries on unique indexes downgrade Next-Key Lock to row locks.

Equality queries on non‑unique indexes that need to scan beyond the matching value downgrade to gap locks.