Table: CREATE TABLE test(no VARCHAR(20) PRIMARY KEY, idx int)
Session1 :
insert into test(no, idx) values ('5', 14);
select sleep(2);
insert into test(no, idx) values ('7', 17);
Session:
update test set idx = 31 where no = 5 ;
这里锁的情形符合案例 5的描述，但是，有些奇怪，我试过，跟Session1 的插入的数据有关，也和Session2的no不用 字符而用 数字有关，极其特殊。。。

数据版本是

Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MySQL connection id is 12297574
Server version: 5.6.16-log Source distribution

看上去是 5.6.16
不知道 mariaDb 跟 mysql 区别大不大

数据表是这样的

CREATE TABLE `test` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `version` int(11) NOT NULL DEFAULT '0',
  `cid` bigint(20) DEFAULT NULL,
  `uid` varchar(32) DEFAULT NULL,
  `flag` int(11) NOT NULL DEFAULT '0',
  `cid_create_time` datetime DEFAULT NULL,
  `uid_create_time` datetime DEFAULT NULL,
  `cid_delete_time` datetime DEFAULT NULL,
  `uid_delete_time` datetime DEFAULT NULL,
  `last_updated` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `date_created` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `udx_cid` (`cid`) USING BTREE,
  UNIQUE KEY `udx_uid` (`uid`) USING BTREE
) ENGINE=InnoDB  DEFAULT CHARSET=utf8mb4

已有的数据，省略了前面的，数据按照 cid uid 有序的

+-----+---------------------+---------------------+
| id  | cid                 | uid                 |
+-----+---------------------+---------------------+
....
....
| 201 | 1774864160186843136 | 1774864180210307072 |
| 202 | 1774864160547553280 | 1774864180856229888 |
| 205 | 1774866745815875584 | 1774866761200439296 |
| 206 | 1774866746168197120 | 1774866787490336768 |
| 393 | 1774866813629382656 | NULL                |
| 394 | 1774866813973315584 | NULL                |
+-----+---------------------+---------------------+

操作步骤

1. 操作 session1

INSERT INTO test(cid) SELECT 1774866813629382656 FROM (SELECT 1) vt LEFT JOIN test t on t.cid=1774866813629382656 WHERE t.cid is NULL;

-- 产生 id 395
INSERT INTO test(uid) SELECT '1774866837847150592' FROM (SELECT 1) vt LEFT JOIN test t on t.uid='1774866837847150592' WHERE t.uid is NULL;

2. 操作 session2

INSERT INTO test(cid) SELECT 1774866813973315584 FROM (SELECT 1) vt LEFT JOIN test t on t.cid=1774866813973315584 WHERE t.cid is NULL;

-- 产生 id 396
INSERT INTO test(uid) SELECT '1774866842418941952' FROM (SELECT 1) vt LEFT JOIN test t on t.uid='1774866842418941952' WHERE t.uid is NULL;

3. 操作 session1

delete from test where id = 395
update test set cid = 1774866813629382656, uid = '1774866837847150592' where id = 393

此时 session1 就 block 住了

查看锁发现

*************************** 1. row ***************************
    lock_id: 413652530:2348:5:165
lock_trx_id: 413652530
  lock_mode: S
  lock_type: RECORD
 lock_table: `extplugin`.`test`
 lock_index: udx_uid
 lock_space: 2348
  lock_page: 5
   lock_rec: 165
  lock_data: '1774866842418941952'
*************************** 2. row ***************************
    lock_id: 413652560:2348:5:165
lock_trx_id: 413652560
  lock_mode: X
  lock_type: RECORD
 lock_table: `extplugin`.`test`
 lock_index: udx_uid
 lock_space: 2348
  lock_page: 5
   lock_rec: 165
  lock_data: '1774866842418941952'

4. 操作 session2

delete from test where id = 396
update test set cid = 1774866813973315584, uid = '1774866842418941952' where id = 394

操作完告知 dead lock

死锁日志

想不通，这是咋么发生的，特别是第三步之后，S 与 X 锁加在了同一条记录上。。。

