表结构

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

表中数据

idnameage
1lzy10
4lzy41233
6lzy60
9lzy90

事务死锁的过程

  1. 隔离级别RC
事务A事务B
begin ;
#update test_lzy setname='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 setname='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记录部分

结论：