- 联系我们
- duidaima.com 版权声明
- 闽ICP备2020021581号
-
闽公网安备 35020302035485号
闽公网安备 35020302035485号
------------------------
LATEST DETECTED DEADLOCK
------------------------
2023-12-11 17:55:14 7f6de3c0a700
*** (1) TRANSACTION:
TRANSACTION 67324219989, ACTIVE 0 sec inserting
mysql tables in use 1, locked 1
LOCK WAIT 5 lock struct(s), heap size 1184, 4 row lock(s), undo log entries 2
MySQL thread id 79690810, OS thread handle 0x7f6de0b4a700, query id 200590503593 x.x.x.x tms_boss_rw update
INSERT INTO delivery_packing_package_info (waybill_code, packing_code, packing_name, packing_type, packing_type_name,
packing_volume, volume_coefficient, packing_specification, packing_unit, packing_number,init_packing_number,
packing_charge,package_code,remark,
real_packing_number,
package_auto_match_flag,
create_user_code,
create_user_name,
create_time,
update_time,
yn)
VALUES
('xxxx00590163185', ...
'2023-12-11 17:55:13.622',
'2023-12-11 17:55:13.622',
1)
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 341 page no 6195 n bits 480 index `idx_waybill_code` of table `tms_boss`.`delivery_packing_package_info` trx id 67324219989 lock_mode X locks gap before rec insert intention waiting
Record lock, heap no 386 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
0: len 15; hex 4a444b413030353930313639383937; asc xxxx00590169897;;
1: len 8; hex 800000000005d001; asc ;;
*** (2) TRANSACTION:
TRANSACTION 67324219985, ACTIVE 0 sec inserting
mysql tables in use 1, locked 1
3 lock struct(s), heap size 1184, 2 row lock(s), undo log entries 1
MySQL thread id 79691432, OS thread handle 0x7f6de3c0a700, query id 200590503599 x.x.x.x tms_boss_rw update
INSERT INTO delivery_packing_package_info (waybill_code, packing_code, packing_name, packing_type, packing_type_name,
packing_volume, volume_coefficient, packing_specification, packing_unit, packing_number,init_packing_number,
packing_charge,package_code,remark,
real_packing_number,
package_auto_match_flag,
create_user_code,
create_user_name,
create_time,
update_time,
yn)
VALUES
('xxxx00590163967', ...
'2023-12-11 17:55:13.822',
'2023-12-11 17:55:13.822',
1)
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 341 page no 6195 n bits 480 index `idx_waybill_code` of table `tms_boss`.`delivery_packing_package_info` trx id 67324219985 lock_mode X locks gap before rec
Record lock, heap no 386 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
0: len 15; hex 4a444b413030353930313639383937; asc xxxx00590169897;;
1: len 8; hex 800000000005d001; asc ;;
*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 341 page no 6195 n bits 480 index `idx_waybill_code` of table `tms_boss`.`delivery_packing_package_info` trx id 67324219985 lock_mode X locks gap before rec insert intention waiting
Record lock, heap no 386 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
0: len 15; hex 4a444b413030353930313639383937; asc xxxx00590169897;;
1: len 8; hex 800000000005d001; asc ;;
*** WE ROLL BACK TRANSACTION (2)
其中：mysql> show create table `tms_boss`.`delivery_packing_package_info` \G
*************************** 1. row ***************************
Table: delivery_packing_package_info
Create Table: CREATE TABLE `delivery_packing_package_info` (
`id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '主键',
`waybill_code` varchar(32) DEFAULT NULL COMMENT '运单号',
`packing_code` varchar(32) DEFAULT NULL COMMENT '包装编号',
`packing_name` varchar(32) DEFAULT NULL COMMENT '包装名称',
`packing_type` varchar(32) DEFAULT NULL COMMENT '包装耗材类型',
`packing_type_name` varchar(32) DEFAULT NULL COMMENT '包装耗材类型名称',
`packing_volume` double DEFAULT NULL COMMENT '包装耗材体积',
`volume_coefficient` double DEFAULT NULL COMMENT '包装耗材体积系数',
`packing_specification` varchar(32) DEFAULT NULL COMMENT '包装规格',
`packing_unit` varchar(32) DEFAULT NULL COMMENT '包装耗材单位',
`packing_number` double DEFAULT NULL COMMENT '包装耗材数量',
`init_packing_number` double DEFAULT NULL COMMENT '包装耗材录入数量',
`real_packing_number` int(11) DEFAULT NULL COMMENT '真实耗材数',
`packing_charge` double DEFAULT NULL COMMENT '包装耗材单个价格',
`package_code` varchar(32) DEFAULT NULL COMMENT '包裹号/包裹编号',
`remark` varchar(3000) DEFAULT NULL COMMENT '合打/单打备注',
`package_auto_match_flag` int(11) DEFAULT NULL COMMENT '包裹号自动匹配flag。1-支持自动匹配 2-只支持扫描',
`create_user_code` varchar(32) DEFAULT NULL COMMENT '创建人账号',
`create_user_name` varchar(32) DEFAULT NULL COMMENT '创建人姓名',
`create_time` datetime DEFAULT NULL COMMENT '创建时间',
`update_time` datetime DEFAULT NULL COMMENT '更新时间',
`yn` int(11) DEFAULT NULL COMMENT '可用状态: 1 有效, 0无效',
PRIMARY KEY (`id`) USING BTREE,
KEY `idx_waybill_code` (`waybill_code`) USING BTREE,
KEY `idx_create_time` (`create_time`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=383557 DEFAULT CHARSET=utf8 COMMENT='包裹维度包装耗材信息表'
1 row in set (0.01 sec)
其中：#231211 17:55:14 server id 1683069784 end_log_pos 307231023 CRC32 0x1f48ba3e GTID last_committed=0 sequence_number=0 rbr_only=no
SET @@SESSION.GTID_NEXT= 'da4da38e-d71b-11ec-ad8b-fa163ed04e5b:12597525219'/*!*/;
# at 307231023
#231211 17:55:14 server id 1683069784 end_log_pos 307231086 CRC32 0xcfeee4b9 Query thread_id=79690810 exec_time=0 error_code=0
SET TIMESTAMP=1702288514/*!*/;
SET @@session.pseudo_thread_id=79690810/*!*/;
SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
SET @@session.sql_mode=524288/*!*/;
SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;
/*!\C utf8 *//*!*/;
SET @@session.character_set_client=33,@@session.collation_connection=33,@@session.collation_server=33/*!*/;
SET @@session.lc_time_names=0/*!*/;
SET @@session.collation_database=DEFAULT/*!*/;
BEGIN
/*!*/;
# at 307231086
#231211 17:55:14 server id 1683069784 end_log_pos 307231264 CRC32 0xe9813982 Rows_query
# UPDATE delivery_packing_package_info
# SET update_time = '2023-12-11 17:55:14.313', yn = 0
# WHERE waybill_code = 'xxxx00590163185' AND yn = 1
# at 307231264
#231211 17:55:14 server id 1683069784 end_log_pos 307232524 CRC32 0x0cab5bb1 Rows_query
# INSERT INTO delivery_packing_package_info (waybill_code, packing_code, packing_name, packing_type, packing_type_name,
# packing_volume, volume_coefficient, packing_specification, packing_unit, packing_number,init_packing_number,
# packing_charge,package_code,remark,
# real_packing_number,
# package_auto_match_flag,
# create_user_code,
# create_user_name,
# create_time,
# update_time,
# yn)
# VALUES
#
# ('xxxx00590163185', ...
# '2023-12-11 17:55:13.622',
# '2023-12-11 17:55:13.622',
# 1)
# at 307232524
其中：mysql> select id,waybill_code,yn,create_time from `tms_boss`.`delivery_packing_package_info` where waybill_code<='xxxx00590169897' order by waybill_code desc limit 8; +--------+-----------------+------+---------------------+ | id | waybill_code | yn | create_time | +--------+-----------------+------+---------------------+ | 380934 | xxxx00590169897 | 1 | 2023-12-11 17:50:56 | | 380933 | xxxx00590169897 | 1 | 2023-12-11 17:50:56 | | 380930 | xxxx00590169897 | 0 | 2023-12-11 17:50:47 | | 380929 | xxxx00590169897 | 0 | 2023-12-11 17:50:47 | <-- | 382335 | xxxx00590166721 | 1 | 2023-12-12 11:14:18 | | 380963 | xxxx00590163185 | 1 | 2023-12-11 17:55:14 | | 380944 | xxxx00590163185 | 0 | 2023-12-11 17:52:41 | <-- | 382673 | xxxx00590162878 | 1 | 2023-12-12 12:47:19 | +--------+-----------------+------+---------------------+ 8 rows in set (0.00 sec)其中：
mysql> select count(*) from `tms_boss`.`delivery_packing_package_info` where waybill_code='xxxx00590163967'; +----------+ | count(*) | +----------+ | 0 | +----------+ 1 row in set (0.00 sec)其中：
mysql> show create table tb \G
*************************** 1. row ***************************
Table: tb
Create Table: CREATE TABLE `tb` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`a` int(11) NOT NULL DEFAULT '0',
`b` int(11) NOT NULL DEFAULT '0',
PRIMARY KEY (`id`),
KEY `idx_a` (`a`)
) ENGINE=InnoDB AUTO_INCREMENT=15 DEFAULT CHARSET=utf8
1 row in set (0.00 sec)
mysql> insert into tb(id,a,b) values(1,1,1),(5,5,5),(9,9,9);
Query OK, 3 rows affected (0.01 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql> select * from tb;
+----+---+---+
| id | a | b |
+----+---+---+
| 1 | 1 | 1 |
| 5 | 5 | 5 |
| 9 | 9 | 9 |
+----+---+---+
3 rows in set (0.00 sec)
其中：主键索引 + 非唯一二级索引| session 1 | session 2 |
|---|---|
| begin; |
|
|
|
begin; |
| update tb set b=4 where a=5; |
|
|
|
update tb set b=5 where a=6; |
|
insert into tb(a,b) values(5,5); Blocked |
|
|
|
insert into tb(a,b) values(6,6); Deadlock found |
死锁复现
------------------------ LATEST DETECTED DEADLOCK ------------------------ 2023-12-12 17:14:16 0x7fdeb5059700 *** (1) TRANSACTION: TRANSACTION 132925, ACTIVE 19 sec inserting mysql tables in use 1, locked 1 LOCK WAIT 5 lock struct(s), heap size 1136, 4 row lock(s), undo log entries 2 MySQL thread id 70, OS thread handle 140594496775936, query id 9070 127.0.0.1 admin update insert into tb(a,b) values(5,5) *** (1) WAITING FOR THIS LOCK TO BE GRANTED: RECORD LOCKS space id 480 page no 4 n bits 72 index idx_a of table `test_zk`.`tb` trx id 132925 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 80000009; asc ;; 1: len 4; hex 80000009; asc ;; *** (2) TRANSACTION: TRANSACTION 132926, ACTIVE 11 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 71, OS thread handle 140594496509696, query id 9071 127.0.0.1 admin update insert into tb(a,b) values(6,6) *** (2) HOLDS THE LOCK(S): RECORD LOCKS space id 480 page no 4 n bits 72 index idx_a of table `test_zk`.`tb` trx id 132926 lock_mode X locks gap before rec 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 ;; *** (2) WAITING FOR THIS LOCK TO BE GRANTED: RECORD LOCKS space id 480 page no 4 n bits 72 index idx_a of table `test_zk`.`tb` trx id 132926 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 80000009; asc ;; 1: len 4; hex 80000009; asc ;; *** WE ROLL BACK TRANSACTION (2)其中：
mysql> insert into tb(id,a,b) values(6,6,6); Query OK, 1 row affected (0.00 sec) mysql> select * from tb; +----+---+---+ | id | a | b | +----+---+---+ | 1 | 1 | 1 | | 5 | 5 | 5 | | 6 | 6 | 6 | | 9 | 9 | 9 | +----+---+---+ 4 rows in set (0.00 sec)操作流程见下表，其中两个事务均更新存在的记录。
| session 1 | session 2 |
|---|---|
| begin; |
|
|
|
begin; |
| update tb set b=4 where a=5; |
|
|
|
update tb set b=5 where a=6; |
|
insert into tb(a,b) values(5,5); Blocked |
|
|
|
insert into tb(a,b) values(6,6); Query OK, 1 row affected (0.00 sec) |
---TRANSACTION 132938, ACTIVE 16 sec 4 lock struct(s), heap size 1136, 4 row lock(s), undo log entries 2 MySQL thread id 71, OS thread handle 140594496509696, query id 9092 127.0.0.1 admin TABLE LOCK table `test_zk`.`tb` trx id 132938 lock mode IX RECORD LOCKS space id 480 page no 4 n bits 72 index idx_a of table `test_zk`.`tb` trx id 132938 lock_mode X 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 80000006; asc ;; RECORD LOCKS space id 480 page no 3 n bits 80 index PRIMARY of table `test_zk`.`tb` trx id 132938 lock_mode X locks rec but not gap Record lock, heap no 5 PHYSICAL RECORD: n_fields 5; compact format; info bits 0 0: len 4; hex 80000006; asc ;; 1: len 6; hex 00000002074a; asc J;; 2: len 7; hex 7000000024030e; asc p $ ;; 3: len 4; hex 80000006; asc ;; 4: len 4; hex 80000005; asc ;; RECORD LOCKS space id 480 page no 4 n bits 72 index idx_a of table `test_zk`.`tb` trx id 132938 lock_mode X locks gap before rec 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 lock, heap no 6 PHYSICAL RECORD: n_fields 2; compact format; info bits 0 0: len 4; hex 80000006; asc ;; 1: len 4; hex 80000014; asc ;; ---TRANSACTION 132937, ACTIVE 25 sec inserting mysql tables in use 1, locked 1 LOCK WAIT 5 lock struct(s), heap size 1136, 4 row lock(s), undo log entries 2 MySQL thread id 70, OS thread handle 140594496775936, query id 9091 127.0.0.1 admin update insert into tb(a,b) values(5,5) Trx read view will not see trx with id >= 132937, sees < 132937 ------- TRX HAS BEEN WAITING 9 SEC FOR THIS LOCK TO BE GRANTED: RECORD LOCKS space id 480 page no 4 n bits 72 index idx_a of table `test_zk`.`tb` trx id 132937 lock_mode X locks gap before rec insert intention waiting 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 80000006; asc ;; ------------------ TABLE LOCK table `test_zk`.`tb` trx id 132937 lock mode IX RECORD LOCKS space id 480 page no 4 n bits 72 index idx_a of table `test_zk`.`tb` trx id 132937 lock_mode X Record lock, heap no 3 PHYSICAL RECORD: n_fields 2; compact format; info bits 0 0: len 4; hex 80000005; asc ;; 1: len 4; hex 80000005; asc ;; RECORD LOCKS space id 480 page no 3 n bits 80 index PRIMARY of table `test_zk`.`tb` trx id 132937 lock_mode X locks rec but not gap Record lock, heap no 3 PHYSICAL RECORD: n_fields 5; compact format; info bits 0 0: len 4; hex 80000005; asc ;; 1: len 6; hex 000000020749; asc I;; 2: len 7; hex 6f000000292dc9; asc o )- ;; 3: len 4; hex 80000005; asc ;; 4: len 4; hex 80000004; asc ;; RECORD LOCKS space id 480 page no 4 n bits 72 index idx_a of table `test_zk`.`tb` trx id 132937 lock_mode X 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 80000006; asc ;; RECORD LOCKS space id 480 page no 4 n bits 72 index idx_a of table `test_zk`.`tb` trx id 132937 lock_mode X locks gap before rec insert intention waiting 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 80000006; asc ;;其中：
update tb set b=5 where a=6; insert into tb(a,b) values(6,6);给函数lock_rec_add_to_queue设置断点，执行 insert 语句时查看堆栈。
static
void
lock_rec_inherit_to_gap_if_gap_lock(
/*================================*/
const buf_block_t* block, /*!< in: buffer block */
ulint heir_heap_no, /*!< in: heap_no of
record which inherits */
ulint heap_no) /*!< in: heap_no of record
from which inherited;
does NOT reset the locks
on this record */
{
lock_t* lock;
...
// 堆代码 duidaima.com
// 遍历记录上的所有锁
for (lock = lock_rec_get_first(lock_sys->rec_hash, block, heap_no);
lock != NULL;
lock = lock_rec_get_next(heap_no, lock)) {
// 如果不是插入意向锁
if (!lock_rec_get_insert_intention(lock)
&& (heap_no == PAGE_HEAP_NO_SUPREMUM
// 是LOCK_GAP或者NEXT-KEY LOCK（没有设置LOCK_REC_NOT_GAP标记)
|| !lock_rec_get_rec_not_gap(lock))) {
// 给事务增加一个新的锁对象，锁的类型为LOCK_REC | LOCK_GAP
// 所有符合条件的会话都继承了这个新的GAP，避免之前的GAP锁失效
lock_rec_add_to_queue(
LOCK_REC | LOCK_GAP | lock_get_mode(lock),
block, heir_heap_no, lock->index,
lock->trx, FALSE);
}
}
...
}
其中：/*************************************************************//**
Updates the lock table when a new user record is inserted. */
void
lock_update_insert(
/*===============*/
const buf_block_t* block, /*!< in: buffer block containing rec */
const rec_t* rec) /*!< in: the inserted record */
{
ulint receiver_heap_no;
ulint donator_heap_no;
// block->frame 中保存数据页的真实内容
ut_ad(block->frame == page_align(rec));
/* Inherit the gap-locking locks for rec, in gap mode, from the next
record */
// 获取 heap_no
...
receiver_heap_no = rec_get_heap_no_new(rec);
donator_heap_no = rec_get_heap_no_new(
page_rec_get_next_low(rec, TRUE));
...
// heir_heap_no=receiver_heap_no，heap_no=donator_heap_no
lock_rec_inherit_to_gap_if_gap_lock(
block, receiver_heap_no, donator_heap_no);
}
其中： if (!(flags & BTR_NO_LOCKING_FLAG) && inherit) {
lock_update_insert(block, *rec);
}
其中：// 通过 cursor 获取 block block = btr_cur_get_block(cursor); // buf_block_t::frame 中保存 page page = buf_block_get_frame(block); index = cursor->index;inherit 变量在初始化后修改。
ibool inherit = TRUE;
err = lock_rec_insert_check_and_lock(
flags, rec, btr_cur_get_block(cursor),
index, thr, mtr, inherit);
其中：dberr_t
lock_rec_insert_check_and_lock(...){
...
ibool inherit_in = *inherit;
trx_t* trx = thr_get_trx(thr);
// 获取下一条记录
const rec_t* next_rec = page_rec_get_next_const(rec);
ulint heap_no = page_rec_get_heap_no(next_rec);
// 判断下一条记录上是否有锁
lock = lock_rec_get_first(lock_sys->rec_hash, block, heap_no);
// lock_rec_get_first返回 NULL 表示下一个记录上没有锁，因此使用隐式锁
if (lock == NULL) {
if (inherit_in && !dict_index_is_clust(index)) {
/* Update the page max trx id field */
// 直接更新二级索引trx id
// 更新页的最大事务ID
page_update_max_trx_id(block,
buf_block_get_page_zip(block),
trx->id, mtr);
}
// 不需要锁分裂
*inherit = FALSE;
return(DB_SUCCESS);
}
// 可能需要锁分裂
*inherit = TRUE;
/* If another transaction has an explicit lock request which locks
the gap, waiting or granted, on the successor, the insert has to wait.*/
// 下一条记录上有锁，然后判断是否有锁冲突
// 插入意向锁
const ulint type_mode = LOCK_X | LOCK_GAP | LOCK_INSERT_INTENTION;
// 判断是否与插入意向锁冲突，也就是检查是否有间隙锁
const lock_t* wait_for = lock_rec_other_has_conflicting(
type_mode, block, heap_no, trx);
// 有冲突时进入锁等待
if (wait_for != NULL) {
RecLock rec_lock(thr, index, block, heap_no, type_mode);
// 如果与插入意向锁有冲突，创建一个插入意向锁，加到事务锁列表中去，插入等待队列中
err = rec_lock.add_to_waitq(wait_for);
} else {
err = DB_SUCCESS;
}
switch (err) {
case DB_SUCCESS_LOCKED_REC:
err = DB_SUCCESS;
/* fall through */
case DB_SUCCESS:
if (!inherit_in || dict_index_is_clust(index)) {
break;
}
/* Update the page max trx id field */
// 如果没有冲突，直接更新页的最大事务ID，然后返回成功
page_update_max_trx_id(
block, buf_block_get_page_zip(block), trx->id, mtr);
default:
/* We only care about the two return values. */
break;
}
...
}
其中：同一个事务持有 gap lock 的前提下插入数据，会发生锁分裂，如果是其他事务持有 gap lock，会发生插入意向锁等待。
RECORD LOCKS space id 480 page no 4 n bits 72 index idx_a of table `test_zk`.`tb` trx id 132938 lock_mode X locks gap before rec 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 lock, heap no 6 PHYSICAL RECORD: n_fields 2; compact format; info bits 0 0: len 4; hex 80000006; asc ;; 1: len 4; hex 80000014; asc ;;测试过程中数据插入的顺序如下所示。
insert into tb(id,a,b) values(1,1,1),(5,5,5),(9,9,9); insert into tb(id,a,b) values(6,6,6); insert into tb(a,b) values(6,6); // (id,a,b)=(14,6,6)idx_a 索引各记录的 heap_no 值如下所示。
infimum：0 supremum：1 a = 1：2(id = 1) a = 5：3(id = 5) a = 9：4(id = 9) a = 6：5(id = 6) a = 6：6(id = 14)显然 heap_no 的顺序与索引值的顺序不同，那么什么是 heap 和 heap_no？
参考《MySQL 运维内参》：
heap_no 保存在 Record Header 中，占用 13 比特，表示当前记录在页面堆中的相对位置。
heap_no 尽管只是一个逻辑顺序编号，但是每条记录按照主键从小到大的顺序组成一个有序单向链表。
