MySQL死锁的详细分析方法

一、概述

MySQL中的死锁是指两个或多个事务在同一资源上相互等待对方释放锁，导致这些事务都无法继续执行的情况。从本质上讲，死锁是多个事务形成了一个等待环路，每个事务都在等待另一个事务所持有的锁资源，而这些事务又都不主动释放自己持有的锁，最终导致所有事务都无法向前推进。

二、产生条件与解决

2.1 产生条件

MySQL中死锁的产生需要同时满足以下四个条件：

互斥条件：同一时刻只能有一个事务持有某个特定的锁。
占有且等待：事务持有至少一个资源，并等待获取其他资源。
不可剥夺条件：事务获得的锁在其主动释放之前，不能被其他事务强行剥夺。
循环等待条件：当多个事务形成等待环路时，即发生死锁。例如，事务A等待事务B释放锁，而事务B又在等待事务A释放锁。

2.2 检测机制

InnoDB存储引擎默认启用了死锁检测机制。当发生死锁时，InnoDB会自动检测到这种情况，并选择回滚其中一个事务来打破死锁。InnoDB通常会选择回滚较小的事务（根据插入、更新或删除的行数来判断事务大小）。

根据MySQL官方文档，InnoDB使用等待图算法（wait-for graph）来检测死锁。当等待中的事务数量达到200的限制时，超过200个事务的等待列表被视为死锁，并且尝试检查等待列表的事务会被回滚。同样，如果锁定线程必须查看等待列表上的事务所拥有的超过1,000,000个锁，也会发生同样的错误。

2.3 死锁与锁等待超时的区别

死锁与锁等待超时是两个不同的概念：

死锁：是一种永远无法解决的互相等待状态，除非系统干预（如InnoDB的死锁检测机制自动回滚一个事务）。当检测到死锁时，MySQL会立即回滚其中一个事务，并返回错误信息：Deadlock found when trying to get lock; try restarting transaction。
锁等待超时：是指事务在等待获取锁时超过了系统设定的最大等待时间（由innodb_lock_wait_timeout参数控制，默认为50秒）。当发生锁等待超时时，MySQL会回滚当前事务，并返回错误信息：Lock wait timeout exceeded; try restarting transaction。

2.4 死锁检测的配置

在高并发系统上，当大量线程等待相同的锁时，死锁检测可能会导致系统性能下降。在这种情况下，有时禁用死锁检测并依赖innodb_lock_wait_timeout设置进行事务回滚可能更有效。可以使用innodb_deadlock_detect配置选项来禁用死锁检测。

2.5 死锁与表锁、行锁的关系

值得注意的是，表锁不会发生死锁，因为表锁的资源都是一次性获取的。死锁主要发生在行锁级别，特别是当多个事务以不同的顺序访问多行数据时。

如果innodb_table_locks = 1（默认值）且autocommit = 0，InnoDB能够感知表锁，MySQL层也能感知行级锁。否则，InnoDB无法检测由MySQL LOCK TABLES语句设置的表锁或由InnoDB以外的存储引擎设置的锁所导致的死锁。

2.6 死锁与数据库隔离级别的关系

死锁的发生与数据库的隔离级别密切相关：

读未提交（Read Uncommitted）：最低的隔离级别，不使用共享锁，因此死锁风险较低，但数据一致性问题较多。
读已提交（Read Committed）：不会出现幻读，不使用间隙锁，死锁风险低于可重复读。
可重复读（Repeatable Read）：MySQL的默认隔离级别，使用间隙锁防止幻读，死锁风险较高。
串行化（Serializable）：最高的隔离级别，事务串行执行，死锁风险最低，但并发性能最差。

在实际应用中，可重复读（RR）隔离级别下的死锁问题最为常见，因为InnoDB在此级别下使用了间隙锁来防止幻读，而间隙锁增加了锁的范围，提高了死锁的可能性。

三、原因和常见场景

3.1 基本原因

MySQL中的死锁产生主要是由于以下几个基本原因：

加锁顺序不一致：不同事务以不同的顺序获取锁，形成循环等待。
资源竞争：多个事务同时争夺相同的资源。
锁粒度和锁类型不合理：使用了过大的锁粒度或不适当的锁类型。
事务隔离级别：在RR（可重复读）隔离级别下，间隙锁的使用增加了死锁的可能性。
长事务：事务执行时间过长，增加了与其他事务发生锁冲突的概率。

3.2 常见场景

3.2.1 多个事务加锁顺序不一致

