mysql的一次死锁记录

表结构

create table lzy_test.test_lzy
(
    id   int   not null primary key,
    name varchar(128) default '' null,
    age  int          default 0  null,
    constraint key_name  unique (name)
);

表中数据

id	name	age
1	lzy1	0
4	lzy4	1233
6	lzy6	0
9	lzy9	0

事务死锁的过程

1. 隔离级别RC

事务A	事务B
begin ;
#update test_lzy set name='lzyxx' where id=6; 按照下面的方式来理解，（等同于但是稍微有点区别，但是不影响这里的结果） select * from test_lzy where name='lzy6' for update; #第一步 #这条记录原值name='lzy6'，这里的更新行为 会把name='lzy6'的索引记录标记为删除，并非真的删 # 此时加锁2个 # 锁A1#  索引：id      数据范围(id=6)           有个x-record 锁 # 锁A2#  索引：name    数据范围(name='lzy6')    有个 x-record 锁
	insert ignore into test_lzy values (122,'lzy6',88) ; #第二步 # 由于 name是唯一键，检查到 lzy6 重复（在事务A中已经标记为删除），此时就会额外加一个s-临键锁，锁住范围是(lzy4,lzy6],左开右闭 # 但是由于事务A中有一个 'lzy6'的x-record锁，所以这里(lzy4,lzy6) gap锁是获取到的，但是 'lzy6'的记录所 会被 事务A block # 所以此时的锁有一个 #锁B1# 索引：name 数据范围(lzy4,lzy6) 类型：S锁 # 锁等待： 事务A的锁A2#
update test_lzy set name='lzy5' where id=9; #第三步 # 这个时候 更新相当于 会插入一个新的索引name='lzy5'， 就会有一个插入意向锁，这里 会被事务B中的锁B1 block # 所以此时新增的锁有一个 #锁B2# 索引：name 数据范围(lzy4,lzy6) 类型：插入意向锁-x # 锁等待： 事务B的锁B1# #此时锁循环依赖 死锁

查看锁记录

第一步：

先忽略意向锁IX

 

执行第二步

后三行是 事务A的，前两行是事务B的

 第三步

触发死锁了

死锁日志

=====================================
2023-05-04 11:35:04 0x70000b9cb000 INNODB MONITOR OUTPUT
=====================================
Per second averages calculated from the last 6 seconds
-----------------
BACKGROUND THREAD
-----------------
srv_master_thread loops: 7 srv_active, 0 srv_shutdown, 2917 srv_idle
srv_master_thread log flush and writes: 0
----------
SEMAPHORES
----------
OS WAIT ARRAY INFO: reservation count 0
OS WAIT ARRAY INFO: signal count 0
RW-shared spins 0, rounds 0, OS waits 0
RW-excl spins 0, rounds 0, OS waits 0
RW-sx spins 0, rounds 0, OS waits 0
Spin rounds per wait: 0.00 RW-shared, 0.00 RW-excl, 0.00 RW-sx
------------------------
LATEST DETECTED DEADLOCK
------------------------
2023-05-04 11:34:35 0x70000ad6b000
*** (1) TRANSACTION:
TRANSACTION 2316, ACTIVE 38 sec inserting
mysql tables in use 1, locked 1
LOCK WAIT 2 lock struct(s), heap size 1136, 1 row lock(s), undo log entries 1
MySQL thread id 11, OS thread handle 123145496825856, query id 367 localhost 127.0.0.1 root update
/* ApplicationName=DataGrip 2021.1 */ insert ignore into test_lzy values (122,'lzy6',88)

*** (1) HOLDS THE LOCK(S):
RECORD LOCKS space id 3 page no 5 n bits 80 index key_name of table `lzy_test`.`test_lzy` trx id 2316 lock mode S waiting
Record lock, heap no 4 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
 0: len 4; hex 6c7a7936; asc lzy6;;
 1: len 4; hex 80000006; asc     ;;

*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 3 page no 5 n bits 80 index key_name of table `lzy_test`.`test_lzy` trx id 2316 lock mode S waiting
Record lock, heap no 4 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
 0: len 4; hex 6c7a7936; asc lzy6;;
 1: len 4; hex 80000006; asc     ;;

*** (2) TRANSACTION:
TRANSACTION 2315, ACTIVE 65 sec updating or deleting
mysql tables in use 1, locked 1
LOCK WAIT 4 lock struct(s), heap size 1136, 4 row lock(s), undo log entries 1
MySQL thread id 9, OS thread handle 123145496219648, query id 394 localhost 127.0.0.1 root updating
/* ApplicationName=DataGrip 2021.1 */ update test_lzy set  name='lzy5' where id=9

*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 3 page no 5 n bits 80 index key_name of table `lzy_test`.`test_lzy` trx id 2315 lock_mode X locks rec but not gap
Record lock, heap no 4 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
 0: len 4; hex 6c7a7936; asc lzy6;;
 1: len 4; hex 80000006; asc     ;;

*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 3 page no 5 n bits 80 index key_name of table `lzy_test`.`test_lzy` trx id 2315 lock_mode X locks gap before rec insert intention waiting
Record lock, heap no 4 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
 0: len 4; hex 6c7a7936; asc lzy6;;
 1: len 4; hex 80000006; asc     ;;

*** WE ROLL BACK TRANSACTION (1)
------------
TRANSACTIONS
------------
Trx id counter 2321
Purge done for trx's n:o < 2321 undo n:o < 0 state: running but idle
History list length 2
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 421944850257696, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 421944850256856, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 421944850255176, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 421944850254336, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 421944850253496, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 2315, ACTIVE 94 sec
4 lock struct(s), heap size 1136, 4 row lock(s), undo log entries 1
MySQL thread id 9, OS thread handle 123145496219648, query id 402 localhost 127.0.0.1 root

 

 分析：

日志中的事务1对应于事务B：黄色区域

1. 获取了一个s临键锁的gap部分：索引是name，值是(lzy4,lzy6)
2. 等待获取这个s临键锁的rec记录部分 : 

日志中的事务2对应于事务A：紫色区域

1. 获取了一个x记录锁 （索引name，值是‘lzy6’）
2. 等待获取这个s临键锁的rec记录部分

结论：