2024-05-16 14:39:51 7fba89b79700
*** (1) TRANSACTION:
TRANSACTION 413655288, ACTIVE 106.044 sec updating or deleting
mysql tables in use 1, locked 1
LOCK WAIT 5 lock struct(s), heap size 1184, 5 row lock(s), undo log entries 3
LOCK BLOCKING MySQL thread id: 12294698 block 12294693
MySQL thread id 12294693, OS thread handle 0x7fba8b1ab700, query id 1146997122 192.168.31.209 dbwrite updating
update test set cid = 1774866813629382656, uid = '1774866837847150592' where id = 393
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 2348 page no 5 n bits 368 index udx_uid of table extplugin.test trx id 413655288 lock mode S waiting
Record lock, heap no 165 PHYSICAL RECORD: n_fields 2; compact format; info bits 32
0: len 19; hex 31373734383636383432343138393431393532; asc 1774866842418941952;;
1: len 8; hex 8000000000000191; asc ;;

*** (2) TRANSACTION:
TRANSACTION 413655343, ACTIVE 98.640 sec updating or deleting
mysql tables in use 1, locked 1
5 lock struct(s), heap size 1184, 6 row lock(s), undo log entries 3
MySQL thread id 12294698, OS thread handle 0x7fba89b79700, query id 1146998311 192.168.31.209 dbwrite updating
update test set cid = 1774866813973315584, uid = '1774866842418941952' where id = 394
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 2348 page no 5 n bits 368 index udx_uid of table extplugin.test trx id 413655343 lock_mode X locks rec but not gap
Record lock, heap no 165 PHYSICAL RECORD: n_fields 2; compact format; info bits 32
0: len 19; hex 31373734383636383432343138393431393532; asc 1774866842418941952;;
1: len 8; hex 8000000000000191; asc ;;

*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 2348 page no 5 n bits 368 index udx_uid of table extplugin.test trx id 413655343 lock_mode X locks gap before rec insert intention waiting
Record lock, heap no 165 PHYSICAL RECORD: n_fields 2; compact format; info bits 32
0: len 19; hex 31373734383636383432343138393431393532; asc 1774866842418941952;;
1: len 8; hex 8000000000000191; asc ;;

MySQL 8.20之后很多案例都无法复现了，希望这些文章一直维护下去，更新一下

使用docker-compose构建容器后，mysql启动异常。

1、insert WAITING FOR lock mode S
2、insert WAITING FOR lock mode S , HOLDS lock_mode X locks rec but not gap
级别：RR
冲突的索引是同一个唯一索引，但不是同一条记录

目测是事务一和事务二都先执行过一次insert，并且锁了不同的记录；两个事务的第二条语句又insert，插入的记录恰好是对方已锁住的记录，这时候触发唯一索引冲突，都想上共享锁

案例20里，有分析，里面的第7步“事务一给 date加锁，和事务二冲突，导致死锁 (非聚簇索引加锁)”，假设真的是这样，由于是二级非唯一索引，那么这是申请的应该是next-key锁（至少是gap锁），而不应该是MySQL死锁日志里提示的：“index rank24h_date_8afc2781 of table business.rank24h trx id 121318802 lock_mode X locks rec but not gap waiting”。
求解答，我有一些初步的思路，但是很难确认，希望可以和你通过邮箱或微信交流，等有了交流成果后再更新到本git项目上。

CREATE TABLE adword_1 (
id bigint(20) NOT NULL AUTO_INCREMENT COMMENT '主键编号',
bu_id int(11) NOT NULL COMMENT '站点',
dimension_type smallint(6) NOT NULL COMMENT '维度类型',
dimension_value varchar(100) NOT NULL COMMENT '维度值',
ad_type smallint(6) NOT NULL COMMENT '类型',
ref_id varchar(200) DEFAULT NULL COMMENT '关联业务',
creator varchar(100) DEFAULT NULL COMMENT '操作人',
created datetime NOT NULL COMMENT '创建时间',
updated datetime NOT NULL COMMENT '修改时间',
yn smallint(6) NOT NULL DEFAULT '1' COMMENT '是否有效:0=无效,1=有效',
PRIMARY KEY (id),
KEY inxex_bu_id_dimension_value (bu_id,dimension_value) USING BTREE,
KEY inxex_updated (updated) USING BTREE,
KEY index_buid_dv_dt_at_yn (bu_id,dimension_value,dimension_type,ad_type,yn)
) ENGINE=InnoDB AUTO_INCREMENT=7371662 DEFAULT CHARSET=utf8mb4 ROW_FORMAT=COMPRESSED KEY_BLOCK_SIZE=8 COMMENT='';

LATEST DETECTED DEADLOCK

