环境
-
5.7.19-log
-
REPEATABLE-READ
表结构
1
2
3
4
5
6
7
8
9
CREATE TABLE "dead_lock" (
"id" bigint(20) NOT NULL AUTO_INCREMENT COMMENT '主键',
"o1" int(11) DEFAULT NULL,
"f1" int(11) DEFAULT NULL,
"s1" int(11) DEFAULT NULL,
PRIMARY KEY ("id"),
KEY "o1_idx" ("o1") USING BTREE,
KEY "f1_idx" ("f1") USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=10 DEFAULT CHARSET=utf8;
数据
1
2
3
4
5
INSERT INTO dead_lock(o1, f1, s1) VALUES(1, 2, 1);
INSERT INTO dead_lock(o1, f1, s1) VALUES(3, 2, 1);
INSERT INTO dead_lock(o1, f1, s1) VALUES(4, 1, 1);
INSERT INTO dead_lock(o1, f1, s1) VALUES(1, 4, 1);
INSERT INTO dead_lock(o1, f1, s1) VALUES(3, 1, 1);
对应的SQL语句
1
2
3
4
5
-- 语句1
UPDATE dead_lock SET s1 = 0 WHERE (o1 = 1 AND f1 = 2 AND s1 = 1);
-- 语句2
UPDATE dead_lock SET s1 = 0 WHERE (o1 = 3 AND f1 = 2 AND s1 = 1);
死锁日志
省略/修改后
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
*** (1) TRANSACTION:
TRANSACTION 7275, ACTIVE 1 sec fetching rows
-- 省略
UPDATE dead_lock SET s1 = 0 WHERE (o1 = 1 AND f1 = 2)
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 2548 page no 773 n bits 328 index PRIMARY of table `test`.`dead_lock` trx id 7275 lock_mode X locks rec but not gap waiting
Record lock, heap no 56 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
-- 省略
*** (2) TRANSACTION:
TRANSACTION 2107, ACTIVE 1 sec fetching rows
-- 省略
UPDATE dead_lock SET s1 = 0 WHERE (o1 = 3 AND f1 = 2)
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 2548 page no 773 n bits 328 index PRIMARY of table `test`.`dead_lock` trx id 2107 lock_mode X locks rec but not gap
Record lock, heap no 56 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
-- 省略
*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 2748 page no 9736 n bits 1000 index f1 of table `test`.`dead_lock` trx id 2107 lock_mode X locks rec but not gap waiting
Record lock, heap no 379 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
-- 省略
加锁流程
不好复现, 属于MySQL内部的加锁顺序不一致
-
事务2获取二级非唯一索引o1锁
-
事务2获取二级非唯一索引o1对应的主键锁id=2
-
事务1获取二级非唯一索引o1锁
-
事务1获取二级非唯一索引对应的主键锁id=2
-
事务2获取二级非唯一索引f1锁
-
事务2获取二级非唯一索引f1对应的主键锁id=1 & id=2(与事物1冲突, 等待事务1释放id=1)
-
事务1获取二级非唯一索引f1锁(与事务2冲突, 发生死锁)
解决方法
- 先查询对应记录, 再根据主键进行更新, 避免根据多个索引进行更新
不同隔离级别下更新操作的加锁本质
- 根据主键更新只在对应主键加X锁
- 根据非主键二级索引更新先加二级索引X锁再回表加主键X锁
- RC: 二级索引为记录锁
- RR: 二级索引为Next-key锁
- Next-key锁 = 记录锁 + 前一个GAP锁
- GAP锁大多数只在RR级别, 目的为了解决幻读
常用查询语句
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
-- 行锁的争夺情况
show status like'innodb_row_lock%';
-- Innodb_row_lock_current_waits: 当前正在等待锁定的数量
-- Innodb_row_lock_time: 从系统启动到现在锁定总时间长度
-- Innodb_row_lock_time_avg: 每次等待所花平均时间
-- Innodb_row_lock_time_max: 从系统启动到现在等待最长的一次所花时间
-- Innodb_row_lock_waits: 系统启动后到现在总共等待的次数
-- 查看事务
SELECT * FROM INFORMATION_SCHEMA.INNODB_TRX;
-- 查看锁
SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCKS;
-- 查看锁等待
SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCK_WAITS;
-- 查看死锁日志
SHOW ENGINE innodb STATUS\G;
-- 杀掉某个事物
kill trx_mysql_thread_id(INFORMATION_SCHEMA.INNODB_TRX表中列对应的值)
总结
死锁的本质在于互相持有相互等待
- 代码层面多个事务保持一致的加锁顺序
- RR级别SQL避免GAP锁
- 避免根据二级索引条件进行更新/删除
- 避免大事务, 大事务中的SQL语句多, 执行时间长, 其它事务与其发生的冲突概率就大