本文共 3032 字，大约阅读时间需要 10 分钟。

MySQL Deadlock Analysis Case Study

1. Introduction

Deadlocks, in general, are a fascinating yet challenging technical problem that most DBAs and developers encounter during their careers. This article will walk through a series of case studies to help those interested better understand deadlocks in MySQL environments.

2. Case Analysis

2.1 Business Scenario

A development team encountered a deadlock issue while initializing data. Their approach involved batch inserting multiple records into a table with a unique constraint. The unique constraint caused deadlocks due to adjacent values being inserted in sequence.

2.2 Environment Setup

• MySQL Version: 5.6.24
• Transaction Isolation Level: RR (Read Committed)

2.3 Technical Details

The table tc has the following structure:

CREATE TABLE `tc` (    `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT '自增ID',    `c1` bigint(20) unsigned NOT NULL DEFAULT '0',    `c2` bigint(20) unsigned NOT NULL DEFAULT '0',    `c3` bigint(20) unsigned NOT NULL DEFAULT '0',    `c4` tinyint(4) NOT NULL DEFAULT '0',    `c5` tinyint(4) NOT NULL DEFAULT '0',    `created_at` datetime NOT NULL DEFAULT '1970-01-01 08:00:00',    `deleted_at` datetime NOT NULL DEFAULT '1970-01-01 08:00:00',    PRIMARY KEY (`id`),    UNIQUE KEY `uniq_cid_bid_dt_tid` (`c1`, `c2`, `deleted_at`, `c3`),    ENGINE=InnoDB AUTO_INCREMENT=19 DEFAULT CHARSET=utf8mb4)

3. Deadlock Log Explanation

The deadlock log reveals the following:

4. Deadlock Causes

4.1 Insert Statement Lock Mechanism

• Stage 1: Unique constraint check requires a shared lock (S) and an ordinary lock (ORDINARY).
• Stage 2: After inserting, the system applies a gap lock (INSERT_INTENTION) to prevent adjacent insertions.
• Stage 3: Finally, it acquires an exclusive lock (X) and a record lock (REC_NOT_GAP).

4.2 Compatibility Matrix

• Shared (S) and Exclusive (X) locks do not conflict.
• Gap (GAP) and Next-Key (S Next-key) locks block insert intentions.
• Record (REC) and Next-Key (S Next-key) locks conflict.
• Existing locks (REC, GAP) do not block new locks.

5. Deadlock Resolution

Deadlocks caused by concurrent inserts can be challenging to resolve at the SQL level. However, some strategies include:

• Adjusting the order of operations: Simplify the insertion logic to reduce concurrency.
• Modifying the unique index: Distribute initial data evenly across the table to avoid adjacent conflicts.

6. Conclusion

The deadlock in this case arises from concurrent inserts of adjacent records due to unique constraints. The system's lock mechanisms, particularly the Next-Key Lock, create a cycle of waiting transactions, leading to deadlock.

7. Extended Reading

For further understanding of MySQL deadlocks and lock mechanisms, we recommend exploring:

• InnoDB transaction isolation levels
• Deadlock prevention strategies
• Index optimization techniques

转载地址：http://qpn.baihongyu.com/