出现了与案例 15 的结果，但是过程不一样

[NeoZephyr]

数据版本是

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 ;;