无用：update dup 死锁

作者: 重庆八怪 | 来源:发表于2020-01-16 14:40 被阅读0次

无用：update dup 死锁
MySQL死锁分析
insert on update死锁
发生死锁的情形
sql语句中的坑
mysql for update 死锁问题
MySQL UPDATE 死锁分析
批量insert into... on duplicate ke
经典问题处理记录
MYSQL死锁（insert&update）

记录死锁分析未完成。大概思路确认了，需要搞明白流程

*** (1) TRANSACTION:
TRANSACTION 971006683, ACTIVE 0 sec inserting
mysql tables in use 1, locked 1
LOCK WAIT 3 lock struct(s), heap size 1136, 2 row lock(s), undo log entries 1
MySQL thread id 2615633, OS thread handle 140479196354304, query id 1868675169 10.11.93.112 studenttask update
INSERT INTO student_task SET prestudyTaskStatus = 0,prestudyTaskNum = 0,rolePlayTaskStatus = 0,rolePlayTaskNum = 0,homeworkTaskStatus = 0,homeworkTaskNum = 0,animationTaskStatus = 0,animationTaskNum = 0,replayTaskStatus = 0,replayTaskdNum = 0,updatedTime = 1578473744880,createdTime = 1578473744880,studentId = 155052010,episodeId = 121635098,lessonId = 3153809,ticketId = 884505443 ON DUPLICATE KEY UPDATE ticketId = 884505443
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 119 page no 2621373 n bits 624 index studentId_lessonId_episodeId of table tutor_student_task.student_task trx id 971006683 lock_mode X locks gap before rec insert intention waiting LOCK_X|LOCK_IN|LOCK_WAIT
Record lock, heap no 194 PHYSICAL RECORD: n_fields 4; compact format; info bits 0
0: len 4; hex 893de7ea; asc = ;; 155052010
1: len 4; hex 00301f91; asc 0 ;; 3153809
2: len 4; hex 0740011e; asc @ ;; 121635102
3: len 8; hex 800000001b316983; asc 1i ;; 456223107
*** (2) TRANSACTION:
TRANSACTION 971006682, ACTIVE 0 sec inserting, thread declared inside InnoDB 1
mysql tables in use 1, locked 1
4 lock struct(s), heap size 1136, 3 row lock(s), undo log entries 1
MySQL thread id 2616899, OS thread handle 140478316992256, query id 1868675168 10.11.93.112 studenttask update
INSERT INTO student_task SET prestudyTaskStatus = 0,prestudyTaskNum = 0,rolePlayTaskStatus = 0,rolePlayTaskNum = 0,homeworkTaskStatus = 0,homeworkTaskNum = 0,animationTaskStatus = 0,animationTaskNum = 0,replayTaskStatus = 0,replayTaskdNum = 0,updatedTime = 1578473744880,createdTime = 1578473744880,studentId = 155052010,episodeId = 121635101,lessonId = 3153809,ticketId = 884505439 ON DUPLICATE KEY UPDATE ticketId = 884505439
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 119 page no 2621373 n bits 624 index studentId_lessonId_episodeId of table tutor_student_task.student_task trx id 971006682 lock_mode X locks gap before rec
Record lock, heap no 194 PHYSICAL RECORD: n_fields 4; compact format; info bits 0
0: len 4; hex 893de7ea; asc = ;;
1: len 4; hex 00301f91; asc 0 ;;
2: len 4; hex 0740011e; asc @ ;;
3: len 8; hex 800000001b316983; asc 1i ;;
*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 119 page no 2621373 n bits 624 index studentId_lessonId_episodeId of table tutor_student_task.student_task trx id 971006682 lock_mode X locks gap before rec insert intention waiting
Record lock, heap no 194 PHYSICAL RECORD: n_fields 4; compact format; info bits 0
0: len 4; hex 893de7ea; asc = ;;
1: len 4; hex 00301f91; asc 0 ;;
2: len 4; hex 0740011e; asc @ ;;
3: len 8; hex 800000001b316983; asc 1i ;;
*** WE ROLL BACK TRANSACTION (1)