2022-06-17 08:44:01 0x7fa14eb9f700
*** (1) TRANSACTION:
TRANSACTION 7812220241, ACTIVE 0 sec starting index read
mysql tables in use 1, locked 1
LOCK WAIT 2 lock struct(s), heap size 1136, 1 row lock(s)
MySQL thread id 6626379, OS thread handle 140355400283904, query id 9145445661 11.43.237.206 adword_28_rw Searching rows for update
UPDATE adword_244
SET yn = 0,

        updated = now()
     

    where
    yn = 1
    AND dimension_value = '10051846311262'
    AND bu_id = 301
     
        AND dimension_type = 1
     
     
     
        AND ref_id = 'sg:289138'
     
     
        AND ad_type = 2

*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 274 page no 98798 n bits 304 index index_buid_dv_dt_at_yn of table adword_28.adword_244 trx id 7812220241 lock_mode X waiting
Record lock, heap no 229 PHYSICAL RECORD: n_fields 6; compact format; info bits 0
0: len 4; hex 8000012d; asc -;;
1: len 14; hex 3130303531383436333131323632; asc 10051846311262;;
2: len 2; hex 8001; asc ;;
3: len 2; hex 8002; asc ;;
4: len 2; hex 8001; asc ;;
5: len 8; hex 80000000006eeebd; asc n ;;

*** (2) TRANSACTION:
TRANSACTION 7812220240, ACTIVE 0 sec updating or deleting, thread declared inside InnoDB 4996
mysql tables in use 1, locked 1
6 lock struct(s), heap size 1136, 6 row lock(s), undo log entries 1
MySQL thread id 6627449, OS thread handle 140330787272448, query id 9145445660 11.40.39.203 adword_28_rw updating
UPDATE adword_244
SET yn = 0,

        updated = now()
     

    where
    yn = 1
    AND dimension_value = '10051846311262'
    AND bu_id = 301
     
        AND dimension_type = 1
     
     
     
        AND ref_id = 'sg:289138'
     
     
        AND ad_type = 2

*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 274 page no 98798 n bits 304 index index_buid_dv_dt_at_yn of table adword_28.adword_244 trx id 7812220240 lock_mode X
Record lock, heap no 229 PHYSICAL RECORD: n_fields 6; compact format; info bits 0
0: len 4; hex 8000012d; asc -;;
1: len 14; hex 3130303531383436333131323632; asc 10051846311262;;
2: len 2; hex 8001; asc ;;
3: len 2; hex 8002; asc ;;
4: len 2; hex 8001; asc ;;
5: len 8; hex 80000000006eeebd; asc n ;;

Record lock, heap no 232 PHYSICAL RECORD: n_fields 6; compact format; info bits 32
0: len 4; hex 8000012d; asc -;;
1: len 14; hex 3130303531383436333131323632; asc 10051846311262;;
2: len 2; hex 8001; asc ;;
3: len 2; hex 8002; asc ;;
4: len 2; hex 8001; asc ;;
5: len 8; hex 8000000000704355; asc pCU;;

*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 274 page no 98798 n bits 304 index index_buid_dv_dt_at_yn of table adword_28.adword_244 trx id 7812220240 lock_mode X locks gap before rec insert intention waiting
Record lock, heap no 229 PHYSICAL RECORD: n_fields 6; compact format; info bits 0
0: len 4; hex 8000012d; asc -;;
1: len 14; hex 3130303531383436333131323632; asc 10051846311262;;
2: len 2; hex 8001; asc ;;
3: len 2; hex 8002; asc ;;
4: len 2; hex 8001; asc ;;
5: len 8; hex 80000000006eeebd; asc n ;;

*** WE ROLL BACK TRANSACTION (1)

你好，我遇到4和18的情况，事务隔离级别已经设置为read committed，但是无法完全避免此类情况，目前只能通过分布式锁保证更新不在同一条id上进行，有办法通过调整数据库操作避免此种情况吗？

------------------------
LATEST DETECTED DEADLOCK
------------------------
2019-11-17 20:35:45 0x2b9fc4081700
*** (1) TRANSACTION:
TRANSACTION 128001656, ACTIVE 0 sec starting index read
mysql tables in use 1, locked 1
LOCK WAIT 2 lock struct(s), heap size 1136, 1 row lock(s)
MySQL thread id 2903, OS thread handle 47972839069440, query id 13418085 192.168.0.11 test updating
DELETE FROM test_table WHERE id = 'test'
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 3800 page no 5 n bits 448 index PRIMARY of table `test_table` trx id 128001656 lock_mode X locks rec but not gap waiting
Record lock, heap no 319 PHYSICAL RECORD: n_fields 5; compact format; info bits 32
 0: len 7; hex 475a3033363135; asc test;;
 1: len 6; hex 000007a12677; asc     &w;;
 2: len 7; hex 33000002e42549; asc 3    %I;;
 3: len 1; hex 83; asc  ;;
 4: len 8; hex 8000000000000001; asc         ;;