这是最常见的死锁场景。当两个或多个事务以不同的顺序锁定相同的行时，容易发生死锁。

示例：

事务A先锁定记录1，再尝试锁定记录2
事务B先锁定记录2，再尝试锁定记录1
结果：事务A等待事务B释放记录2的锁，事务B等待事务A释放记录1的锁，形成死锁

-- 事务A
BEGIN;
SELECT * FROM `table_name` WHERE id = 1 FOR UPDATE;
-- 此时获得了id=1的锁
SELECT * FROM `table_name` WHERE id = 2 FOR UPDATE;
-- 等待获取id=2的锁，因为事务B已经持有

-- 事务B
BEGIN;
SELECT * FROM `table_name` WHERE id = 2 FOR UPDATE;
-- 此时获得了id=2的锁
SELECT * FROM `table_name` WHERE id = 1 FOR UPDATE;
-- 等待获取id=1的锁，因为事务A已经持有

3.2.2 间隙锁与插入意向锁的冲突

在RR隔离级别下，InnoDB使用间隙锁来防止幻读。间隙锁之间不会互相阻塞，但插入意向锁会与间隙锁冲突。

示例：

事务A和B先后在(20, 30)的区间上加了间隙锁
事务A尝试在该区间插入数据，需要获取插入意向锁
事务B的间隙锁阻止了事务A获取插入意向锁
同时，事务B也尝试插入数据，被事务A的间隙锁阻止
结果：形成死锁

3.2.3 范围查询中的行锁加锁顺序问题

在范围查询时，MySQL会一行一行地加锁，如果不同事务的加锁顺序不同，也会导致死锁。

示例：

事务A的查询条件为id < 30，加锁顺序为：id = 15 -> 18 -> 20
事务B走的是二级索引age，加锁顺序为：id = 18 -> 20 -> 15 -> 49
结果：事务A先锁15，再锁18，而事务B先锁18，再锁15，形成死锁

3.2.4 主键索引和唯一二级索引的特殊加锁机制

在插入操作时，主键索引和唯一二级索引会先生成S锁来判断是否唯一，然后再升级为X锁。这种特殊的加锁机制也可能导致死锁。

示例：

事务A插入一条记录，先获取S锁检查唯一性
事务B持有相关记录的X锁
事务A的S锁与事务B的X锁冲突，事务A等待
同时，事务B也需要获取事务A持有的某些锁
结果：形成死锁

3.2.5 不存在记录的锁定与插入操作冲突

当对不存在的记录进行锁定时，MySQL会锁定一个范围（间隙锁），这可能与其他事务的插入操作冲突。

示例：

-- 事务A
SELECT * FROM `table_name` WHERE id = 22 FOR UPDATE;
-- 表中不存在id=22的记录，会加间隙锁

-- 事务B
SELECT * FROM `table_name` WHERE id = 23 FOR UPDATE;
-- 表中不存在id=23的记录，也会加间隙锁

-- 事务A尝试插入id=22的记录
INSERT INTO `table_name` VALUES(22, 'value');
-- 等待事务B释放间隙锁

-- 事务B尝试插入id=23的记录
INSERT INTO `table_name` VALUES(23, 'value');
-- 等待事务A释放间隙锁，形成死锁

3.2.6 批量操作中的死锁

在批量更新或删除操作中，如果不同事务的操作涉及相同的记录集但顺序不同，也容易发生死锁。

示例：

事务A批量更新id为1,2,3的记录
事务B批量更新id为3,2,1的记录
结果：可能形成死锁

3.2.7 复杂SQL语句中的隐式锁定

有些复杂的SQL语句（如JOIN、子查询等）可能会隐式地锁定多个表或多行数据，增加了死锁的风险。

3.2.8 长事务与短事务混合执行

当长事务与多个短事务并发执行时，长事务持有锁的时间较长，增加了与短事务发生锁冲突的概率，容易导致死锁。

四、实际案例分析

4.1 案例一：交叉更新导致的死锁

业务背景

在仓储管理系统中，多个事务同时更新库存占用表（stock_occupy）中的不同记录，由于加锁顺序不一致导致死锁。

SQL示例

-- 事务1
UPDATE `stock_occupy`
SET update_time = NOW(),
    update_user = 'WAPS',
    qty_out_occupy=qty_out_occupy + 12.0000
WHERE map_area_id = 608
    AND goods_no='EMG4418433215231'
    AND owner_no='0'
    AND lot_no='-1'
    AND product_level='100'
    AND org_no = '10'
    AND distribute_no = '10'
    AND warehouse_no = '126'
    AND map_area_id = 608;