CREATE TABLE student_task (
id bigint(20) NOT NULL AUTO_INCREMENT COMMENT '自增 id',
studentId int(11) NOT NULL COMMENT '学生 id',
episodeId int(11) unsigned NOT NULL COMMENT '课程 id',
ticketId bigint(20) unsigned NOT NULL DEFAULT '0' COMMENT 'ticket id',
lessonId int(11) unsigned NOT NULL DEFAULT '0' COMMENT '班课 id',
prestudyTaskStatus int(11) unsigned NOT NULL COMMENT '预习任务状态',
prestudyTaskNum int(11) unsigned NOT NULL COMMENT '预习任务数量',
rolePlayTaskStatus int(11) unsigned NOT NULL COMMENT '角色扮演任务状态',
rolePlayTaskNum int(11) unsigned NOT NULL COMMENT '角色扮演任务数量',
homeworkTaskStatus int(11) unsigned NOT NULL COMMENT '作业任务状态',
homeworkTaskNum int(11) unsigned NOT NULL COMMENT '作业任务数量',
animationTaskStatus int(11) unsigned NOT NULL COMMENT '动画任务状态',
animationTaskNum int(11) unsigned NOT NULL COMMENT '动画任务数量',
replayTaskStatus int(11) unsigned NOT NULL COMMENT '回放任务状态',
replayTaskdNum int(11) unsigned NOT NULL COMMENT '回放任务数量',
createdTime bigint(20) NOT NULL COMMENT '创建时间',
updatedTime bigint(20) NOT NULL COMMENT '更新时间',
PRIMARY KEY (id),
UNIQUE KEY studentId_lessonId_episodeId (studentId,lessonId,episodeId),
KEY episodeId (episodeId),
KEY ticketId (ticketId),
KEY lessonId (lessonId)
) ENGINE=InnoDB AUTO_INCREMENT=458301510 DEFAULT CHARSET=utf8mb4

INSERT INTO student_task SET prestudyTaskStatus = 0,prestudyTaskNum = 0,rolePlayTaskStatus = 0,rolePlayTaskNum = 0,homeworkTaskStatus = 0,homeworkTaskNum = 0,animationTaskStatus = 0,animationTaskNum = 0,replayTaskStatus = 0,replayTaskdNum = 0,updatedTime = 1578473744880,createdTime = 1578473744880,
studentId = 155052010,episodeId = 121635098,lessonId = 3153809,
ticketId = 884505443 ON DUPLICATE KEY UPDATE ticketId = 884505443
INSERT INTO student_task SET prestudyTaskStatus = 0,prestudyTaskNum = 0,rolePlayTaskStatus = 0,rolePlayTaskNum = 0,homeworkTaskStatus = 0,homeworkTaskNum = 0,animationTaskStatus = 0,animationTaskNum = 0,replayTaskStatus = 0,replayTaskdNum = 0,updatedTime = 1578473744880,createdTime = 1578473744880,
studentId = 155052010,episodeId = 121635101,lessonId = 3153809,
ticketId = 884505439 ON DUPLICATE KEY UPDATE ticketId = 884505439

0: len 4; hex 893de7ea; asc = ;; 155052010 studentId
1: len 4; hex 00301f91; asc 0 ;; 3153809 episodeId
2: len 4; hex 0740011e; asc @ ;; 121635102 lessonId (不一样)

3: len 8; hex 800000001b316983; asc 1i ;; 456223107

T1: 1 ROW LOCK

等待 LOCK_X|LOCK_INSERT_INTENTION|LOCK_WAIT|LOCK_GAP

T2: 3 ROW LOCK

获取 LOCK_X|LOCK_GAP

等待 LOCK_X|LOCK_INSERT_INTENTION|LOCK_WAIT|LOCK_GAP

检查主键是否违反 唯一冲突
#0  row_ins_duplicate_error_in_clust (flags=0, cursor=0x7fffec0f3680, entry=0x7ffe7cb02a10, thr=0x7ffe7cba9430, mtr=0x7fffec0f2e50)
    at /root/mysqlall/percona-server-locks-detail-5.7.22/storage/innobase/row/row0ins.cc:2268
#1  0x0000000001b17a9d in row_ins_clust_index_entry_low (flags=0, mode=2, index=0x7ffe7cac46e0, n_uniq=1, entry=0x7ffe7cb02a10, n_ext=0, thr=0x7ffe7cba9430, 
    dup_chk_only=false) at /root/mysqlall/percona-server-locks-detail-5.7.22/storage/innobase/row/row0ins.cc:2556


检查是否有gap 阻止插入 主键
#0  lock_rec_insert_check_and_lock (flags=0, rec=0x7fff81a1412d "\200", block=0x7fff80e89020, index=0x7ffe7cac46e0, thr=0x7ffe7cba9430, mtr=0x7fffec0f2e50, 
    inherit=0x7fffec0f2a80) at /root/mysqlall/percona-server-locks-detail-5.7.22/storage/innobase/lock/lock0lock.cc:6019
#1  0x0000000001c52f36 in btr_cur_ins_lock_and_undo (flags=0, cursor=0x7fffec0f3680, entry=0x7ffe7cb02a10, thr=0x7ffe7cba9430, mtr=0x7fffec0f2e50, 
    inherit=0x7fffec0f2a80) at /root/mysqlall/percona-server-locks-detail-5.7.22/storage/innobase/btr/btr0cur.cc:3035
#2  0x0000000001c538a5 in btr_cur_optimistic_insert (flags=0, cursor=0x7fffec0f3680, offsets=0x7fffec0f37b0, heap=0x7fffec0f37b8, entry=0x7ffe7cb02a10, 
    rec=0x7fffec0f37a8, big_rec=0x7fffec0f37c0, n_ext=0, thr=0x7ffe7cba9430, mtr=0x7fffec0f2e50)
    at /root/mysqlall/percona-server-locks-detail-5.7.22/storage/innobase/btr/btr0cur.cc:3277