*** (2) TRANSACTION:
TRANSACTION 128001655, ACTIVE 0 sec inserting
mysql tables in use 1, locked 1
3 lock struct(s), heap size 1136, 2 row lock(s), undo log entries 1
MySQL thread id 2898, OS thread handle 47965188658944, query id 13418099 192.168.0.11 test update
INSERT INTO test_table (id, state) VALUES ('test', 3)
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 3800 page no 5 n bits 448 index PRIMARY of table `test_table` trx id 128001655 lock_mode X locks rec but not gap
Record lock, heap no 319 PHYSICAL RECORD: n_fields 5; compact format; info bits 32
 0: len 7; hex 475a3033363135; asc test;;
 1: len 6; hex 000007a12677; asc     &w;;
 2: len 7; hex 33000002e42549; asc 3    %I;;
 3: len 1; hex 83; asc  ;;
 4: len 8; hex 8000000000000001; asc         ;;

*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 3800 page no 5 n bits 448 index PRIMARY of table `test_table` trx id 128001655 lock mode S waiting
Record lock, heap no 319 PHYSICAL RECORD: n_fields 5; compact format; info bits 32
 0: len 7; hex 475a3033363135; asc test;;
 1: len 6; hex 000007a12677; asc     &w;;
 2: len 7; hex 33000002e42549; asc 3    %I;;
 3: len 1; hex 83; asc  ;;
 4: len 8; hex 8000000000000001; asc         ;;

*** WE ROLL BACK TRANSACTION (1)

表格中案例 4 描述的是delete delete

案例里实际上是delete insert
https://github.com/aneasystone/mysql-deadlocks/blob/master/4.md