-- 事务2
UPDATE `stock_occupy`
SET update_time = NOW(),
    update_user = 'WAPS',
    qty_out_occupy=qty_out_occupy + 11.0000
WHERE map_area_id = 608
    AND goods_no='EMG4418442253742'
    AND owner_no='0'
    AND lot_no='-1'
    AND product_level='100'
    AND org_no = '10'
    AND distribute_no = '10'
    AND warehouse_no = '126'
    AND map_area_id = 608;

死锁分析

两个事务都在使用二级联合唯一索引idx_map_goods_product_lot_owner进行更新操作
事务1先锁定了主键为273892的记录，然后尝试锁定主键为279349的记录
事务2先锁定了主键为279349的记录，然后尝试锁定主键为273892的记录
形成了循环等待，导致死锁
InnoDB检测到死锁后回滚了事务2

表结构

CREATE TABLE `stock_occupy` (
`id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '自增id',
`map_area_id` bigint(20) NOT NULL COMMENT '地图区域ID',
`goods_no` varchar(50) NOT NULL COMMENT '商品编号',
`product_level` varchar(50) NOT NULL COMMENT '商品等级',
`lot_no` varchar(50) NOT NULL COMMENT '批次号',
`owner_no` varchar(50) NOT NULL COMMENT '货主编号',
PRIMARY KEY (`id`),
UNIQUE KEY `idx_map_goods_product_lot_owner` (`map_area_id`,`goods_no`,`product_level`,`lot_no`,`owner_no`)
)

4.2 案例二：订单表与库存表死锁

业务背景

在电商系统中，订单处理和库存管理是两个紧密关联的业务。当多个事务同时处理订单和更新库存时，如果加锁顺序不一致，容易导致死锁。

SQL示例

-- 事务A
BEGIN;
UPDATE orders SET status = 'PROCESSING' WHERE order_id = 12345;
UPDATE inventory SET quantity = quantity - 1 WHERE product_id = 101;
COMMIT;

-- 事务B
BEGIN;
UPDATE inventory SET quantity = quantity - 1 WHERE product_id = 101;
UPDATE orders SET status = 'PROCESSING' WHERE order_id = 12345;
COMMIT;

死锁分析

事务A先锁定了订单表中的记录，然后尝试锁定库存表中的记录
事务B先锁定了库存表中的记录，然后尝试锁定订单表中的记录
形成了循环等待，导致死锁
解决方案是统一操作顺序，例如所有事务都先更新订单表，再更新库存表

4.3 案例三：范围查询与插入操作导致的死锁

业务背景

在高并发系统中，当一个事务进行范围查询并锁定记录，同时另一个事务尝试在该范围内插入新记录时，可能会导致死锁。

SQL示例

-- 事务1
BEGIN;
SELECT * FROM products WHERE price BETWEEN 10 AND 20 FOR UPDATE;
-- 其他操作...
COMMIT;

-- 事务2
BEGIN;
INSERT INTO products (name, price) VALUES ('New Product', 15);
COMMIT;

死锁分析

事务2执行范围查询并获取了X锁，锁定了价格在10到20之间的所有记录以及相应的间隙
事务1尝试在该范围内插入新记录，需要获取插入意向锁
插入意向锁与间隙锁冲突，导致事务1等待
如果事务2还需要获取事务1持有的某些锁，就会形成循环等待，导致死锁
解决方案包括：降低隔离级别（从RR降到RC可以避免间隙锁）、缩小锁范围、调整事务逻辑等

4.4 案例四：唯一键冲突导致的死锁

业务背景

当多个事务同时尝试插入具有唯一键约束的记录时，可能会导致死锁。

SQL示例

-- 事务1
BEGIN;
INSERT INTO users (id, username, email) VALUES (NULL, 'user1', 'user1@example.com');
-- 其他操作...
COMMIT;

-- 事务2
BEGIN;
INSERT INTO users (id, username, email) VALUES (NULL, 'user2', 'user1@example.com');
-- 注意：email与事务1相同，违反唯一约束
COMMIT;

死锁分析

事务1插入记录时，会先获取S锁检查唯一性，然后升级为X锁
事务2也尝试插入具有相同email的记录，也需要获取S锁检查唯一性
如果事务1已经获取了S锁但还未升级为X锁，事务2也可以获取S锁
当事务1尝试升级为X锁时，需要等待事务2释放S锁
当事务2尝试升级为X锁时，需要等待事务1释放S锁
形成了循环等待，导致死锁

4.5 案例五：批量操作中的死锁

业务背景

在批量处理数据时，如果不同事务以不同的顺序访问相同的记录集，容易导致死锁。

SQL示例

-- 事务1
BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE id IN (1, 2, 3, 4, 5);
COMMIT;

-- 事务2
BEGIN;
UPDATE accounts SET balance = balance + 200 WHERE id IN (5, 4, 3, 2, 1);
COMMIT;

死锁分析

事务1按照id升序加锁：1, 2, 3, 4, 5
事务2按照id降序加锁：5, 4, 3, 2, 1
如果两个事务同时执行，事务1可能锁定了id=1,2,3，事务2可能锁定了id=5,4
当事务1尝试锁定id=4,5时，需要等待事务2释放锁
当事务2尝试锁定id=3,2,1时，需要等待事务1释放锁
形成了循环等待，导致死锁
解决方案是统一加锁顺序，例如都按照id升序加锁

4.6 死锁日志分析方法

4.6.1 死锁日志格式

简化后的死锁日志格式如下：

InnoDB: *** (1) TRANSACTION:
InnoDB: *** (1) WAITING FOR THIS LOCK TO BE GRANTED:
InnoDB: *** (2) TRANSACTION:
InnoDB: *** (2) HOLDS THE LOCK(S):
InnoDB: *** (2) WAITING FOR THIS LOCK TO BE GRANTED:
InnoDB: *** WE ROLL BACK TRANSACTION (1)

4.6.2 分析步骤

识别事务信息：查看每个事务正在执行的SQL语句
分析锁信息：
- 锁类型（X锁、S锁、意向锁等）
- 锁范围（记录锁、间隙锁、Next-Key锁等）
- 锁定的索引和记录
分析等待关系：确定哪个事务持有锁，哪个事务等待锁
确定死锁原因：通常是由于加锁顺序不一致导致的循环等待
制定解决方案：调整加锁顺序、优化事务逻辑、调整隔离级别等

4.6.3 常见死锁排查工具

SHOW ENGINE INNODB STATUS：查看最近一次的死锁信息

查询information_schema中的锁相关表：

SELECT * FROM information_schema.INNODB_TRX;
SELECT * FROM information_schema.INNODB_LOCKS;
SELECT * FROM information_schema.INNODB_LOCK_WAITS;

开启死锁日志记录：

SET GLOBAL innodb_status_output = ON;
SET GLOBAL innodb_status_output_locks = ON;

innodb_trx: 记录当前运行的所有事务。
innodb_locks: 记录当前出现的锁。
innodb_lock_waits: 记录锁等待的对应关系。

下面对innodb_trx表的每个字段进行解释：

字段	描述
trx_id	事务ID
trx_state	事务状态，有以下几种状态：running、lock wait、rolling back和committing
trx_started	事务开始时间
trx_requested_lock_id	事务当前正在等待锁的标识。可以和INNODB_LOCKS表JOIN以得到更多详细信息
trx_wait_started	事务开始等待的时间
trx_weight	事务的权重
trx_mysql_thread_id	事务线程ID。可以和PROCESSLIST表JOIN
trx_query	事务正在执行的SQL语句
trx_operation_state	事务当前操作状态
trx_tables_in_use	当前事务执行的SQL中使用的表的个数
trx_tables_locked	当前执行SQL的行锁数量
trx_lock_structs	事务保留的锁数量
trx_lock_memory_bytes	事务锁住的内存大小，单位为bytes
trx_rows_locked	事务锁住的记录数，包含标记为deleted，并且已经保存到磁盘但对事务不可见的行
trx_rows_modified	事务更改的行数
trx_concurrency_tickets	事务并发票数
trx_isolation_level	当前事务的隔离级别
trx_unique_checks	是否打开唯一性检查的标识
trx_foreign_key_checks	是否打开外键检查的标识
trx_last_foreign_key_error	最后一次的外键错误信息
trx_adaptive_hash_latched	自适应散列索引是否被当前事务锁住的标识
trx_adaptive_hash_timeout	是否立刻放弃为自适应散列索引搜索LATCH的标识

下面对innodb_locks表的每个字段进行解释：

字段	描述
lock_id	锁ID
lock_trx_id	拥有锁的事务ID。可以和INNODB_TRX表JOIN得到事务的详细信息
lock_mode	锁的模式。有如下锁类型：行级锁包括：S、X、IS、IX，分别代表：共享锁、排它锁、意向共享锁、意向排它锁。表级锁包括：S_GAP、X_GAP、IS_GAP、IX_GAP和AUTO_INC，分别代表共享间隙锁、排它间隙锁、意向共享间隙锁、意向排它间隙锁和自动递增锁
lock_type	锁的类型。 RECORD代表行级锁，TABLE代表表级锁
lock_table	被锁定的或者包含锁定记录的表的名称
lock_index	当LOCK_TYPE=’RECORD’时，表示索引的名称；否则为NULL
lock_space	当LOCK_TYPE=’RECORD’时，表示锁定行的表空间ID；否则为NULL
lock_page	当LOCK_TYPE=’RECORD’时，表示锁定行的页号；否则为NULL
lock_rec	当LOCK_TYPE=’RECORD’时，表示一堆页面中锁定行的数量，亦即被锁定的记录号；否则为NULL
lock_data	当LOCK_TYPE=’RECORD’时，表示锁定行的主键；否则为NULL

查看innodb_lock_waits表结构。

字段	描述
requesting_trx_id	请求事务的ID
requested_lock_id	事务所等待的锁定的ID。可以和INNODB_LOCKS表JOIN
blocking_trx_id	阻塞事务的ID
blocking_lock_id	某一事务的锁的ID，该事务阻塞了另一事务的运行。可以和INNODB_LOCKS表JOIN

五、解决方案与最佳实践

5.1 死锁预防策略

5.1.1 保持一致的加锁顺序

死锁的主要原因之一是不同事务以不同的顺序获取锁，形成循环等待。因此，最有效的预防策略是确保所有事务按照相同的顺序获取锁。

最佳实践：

对多表操作时，始终按照固定的顺序访问表（如按表名字母顺序）
对同一表的多行操作，按照主键或索引的顺序进行访问（如ID升序）
使用SELECT ... FOR UPDATE时，可以添加ORDER BY子句确保加锁顺序一致

示例：

-- 推荐：统一按ID升序加锁
UPDATE accounts SET balance = balance - 100 WHERE id IN (1, 2, 3, 4, 5) ORDER BY id;

-- 不推荐：不同事务使用不同的加锁顺序
-- 事务1: UPDATE accounts SET balance = balance - 100 WHERE id IN (1, 2, 3, 4, 5);
-- 事务2: UPDATE accounts SET balance = balance + 200 WHERE id IN (5, 4, 3, 2, 1);

5.1.2 减小锁粒度

锁粒度越大，发生锁冲突的可能性就越高。使用行级锁而非表级锁，可以显著减少死锁的可能性。

最佳实践：

优先使用InnoDB等支持行级锁的存储引擎
尽量使用索引条件进行数据检索，避免全表扫描导致的表锁
避免锁定过多的行，只锁定必要的数据

示例：

-- 推荐：使用索引列作为条件，只锁定必要的行
SELECT * FROM orders WHERE order_id = 12345 FOR UPDATE;

-- 不推荐：无索引条件导致全表扫描和表锁
SELECT * FROM orders WHERE customer_name = 'John' FOR UPDATE;

5.1.3 减少事务持续时间

长事务会长时间持有锁，增加与其他事务发生锁冲突的概率。

最佳实践：

将大事务拆分为多个小事务
只在必要的时候开启事务，尽早提交
避免在事务中进行耗时的操作，如复杂计算、网络I/O等
避免用户交互式操作在事务内进行

示例：

-- 推荐：小事务，及时提交
BEGIN;
UPDATE inventory SET quantity = quantity - 1 WHERE product_id = 101;
COMMIT;

BEGIN;
INSERT INTO order_items (order_id, product_id, quantity) VALUES (12345, 101, 1);
COMMIT;

-- 不推荐：大事务，长时间持有锁
BEGIN;
-- 复杂的业务逻辑和多表操作
-- ...可能持续很长时间的操作
COMMIT;

5.1.4 合理设置隔离级别

MySQL默认的隔离级别是REPEATABLE READ（可重复读），在此级别下会使用间隙锁来防止幻读，但间隙锁也增加了死锁的可能性。

最佳实践：

根据业务需求选择合适的隔离级别
如果业务允许，考虑使用READ COMMITTED隔离级别，可以避免间隙锁
在特定场景下，可以使用SELECT ... FOR UPDATE NOWAIT或SELECT ... FOR UPDATE SKIP LOCKED来避免长时间的锁等待

示例：

-- 设置会话隔离级别为READ COMMITTED，避免间隙锁
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;

-- 使用NOWAIT选项，避免长时间等待锁（MySQL 8.0+）
SELECT * FROM products WHERE id = 10 FOR UPDATE NOWAIT;

5.2 死锁检测与处理

5.2.1 开启死锁检测

MySQL默认开启了死锁检测，但在高并发场景下，死锁检测可能会消耗大量资源。

最佳实践：

保持innodb_deadlock_detect参数开启（默认值为ON）
在极高并发且有完善死锁预防措施的场景下，可以考虑关闭死锁检测，依赖innodb_lock_wait_timeout进行超时回滚
合理设置innodb_lock_wait_timeout参数（默认50秒）

示例：

-- 查看死锁检测是否开启
SHOW VARIABLES LIKE 'innodb_deadlock_detect';

-- 设置锁等待超时时间（单位：秒）
SET GLOBAL innodb_lock_wait_timeout = 20;

5.2.2 死锁日志分析

当发生死锁时，MySQL会自动检测并回滚其中一个事务。通过分析死锁日志，可以找出死锁的原因并进行优化。

最佳实践：

使用SHOW ENGINE INNODB STATUS命令查看最近一次的死锁信息
开启死锁日志记录，便于后续分析
分析死锁日志中的事务信息、锁信息和等待关系

示例：

-- 查看最近一次的死锁信息
SHOW ENGINE INNODB STATUS;

-- 开启死锁日志记录
SET GLOBAL innodb_status_output = ON;
SET GLOBAL innodb_status_output_locks = ON;

-- 查询当前的锁等待情况
SELECT * FROM information_schema.INNODB_TRX;
SELECT * FROM information_schema.INNODB_LOCKS;
SELECT * FROM information_schema.INNODB_LOCK_WAITS;

5.2.3 应用层重试机制

即使采取了各种预防措施，死锁仍然可能发生。在应用层实现重试机制可以提高系统的健壮性。

最佳实践：

捕获死锁异常（MySQL错误码：1213）
实现指数退避重试策略，避免立即重试导致的连续死锁
设置最大重试次数，避免无限重试

示例（Java代码）：

public void test() {
    int maxRetries = 3;
    int retryCount = 0;
    boolean success = false;

    while (!success && retryCount < maxRetries) {
        try {
            // 执行数据库操作
            connection.setAutoCommit(false);
            // ... SQL操作
            connection.commit();
            success = true;
        } catch (SQLException e) {
            if (e.getErrorCode() == 1213) { // MySQL死锁错误码
                // 回滚事务
                connection.rollback();
                // 指数退避
                long waitTime = (long) Math.pow(2, retryCount) * 100;
                Thread.sleep(waitTime);
                retryCount++;
            } else {
                throw e; // 其他错误直接抛出
            }
        } finally {
            connection.setAutoCommit(true);
        }
    }

    if (!success) {
        // 达到最大重试次数仍失败，进行错误处理
    }
}

5.3 特定场景的死锁解决方案

5.3.1 唯一键冲突导致的死锁

当多个事务同时尝试插入具有相同唯一键值的记录时，可能会导致死锁。

解决方案：

使用INSERT ... ON DUPLICATE KEY UPDATE语句代替先查询后插入的模式
使用REPLACE INTO语句（注意：会删除并重新插入记录）
在应用层进行唯一性检查，避免并发插入相同的唯一键值

示例：

-- 推荐：使用INSERT ... ON DUPLICATE KEY UPDATE
INSERT INTO users (username, email)
VALUES ('user1', 'user1@example.com')
ON DUPLICATE KEY UPDATE username = VALUES(username);

-- 或者使用REPLACE INTO
REPLACE INTO users (username, email)
VALUES ('user1', 'user1@example.com');

5.3.2 范围查询与插入操作的死锁

在RR隔离级别下，范围查询会使用间隙锁，可能与插入操作发生冲突导致死锁。

解决方案：

降低隔离级别至READ COMMITTED，避免间隙锁
优化查询条件，减少锁定的范围
调整业务逻辑，避免在同一事务中既有范围查询又有插入操作

示例：

-- 设置隔离级别为READ COMMITTED
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;

-- 优化查询条件，使用精确匹配而非范围查询
SELECT * FROM products WHERE id = 10 FOR UPDATE;
-- 而非：SELECT * FROM products WHERE price BETWEEN 10 AND 20 FOR UPDATE;

5.3.3 批量操作中的死锁

批量更新或删除操作可能会导致大量的锁竞争，增加死锁的风险。

解决方案：

将批量操作拆分为多个小批次，减少单次操作的锁定范围
在批量操作中添加排序条件，确保加锁顺序一致
考虑使用临时表进行批量操作，减少对原表的锁定时间

示例：

-- 推荐：分批处理，每批100条记录
SET @batch_size = 100;
SET @offset = 0;

REPEAT
UPDATE accounts
SET status = 'inactive'
WHERE last_login_date < '2023-01-01'
ORDER BY id
LIMIT @batch_size;

 SET @offset = @offset + ROW_COUNT();
 -- 每批次之间短暂休息，减少锁竞争
 DO SLEEP(0.1);
UNTIL @offset = 0 END REPEAT;

5.3.4 多表关联操作的死锁

涉及多个表的操作容易导致复杂的锁依赖关系，增加死锁的风险。

解决方案：

统一多表操作的顺序，例如按表名字母顺序
减少一个事务中涉及的表数量
考虑使用乐观锁替代悲观锁，减少锁竞争

示例：

-- 推荐：统一的表操作顺序
BEGIN;
-- 先操作accounts表
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
-- 再操作orders表
INSERT INTO orders (account_id, amount) VALUES (1, 100);
-- 最后操作transactions表
INSERT INTO transactions (account_id, order_id, amount) VALUES (1, LAST_INSERT_ID(), 100);
COMMIT;

5.4 索引优化与死锁预防

5.4.1 合理设计索引

索引不仅影响查询性能，还直接影响锁的粒度和范围，进而影响死锁的发生概率。

最佳实践：

确保查询条件中使用了适当的索引，避免全表扫描
为经常在WHERE子句中出现的列创建索引
考虑创建复合索引，优化多条件查询
避免过度索引，每个索引都会增加写操作的开销

示例：

-- 为经常用于查询和更新的条件列创建索引
CREATE INDEX idx_order_status ON orders (status);

-- 为多条件查询创建复合索引
CREATE INDEX idx_product_category_status ON products (category_id, status);

5.4.2 避免索引失效

当索引失效时，MySQL可能会从行锁升级为表锁，大大增加死锁的风险。

最佳实践：

避免在索引列上使用函数或表达式
避免在索引列上进行类型转换
避免使用!=或<>操作符
避免在索引列上使用OR操作符（考虑使用UNION ALL替代）
注意LIKE语句中的前缀通配符会导致索引失效

示例：

-- 推荐：索引有效
SELECT * FROM orders WHERE order_date = '2023-06-01' FOR UPDATE;

-- 不推荐：函数导致索引失效
SELECT * FROM orders WHERE YEAR(order_date) = 2023 FOR UPDATE;

-- 推荐：使用UNION ALL代替OR
SELECT * FROM products WHERE category_id = 1 FOR UPDATE
UNION ALL
SELECT * FROM products WHERE status = 'active' FOR UPDATE;

-- 不推荐：OR可能导致索引失效
SELECT * FROM products WHERE category_id = 1 OR status = 'active' FOR UPDATE;

5.5 业务层面的死锁预防

5.5.1 乐观并发控制

在读多写少的场景中，使用乐观并发控制可以减少锁的使用，降低死锁的风险。

最佳实践：

使用版本号或时间戳实现乐观锁
在更新时检查版本号是否变化
适用于读多写少且冲突概率低的场景

示例：

-- 表结构包含version字段
CREATE TABLE products (
id INT PRIMARY KEY,
name VARCHAR(100),
price DECIMAL(10,2),
version INT
);

-- 读取数据和版本号
SELECT id, name, price, version FROM products WHERE id = 1;

-- 更新时检查版本号
UPDATE products
SET price = 29.99, version = version + 1
WHERE id = 1 AND version = 5;

-- 如果影响行数为0，说明数据已被其他事务修改

5.5.2 避免用户交互式操作在事务内

用户交互式操作（如等待用户输入）会导致事务长时间持有锁，增加死锁的风险。

最佳实践：

在获取用户输入后再开启事务
将事务限制在数据库操作的必要范围内
避免在事务中进行网络请求、文件I/O等可能阻塞的操作

示例（伪代码）：

// 不推荐
beginTransaction();
showUserForm();
waitForUserInput();
updateDatabase();
commitTransaction();

// 推荐
showUserForm();
waitForUserInput();
beginTransaction();
updateDatabase();
commitTransaction();

5.5.3 使用队列处理高并发写操作

在高并发场景下，使用队列可以将并行操作转为串行操作，从根本上避免死锁。

最佳实践：

使用消息队列（如RabbitMQ、Kafka）将高并发写操作转为串行处理
对同一资源的操作放入同一队列，确保按顺序处理
实现幂等性处理，避免重复操作

5.6 监控与预警

5.6.1 建立死锁监控系统

主动监控死锁的发生可以帮助及时发现和解决问题，防止死锁影响系统稳定性。

最佳实践：

定期检查SHOW ENGINE INNODB STATUS输出中的死锁信息
监控information_schema.INNODB_METRICS中的锁相关指标
设置死锁告警阈值，当死锁频率超过阈值时触发告警
记录和分析死锁日志，找出死锁的模式和根本原因

示例：

-- 查询锁等待和死锁统计信息
SELECT name, count
FROM information_schema.INNODB_METRICS
WHERE name IN ('lock_deadlocks', 'lock_timeouts', 'lock_rec_lock_waits');

-- 重置计数器
SET GLOBAL innodb_monitor_reset = 'lock_deadlocks';

5.6.2 性能测试与压力测试

在生产环境部署前进行充分的性能测试和压力测试，可以提前发现潜在的死锁问题。

最佳实践：

模拟生产环境的并发负载
测试各种极端情况下的系统行为
使用工具（如JMeter、Gatling）进行并发测试
分析测试结果，优化数据库设计和应用代码

六、总结

MySQL死锁是高并发系统中常见的问题，但通过合理的设计和最佳实践，可以大大减少死锁的发生。预防死锁的关键在于：

保持一致的加锁顺序：确保所有事务按照相同的顺序获取锁
减小锁粒度：使用行级锁而非表级锁，只锁定必要的数据
减少事务持续时间：将大事务拆分为小事务，尽早提交
合理设置隔离级别：根据业务需求选择合适的隔离级别
优化索引设计：确保查询条件使用了适当的索引，避免索引失效
实现应用层重试机制：捕获死锁异常并实现智能重试策略
监控与分析：建立死锁监控系统，及时发现和解决问题

随着MySQL版本的不断更新和优化，死锁检测和处理机制也在不断完善。在MySQL 8.0及以后的版本中，引入了NOWAIT和SKIP LOCKED选项，为处理高并发场景下的锁竞争提供了更多选择。

posted @ 2022-04-26 09:43 夏尔_717 阅读(858) 评论(0) 收藏举报

刷新页面返回顶部

ciel_717

MySQL死锁的详细分析方法

一、概述

二、产生条件与解决

2.1 产生条件

2.2 检测机制

2.3 死锁与锁等待超时的区别

2.4 死锁检测的配置

2.5 死锁与表锁、行锁的关系

2.6 死锁与数据库隔离级别的关系

三、原因和常见场景

3.1 基本原因

3.2 常见场景

3.2.1 多个事务加锁顺序不一致

3.2.2 间隙锁与插入意向锁的冲突

3.2.3 范围查询中的行锁加锁顺序问题

3.2.4 主键索引和唯一二级索引的特殊加锁机制

3.2.5 不存在记录的锁定与插入操作冲突

3.2.6 批量操作中的死锁

3.2.7 复杂SQL语句中的隐式锁定

3.2.8 长事务与短事务混合执行

四、实际案例分析

4.1 案例一：交叉更新导致的死锁

4.2 案例二：订单表与库存表死锁

4.3 案例三：范围查询与插入操作导致的死锁

4.4 案例四：唯一键冲突导致的死锁

4.5 案例五：批量操作中的死锁

4.6 死锁日志分析方法

4.6.1 死锁日志格式

4.6.2 分析步骤

4.6.3 常见死锁排查工具

五、解决方案与最佳实践

5.1 死锁预防策略

5.1.1 保持一致的加锁顺序

5.1.2 减小锁粒度

5.1.3 减少事务持续时间

5.1.4 合理设置隔离级别

5.2 死锁检测与处理

5.2.1 开启死锁检测

5.2.2 死锁日志分析

5.2.3 应用层重试机制

5.3 特定场景的死锁解决方案

5.3.1 唯一键冲突导致的死锁

5.3.2 范围查询与插入操作的死锁

5.3.3 批量操作中的死锁

5.3.4 多表关联操作的死锁

5.4 索引优化与死锁预防

5.4.1 合理设计索引

5.4.2 避免索引失效

5.5 业务层面的死锁预防

5.5.1 乐观并发控制

5.5.2 避免用户交互式操作在事务内

5.5.3 使用队列处理高并发写操作

5.6 监控与预警

5.6.1 建立死锁监控系统

5.6.2 性能测试与压力测试

六、总结

公告