#3  0x0000000001b17cb9 in row_ins_clust_index_entry_low (flags=0, mode=2, index=0x7ffe7cac46e0, n_uniq=1, entry=0x7ffe7cb02a10, n_ext=0, thr=0x7ffe7cba9430, 

主键插入 btr_cur_optimistic_insert
         page_cur_tuple_insert



检查唯一索引1 唯一冲突

#0  row_ins_scan_sec_index_for_duplicate (flags=0, index=0x7ffe7cb02de0, entry=0x7ffe7cb02ae8, thr=0x7ffe7cba9430, s_latch=false, mtr=0x7fffec0f2ec0, 
    offsets_heap=0x7ffe7c9d3bf8) at /root/mysqlall/percona-server-locks-detail-5.7.22/storage/innobase/row/row0ins.cc:2045
#1  0x0000000001b19001 in row_ins_sec_index_entry_low (flags=0, mode=2, index=0x7ffe7cb02de0, offsets_heap=0x7ffe7c9d3bf8, heap=0x7ffe7cac3d88, entry=0x7ffe7cb02ae8, 
    trx_id=0, thr=0x7ffe7cba9430, dup_chk_only=false) at /root/mysqlall/percona-server-locks-detail-5.7.22/storage/innobase/row/row0ins.cc:3034

检查是否有gap 阻止插入 二级索引1
#0  lock_rec_insert_check_and_lock (flags=0, rec=0x7fff81a180d8 "\200", block=0x7fff80e89530, index=0x7ffe7cb02de0, thr=0x7ffe7cba9430, mtr=0x7fffec0f2ec0, 
    inherit=0x7fffec0f2330) at /root/mysqlall/percona-server-locks-detail-5.7.22/storage/innobase/lock/lock0lock.cc:6019
#1  0x0000000001c52f36 in btr_cur_ins_lock_and_undo (flags=0, cursor=0x7fffec0f36f0, entry=0x7ffe7cb02ae8, thr=0x7ffe7cba9430, mtr=0x7fffec0f2ec0, 
    inherit=0x7fffec0f2330) at /root/mysqlall/percona-server-locks-detail-5.7.22/storage/innobase/btr/btr0cur.cc:3035
#2  0x0000000001c538a5 in btr_cur_optimistic_insert (flags=0, cursor=0x7fffec0f36f0, offsets=0x7fffec0f37c8, heap=0x7fffec0f26e0, entry=0x7ffe7cb02ae8, 
    rec=0x7fffec0f37c0, big_rec=0x7fffec0f37b8, n_ext=0, thr=0x7ffe7cba9430, mtr=0x7fffec0f2ec0)
    at /root/mysqlall/percona-server-locks-detail-5.7.22/storage/innobase/btr/btr0cur.cc:3277
#3  0x0000000001b195fe in row_ins_sec_index_entry_low (flags=0, mode=2, index=0x7ffe7cb02de0, offsets_heap=0x7ffe7c9d3bf8, heap=0x7ffe7cac3d88, entry=0x7ffe7cb02ae8, 
    trx_id=0, thr=0x7ffe7cba9430, dup_chk_only=false) at /root/mysqlall/percona-server-locks-detail-5.7.22/storage/innobase/row/row0ins.cc:3166
    
插入 page_cur_tuple_insert

无用：update dup 死锁
记录死锁分析未完成。大概思路确认了，需要搞明白流程 *** (1) TRANSACTION:TRANSACTIO...
MySQL死锁分析
一、表结构,死锁日志，事务隔离级别表结构：死锁日志：事务隔离级别为读已提交。二、insert on dup...
insert on update死锁
表结构隔离级别 RR 死锁日志2020-01-08 16:55:44 0x7fc3dc431700*** (1)...
发生死锁的情形
多条insert on duplicate key update 死锁的原因 https://mp.weixin...
sql语句中的坑
1、update无主键时lock table 连接数打满update 必须是主键，否则可能死锁http://bl...
mysql for update 死锁问题
在RR条件， id是主键，插入五条数据我们可以发现针对事务一：select * from user where...
MySQL UPDATE 死锁分析
Table Update Analyse innodb update语句会执行以下步骤：若1,2步骤之间有类似语...
批量insert into... on duplicate ke
同一事务批量 insert into... on duplicate key update... 会产生死锁。原因...
经典问题处理记录
1.mysql update操作死锁问题故障描述日志监控发现少量数据库死锁异常问题原因设计方案使用悲观锁s...
MYSQL死锁（insert&update）
错误信息问题代码A 参考资料 http://www.cnblogs.com/crazylqy/p/7689447...

无用：update dup 死锁

相关文章

无用：update dup 死锁

MySQL死锁分析

insert on update死锁

发生死锁的情形

sql语句中的坑

mysql for update 死锁问题

MySQL UPDATE 死锁分析

批量insert into... on duplicate ke

经典问题处理记录

MYSQL死锁（insert&update）

网友评论

延伸阅读

深度阅读

栏目导航

热点阅读