老师您好，打扰你了 之前自己业务场景发生了如下的死锁问题 但是我看了您的文章之后还是有点解释不通我的case，可能自己理解好有点问题。
RR模式下
CREATE TABLE t (
id int(11) NOT NULL,
c int(11) DEFAULT NULL,
d int(11) DEFAULT NULL,
PRIMARY KEY (id),
KEY c (c)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

insert into t values(0,0,0),(5,5,5),(10,10,10),(15,15,15),(20,20,20),(25,25,25);

执行顺序
session 1
insert t values (9,9,9)[获取IX]
session 2
update t set d=9 where c=9 [给session1 上X锁，同时自己需要获取next-key,间隙锁获取成功，但是X锁获取失败进入锁等待]
session 1
update t set d=9 where c=9[需要获取next-key,间隙锁之间不冲突，间隙锁获取成功，但是X锁获取失败进入锁等待]
报发生死锁 session2回滚
疑问
明明session已经有X锁了 为什么session1的 update还会等待X锁造成死锁

死锁日志中也显示 session1 已经拥有X锁(lock_mode x locks but not gap)
但是等待next-key(lock_mode X) 间隙锁+X锁
这点很奇怪 因为自己的理解中间隙锁之间是不冲突的 间隙锁和插入意向锁是冲突的，此时session1已经拥有了 X锁 然后间隙锁也能获得 为什么死锁日志显示获取不到next-key

(1) TRANSACTION:
TRANSACTION 7826110, ACTIVE 2 sec inserting
mysql tables in use 1, locked 1
LOCK WAIT 10 lock struct(s), heap size 1136, 15 row lock(s), undo log entries 10
MySQL thread id 17765371, OS thread handle 139825729169152, query id 199063024 172.16.10.166 gdcuser update
/* insert TestDO */ insert into tbl_test (create_time, global_name, update_time, corp_id, count, editable, expire_date, order_id, resource_id, sp_id, status, type, type_id, id) values (1575826470134, 'global', 1575826470134, '9434', 10000, 1, 0, '39f838f61783474492311f009aaba483', 'c342774899bf4ddfbd5ab68f1aab322f', '2c908a266ee67a45016ee693b1744424', 0, 'B', '', '2c908a266ee67a45016ee693b50b44d0')
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 23657 page no 4 n bits 248 index uk_tbl_test_index of table table.tbl_test trx id 7826110 lock mode S waiting
Record lock, heap no 81 PHYSICAL RECORD: n_fields 4; compact format; info bits 0
0: len 30; hex 633564393537313638616430343666626163623662653538613865646435; asc c5d957168ad046fbacb6be58a8edd5; (total 32 bytes);
1: len 3; hex 564d52; asc C;;
2: len 5; hex 3233323838; asc 23288;;
3: len 30; hex 326339303861323636656536376134353031366565363933616234363432; asc 2c908a266ee67a45016ee693ab4642; (total 32 bytes);

*** (2) TRANSACTION:
TRANSACTION 7825164, ACTIVE 5 sec inserting
mysql tables in use 1, locked 1
13 lock struct(s), heap size 1136, 3 row lock(s), undo log entries 1024
MySQL thread id 17765414, OS thread handle 139827979204352, query id 199069471 172.16.10.166 gdcuser update
/* insert TestDO */ insert into tbl_test (create_time, global_name, update_time, corp_id, count, editable, expire_date, order_id, resource_id, sp_id, status, type, type_id, id) values (1575826467635, 'global', 1575826467635, '23288', 500, 1, 20191020, '31ddb90006f141dab012e02b09763eff', '29afcf31f27a4735a8f38dcdb66cb3fa', '2c908a266ee67a45016ee6931a643059', 0, 'A', '', '2c908a266ee67a45016ee693bc81464a')
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 23657 page no 4 n bits 248 index uk_tbl_test_index of table table.tbl_test trx id 7825164 lock_mode X locks rec but not gap
Record lock, heap no 81 PHYSICAL RECORD: n_fields 4; compact format; info bits 0
0: len 30; hex 633564393537313638616430343666626163623662653538613865646435; asc c5d957168ad046fbacb6be58a8edd5; (total 32 bytes);
1: len 3; hex 564d52; asc C;;
2: len 5; hex 3233323838; asc 23288;;
3: len 30; hex 326339303861323636656536376134353031366565363933616234363432; asc 2c908a266ee67a45016ee693ab4642; (total 32 bytes);

*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 23657 page no 4 n bits 248 index uk_tbl_test_index of table table.tbl_test trx id 7825164 lock_mode X locks gap before rec insert intention waiting
Record lock, heap no 157 PHYSICAL RECORD: n_fields 4; compact format; info bits 32
0: len 30; hex 326136343165353562626465346265626230623230313833323466633439; asc 2a641e55bbde4bebb0b2018324fc49; (total 32 bytes);
1: len 9; hex 434f4e465f43414c4c; asc D;;
2: len 4; hex 39343334; asc 9434;;
3: len 30; hex 326339303861323636656536376134353031366565363933623331383434; asc 2c908a266ee67a45016ee693b31844; (total 32 bytes);

*** WE ROLL BACK TRANSACTION (1)

唯一索引为 resource_id、type、corp_id的组合索引，事务1中和事务2中插入记录并无冲突，请教下为何还会死锁?

insert insert，
事务1等待锁：lock mode S waiting Record lock
事务2等待锁：lock_mode X waiting Record lock
事务2持有锁：lock_mode X locks rec but not gap Record lock

复现场景：
CREATE TABLE test123 (
id int(11) NOT NULL AUTO_INCREMENT,
a int(11) DEFAULT NULL,
b int(11) DEFAULT NULL,
c int(11) DEFAULT NULL,
PRIMARY KEY (id),
UNIQUE KEY uk (a,b)
) ENGINE=InnoDB AUTO_INCREMENT=0

事务0： insert into test123(a, b, c) values(0,1,171);
事务1： insert into test123(a, b, c) values(0,1,172);
事务2: insert into test123(a, b, c) values(0,1,173);
事务0: update test123 set a = 171 where c = 171;
事务0: commit;
事务1、2 死锁。

死锁日志：
'InnoDB', '', '

2021-03-30 14:56:09 700001b4a000 INNODB MONITOR OUTPUT

Per second averages calculated from the last 16 seconds

BACKGROUND THREAD

srv_master_thread loops: 46 srv_active, 0 srv_shutdown, 1094569 srv_idle
srv_master_thread log flush and writes: 1094545

SEMAPHORES

OS WAIT ARRAY INFO: reservation count 102
OS WAIT ARRAY INFO: signal count 101
Mutex spin waits 4356, rounds 4124, OS waits 4
RW-shared spins 90, rounds 2730, OS waits 91
RW-excl spins 3, rounds 159, OS waits 2
Spin rounds per wait: 0.95 mutex, 30.33 RW-shared, 53.00 RW-excl

LATEST DETECTED DEADLOCK

2021-03-30 14:55:39 700001b8e000
*** (1) TRANSACTION:
TRANSACTION 245654, ACTIVE 26 sec inserting
mysql tables in use 1, locked 1
LOCK WAIT 3 lock struct(s), heap size 360, 2 row lock(s), undo log entries 1
MySQL thread id 7, OS thread handle 0x700001bd2000, query id 446 localhost admin update
insert into test123(a, b, c) values(0,1,173)
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 38 page no 4 n bits 80 index uk of table test.test123 trx id 245654 lock mode S waiting
Record lock, heap no 8 PHYSICAL RECORD: n_fields 3; compact format; info bits 32
0: len 4; hex 80000000; asc ;;
1: len 4; hex 80000001; asc ;;
2: len 4; hex 8000001e; asc ;;

*** (2) TRANSACTION:
TRANSACTION 245648, ACTIVE 54 sec fetching rows
mysql tables in use 1, locked 1
7 lock struct(s), heap size 1184, 12 row lock(s), undo log entries 2
MySQL thread id 3, OS thread handle 0x700001b8e000, query id 447 localhost admin updating
update test123 set a = 171 where c = 171
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 38 page no 4 n bits 80 index uk of table test.test123 trx id 245648 lock_mode X locks rec but not gap
Record lock, heap no 8 PHYSICAL RECORD: n_fields 3; compact format; info bits 32
0: len 4; hex 80000000; asc ;;
1: len 4; hex 80000001; asc ;;
2: len 4; hex 8000001e; asc ;;

*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 38 page no 3 n bits 80 index PRIMARY of table test.test123 trx id 245648 lock_mode X waiting
Record lock, heap no 11 PHYSICAL RECORD: n_fields 6; compact format; info bits 0
0: len 4; hex 80000020; asc ;;
1: len 6; hex 00000003bf96; asc ;;
2: len 7; hex e0000001b80110; asc ;;
3: len 4; hex 80000000; asc ;;
4: len 4; hex 80000001; asc ;;
5: len 4; hex 800000ad; asc ;;

*** WE ROLL BACK TRANSACTION (1)

TRANSACTIONS

Trx id counter 245656
Purge done for trx's n:o < 245656 undo n:o < 0 state: running but idle
History list length 228
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 245653, not started
MySQL thread id 6, OS thread handle 0x700001c16000, query id 443 localhost admin
---TRANSACTION 245654, not started
MySQL thread id 7, OS thread handle 0x700001bd2000, query id 446 localhost admin
---TRANSACTION 245648, not started
MySQL thread id 3, OS thread handle 0x700001b8e000, query id 448 localhost admin
---TRANSACTION 0, not started
MySQL thread id 1, OS thread handle 0x700001b06000, query id 456 localhost 127.0.0.1 root
---TRANSACTION 245647, not started
MySQL thread id 2, OS thread handle 0x700001b4a000, query id 458 localhost 127.0.0.1 root init
show engine innodb status

FILE I/O

I/O thread 0 state: waiting for i/o request (insert buffer thread)
I/O thread 1 state: waiting for i/o request (log thread)
I/O thread 2 state: waiting for i/o request (read thread)
I/O thread 3 state: waiting for i/o request (read thread)
I/O thread 4 state: waiting for i/o request (read thread)
I/O thread 5 state: waiting for i/o request (read thread)
I/O thread 6 state: waiting for i/o request (write thread)
I/O thread 7 state: waiting for i/o request (write thread)
I/O thread 8 state: waiting for i/o request (write thread)
I/O thread 9 state: waiting for i/o request (write thread)
Pending normal aio reads: 0 [0, 0, 0, 0] , aio writes: 0 [0, 0, 0, 0] ,
ibuf aio reads: 0, log i/o's: 0, sync i/o's: 0
Pending flushes (fsync) log: 0; buffer pool: 0
408 OS file reads, 534 OS file writes, 450 OS fsyncs
0.00 reads/s, 0 avg bytes/read, 0.00 writes/s, 0.00 fsyncs/s

INSERT BUFFER AND ADAPTIVE HASH INDEX

Ibuf: size 1, free list len 0, seg size 2, 0 merges
merged operations:
insert 0, delete mark 0, delete 0
discarded operations:
insert 0, delete mark 0, delete 0
Hash table size 276671, node heap has 1 buffer(s)
0.00 hash searches/s, 0.00 non-hash searches/s

LOG

Log sequence number 42684143
Log flushed up to 42684143
Pages flushed up to 42684143
Last checkpoint at 42684143
0 pending log writes, 0 pending chkp writes
208 log i/o's done, 0.00 log i/o's/second

BUFFER POOL AND MEMORY

Total memory allocated 137363456; in additional pool allocated 0
Dictionary memory allocated 92081
Buffer pool size 8191
Free buffers 7784
Database pages 406
Old database pages 0
Modified db pages 0
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 0, not young 0
0.00 youngs/s, 0.00 non-youngs/s
Pages read 391, created 15, written 340
0.00 reads/s, 0.00 creates/s, 0.00 writes/s
No buffer pool page gets since the last printout
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 406, unzip_LRU len: 0
I/O sum[0]:cur[0], unzip sum[0]:cur[0]

ROW OPERATIONS

0 queries inside InnoDB, 0 queries in queue
0 read views open inside InnoDB
Main thread id 123145325797376, state: sleeping
Number of rows inserted 14, updated 11, deleted 4, read 75
0.00 inserts/s, 0.00 updates/s, 0.00 deletes/s, 0.00 reads/s

END OF INNODB MONITOR OUTPUT

'

之前线上有过一样的故障异常，后来排查是for循环事务导致的，故障现场精简后如下：

原业务逻辑是这样的：业务端会发送消息，该消息是个 list，里面存放 userId 和 money。消费端在接收的地方加了 @transaction 注解。在高峰情况下，会存在以上的场景，即某两个 userId 存在两个不同的消息中，并一起消费，互相死锁导致的异常。异常日志和 20.md 描述一致。

想请教个问题，死锁代码是否有一些共同特征，能否通过静态代码检查发现这些问题，或者给出提示信息

"事务二再插入 a=9 这条记录，需要获取插入意向锁（lock_mode X locks gap before rec insert intention）和事务一持有的 Next-key 锁冲突"，这个时候事务一不是在等待Next-key 锁吗，为什么事务二会和还没有持有的锁冲突

即使是事务2和事务3进行一次当前读，但是在RC级别下应该是没有GAP锁的，所以就解释不通了

业务表有自增id主键和组合唯一键

insert into on duplicate key update写法
delete from where

操作两个账号的数据时，insert语句可能时二阶段加锁了，会出现了这个死锁特征：
delete WAITING FOR lock_mode X
insert WAITING FOR lock_mode X locks gap before rec insert intention, HOLDS lock_mode X locks rec but not gap

请问您有对insert into on duplicate key update加锁解释的文章吗

rt，MySQL5.7，隔离级别：RR，实验结果并不会发生死锁

场景11无法复现
MySQL版本5.7.18

关于案例 14 的重现步骤，为啥两个 session 都先执行一个 delete 语句呢？

如果有 delete 的话，session1 会对在uniq_kid_aid_biz_rid索引上对 kdt_id 区间 (10，20] 加锁，session2 删除 ktd_id= 18，也会对区间 (10，20] 加锁，所以会等待 session1 完成，不会出现死锁。

我遇到的类似的死锁日志完全是由 insert 语句造成的。

案例14 重现步骤

我在线上环境遇到另一种死锁，但是未在表格中找到，不知道你是否遇到过？

具体日志：

------------------------
LATEST DETECTED DEADLOCK
------------------------
190723 18:54:38
*** (1) TRANSACTION:
TRANSACTION E04E0FAF, ACTIVE 0 sec inserting
mysql tables in use 1, locked 1
LOCK WAIT 32 lock struct(s), heap size 6960, 431 row lock(s), undo log entries 82
MySQL thread id 364137278, OS thread handle 0x7fb4459f7700, query id 23683965704  operation update
INSERT INTO `account_user_groups` (`user_id`, `group_id`) VALUES (113, 704)
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 6655 page no 255 n bits 1000 index `account_user_groups_user_id_group_id_4d09af3e_uniq` of table `operation`.`account_user_groups` trx id E04E0FAF lock mode S waiting
Record lock, heap no 282 PHYSICAL RECORD: n_fields 3; compact format; info bits 32
 0: len 4; hex 80000071; asc    q;;
 1: len 4; hex 800002c8; asc     ;;
 2: len 4; hex 81878884; asc     ;;

*** (2) TRANSACTION:
TRANSACTION E04E0FC5, ACTIVE 0 sec updating or deleting, thread declared inside InnoDB 499
mysql tables in use 1, locked 1
42 lock struct(s), heap size 14776, 182 row lock(s), undo log entries 52
MySQL thread id 364137279, OS thread handle 0x7fb444b7e700, query id 23683965809 operation updating
DELETE FROM `account_user_groups` WHERE `account_user_groups`.`group_id` = 713
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 6655 page no 255 n bits 1000 index `account_user_groups_user_id_group_id_4d09af3e_uniq` of table `operation`.`account_user_groups` trx id E04E0FC5 lock_mode X locks rec but not gap
Record lock, heap no 280 PHYSICAL RECORD: n_fields 3; compact format; info bits 32
 0: len 4; hex 8000004a; asc    J;;
 1: len 4; hex 800002c8; asc     ;;
 2: len 4; hex 8187888a; asc     ;;

Record lock, heap no 282 PHYSICAL RECORD: n_fields 3; compact format; info bits 32
 0: len 4; hex 80000071; asc    q;;
 1: len 4; hex 800002c8; asc     ;;
 2: len 4; hex 81878884; asc     ;;

Record lock, heap no 466 PHYSICAL RECORD: n_fields 3; compact format; info bits 32
 0: len 4; hex 80000073; asc    s;;
 1: len 4; hex 800002c8; asc     ;;
 2: len 4; hex 81878886; asc     ;;

Record lock, heap no 711 PHYSICAL RECORD: n_fields 3; compact format; info bits 32
 0: len 4; hex 80000072; asc    r;;
 1: len 4; hex 800002c8; asc     ;;
 2: len 4; hex 81878885; asc     ;;

*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 6655 page no 206 n bits 1000 index `account_user_groups_user_id_group_id_4d09af3e_uniq` of table `operation`.`account_user_groups` trx id E04E0FC5 lock_mode X locks rec but not gap waiting
Record lock, heap no 23 PHYSICAL RECORD: n_fields 3; compact format; info bits 0
 0: len 4; hex 80000042; asc    B;;
 1: len 4; hex 800002c9; asc     ;;
 2: len 4; hex 8187889e; asc     ;;

*** WE ROLL BACK TRANSACTION (2)
------------
TRANSACTIONS
------------

您好~场景5中事物二持有的不是记录锁吗？为什么会有s锁的解释呢？

https://zhuanlan.zhihu.com/p/59000837

README.md中的表格是否可以添加一列隔离级别？

我实际场景中 出现了案例2: 但情况有所不同 历史遗留的表结构

uid 主键非自增
cpid 普通索引

insert 的时候 会指定uid插入

1. cpid查询 是否存在. 不存在 insert
2. uid查询 是否存在 存在 update cpid
   不存在 insert

请问我这种是什么情况

case 19 是哪个版本的。无法复线 建表语句也有问题

1. rc级别:
2. 表结构:

CREATE TABLE `message_entity` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `message_id` bigint(20) unsigned NOT NULL,
  `chat_id` bigint(20) unsigned NOT NULL,
  `entity` mediumblob,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uniq_message_id` (`message_id`)
) ENGINE=InnoDB AUTO_INCREMENT=21 DEFAULT CHARSET=utf8mb4 ROW_FORMAT=DYNAMIC

3. 死锁日志：

*** (1) TRANSACTION:
TRANSACTION 2404187192, ACTIVE 0 sec inserting
mysql tables in use 1, locked 1
LOCK WAIT 8 lock struct(s), heap size 1136, 2 row lock(s)
MySQL thread id 118913019, OS thread handle 140411115681536, query id 8752700587 xx.xx.xx.x xxx__u update  INTO `message_entity` (`id`,`message_id`,`chat_id`,`entity`) VALUES (868,868,1,'')
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 289 page no 2697984 n bits 80 index PRIMARY of table `xxxx_shard_264`.`message_entity` trx id 2404187192 lock_mode X locks gap before rec insert intention waiting

*** (2) TRANSACTION:
TRANSACTION 2404186956, ACTIVE 0 sec inserting, thread declared inside InnoDB 1
mysql tables in use 1, locked 1
8 lock struct(s), heap size 1136, 3 row lock(s)
MySQL thread id 118913470, OS thread handle 140410161960704, query id 8752703155 xx.xx.xx.xx xxx__u update INTO `message_entity` (`id`,`message_id`,`chat_id`,`entity`) VALUES (988,988,2,'')
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 289 page no 2697984 n bits 80 index PRIMARY of table `xxxx_shard_264`.`message_entity` trx id 2404186956 lock mode S locks gap before rec
*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 289 page no 2697984 n bits 80 index PRIMARY of table `xxxx_shard_264`.`message_entity` trx id 2404186956 lock_mode X locks gap before rec insert intention waiting
*** WE ROLL BACK TRANSACTION (2)
------------
TRANSACTIONS
------------