------------------------ LATEST DETECTED DEADLOCK ------------------------ 2023-12-28 01:00:02 0x7f6038048700 *** (1) TRANSACTION: TRANSACTION 107457050, ACTIVE 0 sec inserting mysql tables in use 1, locked 1 LOCK WAIT 4 lock struct(s), heap size 1136, 3 row lock(s), undo log entries 1 MySQL thread id 53549097, OS thread handle 140052197938944, query id 33728119548 x.x.x.x lop_services_rw update insert into t_ms_provider ( id, app_key, app_group, alias, create_time, update_time ) values ( null, ... 'lwprod-x_133', now(), now() ) *** (1) WAITING FOR THIS LOCK TO BE GRANTED: RECORD LOCKS space id 77 page no 4 n bits 248 index uniq_ws_conn_app_group_alias of table `lop_services`.`t_ms_provider` trx id 107457050 lock_mode X locks gap before rec insert intention waiting Record lock, heap no 4 PHYSICAL RECORD: n_fields 4; compact format; info bits 32 0: len 30; hex 303839346238393831363130346132666265376362373230633863323066; asc 0894b89816104a2fbe7cb720c8c20f; (total 32 bytes); 1: len 7; hex 64656661756c74; asc default;; 2: len 21; hex 6c7770726f642d31315f3136355f3134355f323438; asc lwprod-x_248;; 3: len 8; hex 0000000000488217; asc H ;; *** (2) TRANSACTION: TRANSACTION 107457048, ACTIVE 0 sec inserting mysql tables in use 1, locked 1 4 lock struct(s), heap size 1136, 4 row lock(s), undo log entries 1 MySQL thread id 53547339, OS thread handle 140051233408768, query id 33728119527 x.x.x.x lop_services_rw update insert into t_ms_provider ( id, app_key, app_group, alias, create_time, update_time ) values ( null, ... 'lwprod-x_133', now(), now() ) *** (2) HOLDS THE LOCK(S): RECORD LOCKS space id 77 page no 4 n bits 248 index uniq_ws_conn_app_group_alias of table `lop_services`.`t_ms_provider` trx id 107457048 lock mode S Record lock, heap no 3 PHYSICAL RECORD: n_fields 4; compact format; info bits 32 0: len 30; hex 303839346238393831363130346132666265376362373230633863323066; asc 0894b89816104a2fbe7cb720c8c20f; (total 32 bytes); 1: len 7; hex 64656661756c74; asc default;; 2: len 21; hex 6c7770726f642d31315f3135375f3232305f313333; asc lwprod-x_133;; 3: len 8; hex 0000000000488215; asc H ;; Record lock, heap no 4 PHYSICAL RECORD: n_fields 4; compact format; info bits 32 0: len 30; hex 303839346238393831363130346132666265376362373230633863323066; asc 0894b89816104a2fbe7cb720c8c20f; (total 32 bytes); 1: len 7; hex 64656661756c74; asc default;; 2: len 21; hex 6c7770726f642d31315f3136355f3134355f323438; asc lwprod-x_248;; 3: len 8; hex 0000000000488217; asc H ;; *** (2) WAITING FOR THIS LOCK TO BE GRANTED: RECORD LOCKS space id 77 page no 4 n bits 248 index uniq_ws_conn_app_group_alias of table `lop_services`.`t_ms_provider` trx id 107457048 lock_mode X locks gap before rec insert intention waiting Record lock, heap no 4 PHYSICAL RECORD: n_fields 4; compact format; info bits 32 0: len 30; hex 303839346238393831363130346132666265376362373230633863323066; asc 0894b89816104a2fbe7cb720c8c20f; (total 32 bytes); 1: len 7; hex 64656661756c74; asc default;; 2: len 21; hex 6c7770726f642d31315f3136355f3134355f323438; asc lwprod-x_248;; 3: len 8; hex 0000000000488217; asc H ;; *** WE ROLL BACK TRANSACTION (2)其中:
mysql> show create table `lop_services`.`t_ms_provider` \G *************************** 1. row *************************** Table: t_ms_provider Create Table: CREATE TABLE `t_ms_provider` ( `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT '主键', `app_key` varchar(40) NOT NULL COMMENT '应用', `app_group` varchar(16) NOT NULL COMMENT '分组', `alias` varchar(40) NOT NULL COMMENT '别名', `create_time` datetime NOT NULL COMMENT '创建时间', `update_time` datetime NOT NULL COMMENT '更新时间', `is_delete` tinyint(1) NOT NULL DEFAULT '0' COMMENT '刪除标志', PRIMARY KEY (`id`), UNIQUE KEY `uniq_ws_conn_app_group_alias` (`app_key`,`app_group`,`alias`) USING BTREE ) ENGINE=InnoDB AUTO_INCREMENT=4769114 DEFAULT CHARSET=utf8 COMMENT='连接关系' 1 row in set (0.00 sec) mysql> select * from `lop_services`.`t_ms_provider` where id=4751895 \G Empty set (0.00 sec) mysql> select * from `lop_services`.`t_ms_provider` where app_key='x' and alias='lwprod-x_248' \G Empty set (0.00 sec) mysql> select * from `lop_services`.`t_ms_provider` where app_key='x' and alias='lwprod-x_133' \G Empty set (0.00 sec)其中:
2.数据已删除,因此没有参考意义。
#231228 1:00:02 server id 1683071722 end_log_pos 6757 CRC32 0xe038610a GTID last_committed=12 sequence_number=13 rbr_only=yes /*!50718 SET TRANSACTION ISOLATION LEVEL READ COMMITTED*//*!*/; SET @@SESSION.GTID_NEXT= '0696f1f5-33a5-11eb-b03b-fa163edd964a:37080952'/*!*/; # at 6757 #231228 1:00:02 server id 1683071722 end_log_pos 6820 CRC32 0xdd6c18a3 Query thread_id=53549097 exec_time=0 error_code=0 SET TIMESTAMP=1703696402/*!*/; BEGIN /*!*/; # at 6820 #231228 1:00:02 server id 1683071722 end_log_pos 7178 CRC32 0xce724355 Rows_query # insert into t_ms_provider # ( id, # app_key, # app_group, # alias, # create_time, # update_time ) # values # ( null, ... # 'lwprod-x_133', # now(), # now() ) # at 7178 #231228 1:00:02 server id 1683071722 end_log_pos 7256 CRC32 0x4c1a11a7 Table_map: `lop_services`.`t_ms_provider` mapped to number 313391 # at 7256 #231228 1:00:02 server id 1683071722 end_log_pos 7374 CRC32 0x0196a119 Write_rows: table id 313391 flags: STMT_END_F ### INSERT INTO `lop_services`.`t_ms_provider` ### SET ### @1=4754161 /* LONGINT meta=0 nullable=0 is_null=0 */ ... ### @4='lwprod-x_133' /* VARSTRING(120) meta=120 nullable=0 is_null=0 */ ### @5='2023-12-28 01:00:02' /* DATETIME(0) meta=0 nullable=0 is_null=0 */ ### @6='2023-12-28 01:00:02' /* DATETIME(0) meta=0 nullable=0 is_null=0 */ ### @7=0 /* TINYINT meta=0 nullable=0 is_null=0 */ # at 7374 #231228 1:00:02 server id 1683071722 end_log_pos 7405 CRC32 0xa4ea95b6 Xid = 164809495 COMMIT/*!*/;其中:
delete,lwprod-x_133 # at 4404 #231228 1:00:01 server id 1683071722 end_log_pos 4469 CRC32 0xcaffa63d GTID last_committed=8 sequence_number=9 rbr_only=yes /*!50718 SET TRANSACTION ISOLATION LEVEL READ COMMITTED*//*!*/; SET @@SESSION.GTID_NEXT= '0696f1f5-33a5-11eb-b03b-fa163edd964a:37080948'/*!*/; # at 4469 #231228 1:00:01 server id 1683071722 end_log_pos 4532 CRC32 0xd4c899b6 Query thread_id=53549097 exec_time=0 error_code=0 SET TIMESTAMP=1703696401/*!*/; BEGIN /*!*/; # at 4532 #231228 1:00:01 server id 1683071722 end_log_pos 4608 CRC32 0x06d979e5 Rows_query # delete from t_ms_provider # where id = 4751893 # at 4608 #231228 1:00:01 server id 1683071722 end_log_pos 4686 CRC32 0xeb2610e0 Table_map: `lop_services`.`t_ms_provider` mapped to number 313391 # at 4686 #231228 1:00:01 server id 1683071722 end_log_pos 4804 CRC32 0x0d8e269a Delete_rows: table id 313391 flags: STMT_END_F ### DELETE FROM `lop_services`.`t_ms_provider` ### WHERE ### @1=4751893 /* LONGINT meta=0 nullable=0 is_null=0 */ ... ### @4='lwprod-x_133' /* VARSTRING(120) meta=120 nullable=0 is_null=0 */ ### @5='2023-12-27 01:00:02' /* DATETIME(0) meta=0 nullable=0 is_null=0 */ ### @6='2023-12-27 21:01:10' /* DATETIME(0) meta=0 nullable=0 is_null=0 */ ### @7=0 /* TINYINT meta=0 nullable=0 is_null=0 */ # at 4804 #231228 1:00:01 server id 1683071722 end_log_pos 4835 CRC32 0x895dcbdb Xid = 164809487 COMMIT/*!*/;整理 binlog 中记录的事务顺序:
delete,lwprod-x_133 delete,lwprod-x_248 insert,lwprod-x_248 insert,lwprod-x_133,commit insert,lwprod-x_133,rollback下面参考 binlog 中 5 个事务的顺序复现死锁。
mysql> show create table t_unique \G *************************** 1. row *************************** Table: t_unique Create Table: CREATE TABLE `t_unique` ( `id` int(11) NOT NULL AUTO_INCREMENT, `age` int(11) DEFAULT '0', `a` int(11) DEFAULT '0', PRIMARY KEY (`id`), UNIQUE KEY `uk_age` (`age`) ) ENGINE=InnoDB AUTO_INCREMENT=34 DEFAULT CHARSET=utf8 1 row in set (0.00 sec) mysql> select * from t_unique; +----+------+------+ | id | age | a | +----+------+------+ | 1 | 1 | 0 | | 5 | 5 | 0 | | 6 | 6 | 0 | | 9 | 9 | 0 | +----+------+------+ 4 rows in set (0.01 sec)其中:
mysql> set global innodb_purge_stop_now=1; Query OK, 0 rows affected (0.02 sec) mysql> show engine innodb status \G ... Trx id counter 34701 Purge done for trx's n:o < 34701 undo n:o < 0 state: stopped History list length 0 ...其中:
时刻 | session 1 | session 2 | session 3 | Session 4 |
---|---|---|---|---|
1 |
begin; delete from t_unique where age=5; commit; |
|
|
|
2 |
begin; delete from t_unique where age=6; commit; |
|
|
|
3 |
|
begin; insert into t_unique(age) values(6); |
|
|
4 |
|
|
begin; insert into t_unique(age) values(5); blocking |
begin; insert into t_unique(age) values(5); blocking |
5 |
|
commit; |
|
|
6 |
|
|
Deadlock found | 1 row affected (32.33 sec) |
------------------------ LATEST DETECTED DEADLOCK ------------------------ 2024-01-04 17:46:57 0x7fae4c161700 *** (1) TRANSACTION: TRANSACTION 34711, ACTIVE 33 sec inserting mysql tables in use 1, locked 1 LOCK WAIT 4 lock struct(s), heap size 1160, 3 row lock(s), undo log entries 1 MySQL thread id 49, OS thread handle 140386577819392, query id 522 127.0.0.1 admin update insert into t_unique(age) values(5) *** (1) WAITING FOR THIS LOCK TO BE GRANTED: RECORD LOCKS space id 26 page no 4 n bits 80 index uk_age of table `test_zk`.`t_unique` trx id 34711 lock_mode X locks gap before rec insert intention waiting Record lock, heap no 3 PHYSICAL RECORD: n_fields 2; compact format; info bits 32 0: len 4; hex 80000006; asc ;; 1: len 4; hex 80000006; asc ;; *** (2) TRANSACTION: TRANSACTION 34710, ACTIVE 37 sec inserting mysql tables in use 1, locked 1 4 lock struct(s), heap size 1160, 4 row lock(s), undo log entries 1 MySQL thread id 48, OS thread handle 140386577553152, query id 520 127.0.0.1 admin update insert into t_unique(age) values(5) *** (2) HOLDS THE LOCK(S): RECORD LOCKS space id 26 page no 4 n bits 80 index uk_age of table `test_zk`.`t_unique` trx id 34710 lock mode S Record lock, heap no 3 PHYSICAL RECORD: n_fields 2; compact format; info bits 32 0: len 4; hex 80000006; asc ;; 1: len 4; hex 80000006; asc ;; Record lock, heap no 6 PHYSICAL RECORD: n_fields 2; compact format; info bits 32 0: len 4; hex 80000005; asc ;; 1: len 4; hex 80000005; asc ;; *** (2) WAITING FOR THIS LOCK TO BE GRANTED: RECORD LOCKS space id 26 page no 4 n bits 80 index uk_age of table `test_zk`.`t_unique` trx id 34710 lock_mode X locks gap before rec insert intention waiting Record lock, heap no 3 PHYSICAL RECORD: n_fields 2; compact format; info bits 32 0: len 4; hex 80000006; asc ;; 1: len 4; hex 80000006; asc ;; *** WE ROLL BACK TRANSACTION (2)其中:
---TRANSACTION 421861590415552, not started 0 lock struct(s), heap size 1160, 0 row lock(s) ---TRANSACTION 34711, ACTIVE 4 sec inserting mysql tables in use 1, locked 1 LOCK WAIT 4 lock struct(s), heap size 1160, 3 row lock(s), undo log entries 1 MySQL thread id 49, OS thread handle 140386577819392, query id 522 127.0.0.1 admin update insert into t_unique(age) values(5) ------- TRX HAS BEEN WAITING 4 SEC FOR THIS LOCK TO BE GRANTED: RECORD LOCKS space id 26 page no 4 n bits 80 index uk_age of table `test_zk`.`t_unique` trx id 34711 lock_mode X locks gap before rec insert intention waiting Record lock, heap no 3 PHYSICAL RECORD: n_fields 2; compact format; info bits 32 0: len 4; hex 80000006; asc ;; 1: len 4; hex 80000006; asc ;; ------------------ TABLE LOCK table `test_zk`.`t_unique` trx id 34711 lock mode IX RECORD LOCKS space id 26 page no 4 n bits 80 index uk_age of table `test_zk`.`t_unique` trx id 34711 lock mode S Record lock, heap no 6 PHYSICAL RECORD: n_fields 2; compact format; info bits 32 0: len 4; hex 80000005; asc ;; 1: len 4; hex 80000005; asc ;; RECORD LOCKS space id 26 page no 4 n bits 80 index uk_age of table `test_zk`.`t_unique` trx id 34711 lock mode S Record lock, heap no 3 PHYSICAL RECORD: n_fields 2; compact format; info bits 32 0: len 4; hex 80000006; asc ;; 1: len 4; hex 80000006; asc ;; RECORD LOCKS space id 26 page no 4 n bits 80 index uk_age of table `test_zk`.`t_unique` trx id 34711 lock_mode X locks gap before rec insert intention waiting Record lock, heap no 3 PHYSICAL RECORD: n_fields 2; compact format; info bits 32 0: len 4; hex 80000006; asc ;; 1: len 4; hex 80000006; asc ;; ---TRANSACTION 34710, ACTIVE 8 sec inserting mysql tables in use 1, locked 1 LOCK WAIT 3 lock struct(s), heap size 1160, 3 row lock(s), undo log entries 1 MySQL thread id 48, OS thread handle 140386577553152, query id 520 127.0.0.1 admin update insert into t_unique(age) values(5) ------- TRX HAS BEEN WAITING 8 SEC FOR THIS LOCK TO BE GRANTED: RECORD LOCKS space id 26 page no 4 n bits 80 index uk_age of table `test_zk`.`t_unique` trx id 34710 lock_mode X locks gap before rec insert intention waiting Record lock, heap no 3 PHYSICAL RECORD: n_fields 2; compact format; info bits 32 0: len 4; hex 80000006; asc ;; 1: len 4; hex 80000006; asc ;; ------------------ TABLE LOCK table `test_zk`.`t_unique` trx id 34710 lock mode IX RECORD LOCKS space id 26 page no 4 n bits 80 index uk_age of table `test_zk`.`t_unique` trx id 34710 lock mode S Record lock, heap no 3 PHYSICAL RECORD: n_fields 2; compact format; info bits 32 0: len 4; hex 80000006; asc ;; 1: len 4; hex 80000006; asc ;; Record lock, heap no 6 PHYSICAL RECORD: n_fields 2; compact format; info bits 32 0: len 4; hex 80000005; asc ;; 1: len 4; hex 80000005; asc ;; RECORD LOCKS space id 26 page no 4 n bits 80 index uk_age of table `test_zk`.`t_unique` trx id 34710 lock_mode X locks gap before rec insert intention waiting Record lock, heap no 3 PHYSICAL RECORD: n_fields 2; compact format; info bits 32 0: len 4; hex 80000006; asc ;; 1: len 4; hex 80000006; asc ;; ---TRANSACTION 34709, ACTIVE 15 sec 3 lock struct(s), heap size 1160, 3 row lock(s), undo log entries 1 MySQL thread id 47, OS thread handle 140386578351872, query id 523 127.0.0.1 admin starting show engine innodb status TABLE LOCK table `test_zk`.`t_unique` trx id 34709 lock mode IX RECORD LOCKS space id 26 page no 4 n bits 80 index uk_age of table `test_zk`.`t_unique` trx id 34709 lock mode S Record lock, heap no 3 PHYSICAL RECORD: n_fields 2; compact format; info bits 32 0: len 4; hex 80000006; asc ;; 1: len 4; hex 80000006; asc ;; Record lock, heap no 4 PHYSICAL RECORD: n_fields 2; compact format; info bits 0 0: len 4; hex 80000009; asc ;; 1: len 4; hex 80000009; asc ;; RECORD LOCKS space id 26 page no 4 n bits 80 index uk_age of table `test_zk`.`t_unique` trx id 34709 lock mode S locks gap before rec Record lock, heap no 5 PHYSICAL RECORD: n_fields 2; compact format; info bits 0 0: len 4; hex 80000006; asc ;; 1: len 4; hex 8000001f; asc ;;其中:
---TRANSACTION 34711, ACTIVE 41 sec 5 lock struct(s), heap size 1160, 4 row lock(s), undo log entries 1 MySQL thread id 49, OS thread handle 140386577819392, query id 522 127.0.0.1 admin TABLE LOCK table `test_zk`.`t_unique` trx id 34711 lock mode IX RECORD LOCKS space id 26 page no 4 n bits 80 index uk_age of table `test_zk`.`t_unique` trx id 34711 lock mode S Record lock, heap no 6 PHYSICAL RECORD: n_fields 2; compact format; info bits 32 0: len 4; hex 80000005; asc ;; 1: len 4; hex 80000005; asc ;; RECORD LOCKS space id 26 page no 4 n bits 80 index uk_age of table `test_zk`.`t_unique` trx id 34711 lock mode S Record lock, heap no 3 PHYSICAL RECORD: n_fields 2; compact format; info bits 32 0: len 4; hex 80000006; asc ;; 1: len 4; hex 80000006; asc ;; RECORD LOCKS space id 26 page no 4 n bits 80 index uk_age of table `test_zk`.`t_unique` trx id 34711 lock_mode X locks gap before rec insert intention Record lock, heap no 3 PHYSICAL RECORD: n_fields 2; compact format; info bits 32 0: len 4; hex 80000006; asc ;; 1: len 4; hex 80000006; asc ;; RECORD LOCKS space id 26 page no 4 n bits 80 index uk_age of table `test_zk`.`t_unique` trx id 34711 lock mode S locks gap before rec Record lock, heap no 7 PHYSICAL RECORD: n_fields 2; compact format; info bits 0 0: len 4; hex 80000005; asc ;; 1: len 4; hex 80000021; asc !;;其中:
|
X | S |
---|---|---|
X | - | - |
S | - | + |
session 1 session 2 begin; delete from t_unique where age=6; begin; delete from t_unique where age=5; insert into t_unique(age) values(6); insert into t_unique(age) values(5); blocking其中:
RECORD LOCKS space id 502 page no 4 n bits 72 index uk_age of table `test_zk`.`t_unique` trx id 133927 lock mode S Record lock, heap no 3 PHYSICAL RECORD: n_fields 2; compact format; info bits 32 0: len 4; hex 80000005; asc ;; 1: len 4; hex 80000005; asc ;; RECORD LOCKS space id 502 page no 4 n bits 72 index uk_age of table `test_zk`.`t_unique` trx id 133927 lock mode S waiting Record lock, heap no 5 PHYSICAL RECORD: n_fields 2; compact format; info bits 32 0: len 4; hex 80000006; asc ;; 1: len 4; hex 80000006; asc ;;其中:
allow_duplicates = thr_get_trx(thr)->duplicates; /* Scan index records and check if there is a duplicate */ do { // 加锁 if (allow_duplicates) { // insert on duplicate 检测到二级唯一键冲突时加 X 型 next-key lock err = row_ins_set_exclusive_rec_lock( lock_type, block, rec, index, offsets, thr); } else { // insert 检测到二级唯一键冲突时加 S 型 next-key lock err = row_ins_set_shared_rec_lock( lock_type, block, rec, index, offsets, thr); } // 判断唯一键 key 是否重复,注意是先加锁后比较,cmp=0 表示重复 cmp = cmp_dtuple_rec(entry, rec, offsets); // 唯一键重复,并且不允许重复 if (cmp == 0 && !index->allow_duplicates) { // 判断冲突行是否已经删除,如果已经删除,则不冲突,返回 false, 如果不是删除的行,则冲突,返回 true if (row_ins_dupl_error_with_rec(rec, entry, index, offsets)) { // 报错唯一键冲突 err = DB_DUPLICATE_KEY; goto end_scan; } } else { // ut_a 断言,表达式不为真时退出 // 如果不重复或者重复但是允许重复时,执行下一行退出循环 // cmp < 0 表示 entry < rec,其中 rec 是读取到的记录,entry 是要插入的记录,表明读取到了更大的记录 ut_a(cmp < 0 || index->allow_duplicates); goto end_scan; } // 扫描下一个记录,直到遇到第一个不同的记录 } while (btr_pcur_move_to_next(&pcur, mtr));其中:
---TRANSACTION 34711, ACTIVE 41 sec 5 lock struct(s), heap size 1160, 4 row lock(s), undo log entries 1 MySQL thread id 49, OS thread handle 140386577819392, query id 522 127.0.0.1 admin TABLE LOCK table `test_zk`.`t_unique` trx id 34711 lock mode IX RECORD LOCKS space id 26 page no 4 n bits 80 index uk_age of table `test_zk`.`t_unique` trx id 34711 lock mode S Record lock, heap no 6 PHYSICAL RECORD: n_fields 2; compact format; info bits 32 0: len 4; hex 80000005; asc ;; 1: len 4; hex 80000005; asc ;; RECORD LOCKS space id 26 page no 4 n bits 80 index uk_age of table `test_zk`.`t_unique` trx id 34711 lock mode S Record lock, heap no 3 PHYSICAL RECORD: n_fields 2; compact format; info bits 32 0: len 4; hex 80000006; asc ;; 1: len 4; hex 80000006; asc ;;其中:
session 2 | session 3 | Session 4 |
---|---|---|
begin; insert into t_unique(age) values(6); |
|
|
|
begin; insert into t_unique(age) values(5); blocking |
begin; insert into t_unique(age) values(5); blocking |
commit; |
|
|
|
Deadlock found | 1 row affected (32.33 sec) |
。
时刻 | session 1 | session 2 | session 3 | Session 4 | 分析 |
---|---|---|---|---|---|
1 | delete 5; |
|
|
|
delete-marked record 5 |
2 | delete 6; |
|
|
|
delete-marked record 6 |
3 |
|
begin; insert 6; |
|
|
持有 6、9 的 S next-key lock |
4 |
|
|
insert 5; blocking |
insert 5; blocking |
持有 5、6 的 S next-key lock 等待 6 的 X locks gap before rec |
5 |
|
commit; |
|
|
释放 6 的 X locks gap before rec |
6 |
|
|
Deadlock found | 1 row affected | 竞争 6 的 X locks gap before rec |
该案例中 delete + insert 不在同一个事务中的原因与业务场景有关,因此无法将 delete + insert 放在同一个事务中。