Description:
2021-04-27T10:47:36.764116+08:00 84 [Note] InnoDB: Transactions deadlock detected, dumping detailed information.
2021-04-27T10:47:36.764212+08:00 84 [Note] InnoDB:
*** (1) TRANSACTION:
TRANSACTION 29507, ACTIVE 69 sec fetching rows
mysql tables in use 1, locked 1
LOCK WAIT 9 lock struct(s), heap size 1136, 5 row lock(s), undo log entries 1
MySQL thread id 83, OS thread handle 140265850660608, query id 2879131 localhost root updating
update t1 set c=12 where a=3
2021-04-27T10:47:36.764258+08:00 84 [Note] InnoDB: *** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 1323 page no 4 n bits 72 index uk_a of table `test`.`t1` /* Partition `p1` */ trx id 29507 lock_mode X locks rec but not gap waiting
Record lock, heap no 4 PHYSICAL RECORD: n_fields 3; compact format; info bits 0
0: len 4; hex 80000005; asc ;;
1: len 4; hex 80000001; asc ;;
2: len 8; hex 000000000000000b; asc ;;
2021-04-27T10:47:36.764495+08:00 84 [Note] InnoDB: *** (2) TRANSACTION:
TRANSACTION 29508, ACTIVE 49 sec fetching rows, thread declared inside InnoDB 4996
mysql tables in use 1, locked 1
9 lock struct(s), heap size 1136, 5 row lock(s)
MySQL thread id 84, OS thread handle 140265850128128, query id 2879138 localhost root updating
update t1 set c=12 where a=4
2021-04-27T10:47:36.764526+08:00 84 [Note] InnoDB: *** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 1323 page no 4 n bits 72 index uk_a of table `test`.`t1` /* Partition `p1` */ trx id 29508 lock_mode X locks rec but not gap
Record lock, heap no 4 PHYSICAL RECORD: n_fields 3; compact format; info bits 0
0: len 4; hex 80000005; asc ;;
1: len 4; hex 80000001; asc ;;
2: len 8; hex 000000000000000b; asc ;;
2021-04-27T10:47:36.764638+08:00 84 [Note] InnoDB: *** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 1324 page no 4 n bits 72 index uk_a of table `test`.`t1` /* Partition `p2` */ trx id 29508 lock_mode X locks rec but not gap waiting
Record lock, heap no 4 PHYSICAL RECORD: n_fields 3; compact format; info bits 0
0: len 4; hex 80000006; asc ;;
1: len 4; hex 80000002; asc ;;
2: len 8; hex 000000000000000f; asc ;;
2021-04-27T10:47:36.764752+08:00 84 [Note] InnoDB: *** WE ROLL BACK TRANSACTION (2)
How to repeat:
1、table
CREATE TABLE `t1` (
`sn_id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
`zone_id` int(11) NOT NULL,
`a` int(11) DEFAULT NULL,
`b` int(11) DEFAULT NULL,
`c` int(11) DEFAULT NULL,
PRIMARY KEY (`sn_id`,`zone_id`),
UNIQUE KEY `uk_a` (`a`,`zone_id`),
KEY `k_b` (`b`)
) ENGINE=InnoDB AUTO_INCREMENT=9 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin
/*!50100 PARTITION BY LIST (zone_id)
(PARTITION p1 VALUES IN (1) ENGINE = InnoDB,
PARTITION p2 VALUES IN (2) ENGINE = InnoDB,
PARTITION p3 VALUES IN (3) ENGINE = InnoDB,
PARTITION p4 VALUES IN (4) ENGINE = InnoDB) */
2、data
insert into t1 values
(null,1,1,1,2),
(null,1,3,2,1),
(null,1,5,3,3),
(null,1,7,3,3),
(null,2,2,1,1),
(null,2,4,4,1),
(null,2,6,4,1),
(null,2,8,5,1),
(null,3,9,6,2),
(null,4,10,1,3);
3、sql
（1）update adjacent records without partition fields, resulting in deadlock
a.sql execution sequence
session 1:
update t1 set c=12 where a=6;
session 2:
update t1 set c=12 where a=5;
session 1:
update t1 set c=12 where a=3;
session 2:
update t1 set c=12 where a=4;
（2）update adjacent records with partition fields, no waiting locks and no deadlocks
a.sql执行顺序
session 1:
update t1 set c=12 where a=6 and zone_id=2;
session 2:
update t1 set c=12 where a=5 and zone_id=1;
session 1:
update t1 set c=12 where a=3 and zone_id=1;
session 2:
update t1 set c=12 where a=4 and zone_id=2;
Description: 2021-04-27T10:47:36.764116+08:00 84 [Note] InnoDB: Transactions deadlock detected, dumping detailed information. 2021-04-27T10:47:36.764212+08:00 84 [Note] InnoDB: *** (1) TRANSACTION: TRANSACTION 29507, ACTIVE 69 sec fetching rows mysql tables in use 1, locked 1 LOCK WAIT 9 lock struct(s), heap size 1136, 5 row lock(s), undo log entries 1 MySQL thread id 83, OS thread handle 140265850660608, query id 2879131 localhost root updating update t1 set c=12 where a=3 2021-04-27T10:47:36.764258+08:00 84 [Note] InnoDB: *** (1) WAITING FOR THIS LOCK TO BE GRANTED: RECORD LOCKS space id 1323 page no 4 n bits 72 index uk_a of table `test`.`t1` /* Partition `p1` */ trx id 29507 lock_mode X locks rec but not gap waiting Record lock, heap no 4 PHYSICAL RECORD: n_fields 3; compact format; info bits 0 0: len 4; hex 80000005; asc ;; 1: len 4; hex 80000001; asc ;; 2: len 8; hex 000000000000000b; asc ;; 2021-04-27T10:47:36.764495+08:00 84 [Note] InnoDB: *** (2) TRANSACTION: TRANSACTION 29508, ACTIVE 49 sec fetching rows, thread declared inside InnoDB 4996 mysql tables in use 1, locked 1 9 lock struct(s), heap size 1136, 5 row lock(s) MySQL thread id 84, OS thread handle 140265850128128, query id 2879138 localhost root updating update t1 set c=12 where a=4 2021-04-27T10:47:36.764526+08:00 84 [Note] InnoDB: *** (2) HOLDS THE LOCK(S): RECORD LOCKS space id 1323 page no 4 n bits 72 index uk_a of table `test`.`t1` /* Partition `p1` */ trx id 29508 lock_mode X locks rec but not gap Record lock, heap no 4 PHYSICAL RECORD: n_fields 3; compact format; info bits 0 0: len 4; hex 80000005; asc ;; 1: len 4; hex 80000001; asc ;; 2: len 8; hex 000000000000000b; asc ;; 2021-04-27T10:47:36.764638+08:00 84 [Note] InnoDB: *** (2) WAITING FOR THIS LOCK TO BE GRANTED: RECORD LOCKS space id 1324 page no 4 n bits 72 index uk_a of table `test`.`t1` /* Partition `p2` */ trx id 29508 lock_mode X locks rec but not gap waiting Record lock, heap no 4 PHYSICAL RECORD: n_fields 3; compact format; info bits 0 0: len 4; hex 80000006; asc ;; 1: len 4; hex 80000002; asc ;; 2: len 8; hex 000000000000000f; asc ;; 2021-04-27T10:47:36.764752+08:00 84 [Note] InnoDB: *** WE ROLL BACK TRANSACTION (2) How to repeat: 1、table CREATE TABLE `t1` ( `sn_id` bigint(20) unsigned NOT NULL AUTO_INCREMENT, `zone_id` int(11) NOT NULL, `a` int(11) DEFAULT NULL, `b` int(11) DEFAULT NULL, `c` int(11) DEFAULT NULL, PRIMARY KEY (`sn_id`,`zone_id`), UNIQUE KEY `uk_a` (`a`,`zone_id`), KEY `k_b` (`b`) ) ENGINE=InnoDB AUTO_INCREMENT=9 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin /*!50100 PARTITION BY LIST (zone_id) (PARTITION p1 VALUES IN (1) ENGINE = InnoDB, PARTITION p2 VALUES IN (2) ENGINE = InnoDB, PARTITION p3 VALUES IN (3) ENGINE = InnoDB, PARTITION p4 VALUES IN (4) ENGINE = InnoDB) */ 2、data insert into t1 values (null,1,1,1,2), (null,1,3,2,1), (null,1,5,3,3), (null,1,7,3,3), (null,2,2,1,1), (null,2,4,4,1), (null,2,6,4,1), (null,2,8,5,1), (null,3,9,6,2), (null,4,10,1,3); 3、sql （1）update adjacent records without partition fields, resulting in deadlock a.sql execution sequence session 1: update t1 set c=12 where a=6; session 2: update t1 set c=12 where a=5; session 1: update t1 set c=12 where a=3; session 2: update t1 set c=12 where a=4; （2）update adjacent records with partition fields, no waiting locks and no deadlocks a.sql执行顺序 session 1: update t1 set c=12 where a=6 and zone_id=2; session 2: update t1 set c=12 where a=5 and zone_id=1; session 1: update t1 set c=12 where a=3 and zone_id=1; session 2: update t1 set c=12 where a=4 and zone_id=2;