由于多个线程插入,MySQL中出现死锁 [英] Deadlock in MySQL due to Insert by multiple threads

查看:145
本文介绍了由于多个线程插入,MySQL中出现死锁的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!

问题描述

我有一个多线程应用程序,试图以多个批次对表中的记录进行 INSERT .每个线程处理一批.有时我会出现死锁错误,以下是跟踪.

我要在其中插入记录的表是这样的:

RecordBase(Col1,Col2,Col3)

Col1 Col2 一起形成一个复合主键.

我之前认为这可能是由于索引-record 锁定,但是跟踪清楚地表明,相互阻塞的语句没有任何重复的记录.那为什么会导致死锁呢?

  ------------------------最新检测到的死锁------------------------2015-09-09 17:13:22 2b70324de700***(1)交易:交易1787379600,正在活动中,在使用中插入mysql表的时间为7秒,已锁定1 LOCK WAIT 486锁结构,堆大小63016,13085行锁,撤消日志条目8713 MySQL线程ID 537443,OS线程句柄0x2b703286c700,查询ID 578560605 127.0.0.1 192.168.1.195 demoreleaseroot更新插入Record_Base值('da5fd95c-4d8e-11e5-9761-22000bd9028a','101e7dcd-4c81-11e5-9ca0-22000bd8028c','0'),('da5fcf08-4d8e-11e5-9761-22000bd9028a','101e7dcd-4c81-11e5-9ca0-22000bd8028c','0'),('da5fc4eb-4d8e-11e5-9761-22000bd9028a','101e7dcd-4c81-11e5-9ca0-22000bd8028c','0'),('da5fbabe-4d8e-11e5-9761-22000bd9028a','101e7dcd-4c81-11e5-9ca0-22000bd8028c','0'),('da5fb087-4d8e-11e5-9761-22000bd9028a','101e7dcd-4c81-11e5-9ca0-22000bd8028c','0'),('da5fa616-4d8e-11e5-9761-22000bd9028a','101e7dcd-4c81-11e5-9ca0-22000bd8028c','0'),('da5f99bf-4d8e-11e5-9761-22000bd9028a','101e7dcd-4c81-11e5-9ca0-22000bd8028c','0'),('da5f8f0f-4d8e-11e5-9761-22000bd9028a','101e7dcd-4c81-11e5-9ca0-22000bd8028c','0'),('da5f5e2e-4d8e-11e5-9761-22000bd9028a','101e7dcd-4c81-11e5-9ca0-22000bd8028c','0'),('da5f52e3-4d8e-11e5-9761-22000bd9028a','101e7d***(1)等待授予此锁定:记录锁定空间ID 26232190页号5961 n位160索引表`provalant101_mxradon`的`PRIMARY`.`Record_Base`trx ID 1787379600 lock_mode X锁定间隙,然后再插入记录意图记录锁定,没有堆29物理记录:n_fields 5;紧凑格式信息位0 0:len 30;六角形65376566306364332d353039352d313165352d393736312d323230303062;asc e7ef0cd3-5095-11e5-9761-22000b;(总共36个字节);1:伦30;十六进制31303165376463642d346338312d313165352d396361302d323230303062;asc 101e7dcd-4c81-11e5-9ca0-22000b;(总共36个字节);2:伦6;十六进制00006a893f90;asc j吗?;;3:伦7;六角b40001a7c3290f;asc);;4:伦4;十六进制80000000;asc ;;***(2)事务:事务1787379848,活动1秒钟,正在插入正在使用的mysql表,已锁定1 1030锁结构,堆大小112168,5801行锁,撤消日志条目2639 MySQL线程ID 537467,OS线程句柄0x2b70324de700,查询ID 578563042 127.0.0.1 192.168.1.195 demoreleaseroot更新INSERT INTO Record_Base VALUES('4849f98e-5094-11e5-9761-22000bd9028a','101e7dcd-4c81-11e5-9ca0-22000bd8028c','0'),('4849ebe5-5094-11e5-9761-22000bd9028a','101e7dcd-4c81-11e5-9ca0-22000bd8028c','0'),('4849c44c-5094-11e5-9761-22000bd9028a','101e7dcd-4c81-11e5-9ca0-22000bd8028c','0'),('4849add7-5094-11e5-9761-22000bd9028a','101e7dcd-4c81-11e5-9ca0-22000bd8028c','0'),('4849a0ef-5094-11e5-9761-22000bd9028a','101e7dcd-4c81-11e5-9ca0-22000bd8028c','0'),('48499430-5094-11e5-9761-22000bd9028a','101e7dcd-4c81-11e5-9ca0-22000bd8028c','0'),('48498752-5094-11e5-9761-22000bd9028a','101e7dcd-4c81-11e5-9ca0-22000bd8028c','0'),('48496d2d-5094-11e5-9761-22000bd9028a','101e7dcd-4c81-11e5-9ca0-22000bd8028c','0'),('4848731e-5094-11e5-9761-22000bd9028a','101e7dcd-4c81-11e5-9ca0-22000bd8028c','0'),('4846784e-5094-11e5-9761-22000bd9028a','101e7d***(2)保持锁定:记录锁定空间ID 26232190页号5961 n位152表provantant101_mxradon`的索引`PRIMARY`.`Record_Base`trx id 1787379848 lock_mode X锁定记录之前的间隙记录锁,堆否29物理记录:n_fields 5;紧凑格式信息位0 0:len 30;六角形65376566306364332d353039352d313165352d393736312d323230303062;asc e7ef0cd3-5095-11e5-9761-22000b;(总共36个字节);1:伦30;十六进制31303165376463642d346338312d313165352d396361302d323230303062;asc 101e7dcd-4c81-11e5-9ca0-22000b;(总共36个字节);2:伦6;十六进制00006a893f90;asc j吗?;;3:伦7;六角b40001a7c3290f;asc);;4:伦4;十六进制80000000;asc ;;***(2)等待授予此锁:记录锁空间ID 26232190页号14639 n位192表`provalant101_mxradon`的索引`PRIMARY`.`Record_Base`trx id 1787379848 lock_mode X锁定间隙,然后插入记录意图记录锁定,堆号121物理记录:n_fields 5;紧凑格式信息位0 0:len 30;六角形38393531613333352d353039342d313165352d393736312d323230303062;asc 8951a335-5094-11e5-9761-22000b;(总共36个字节);1:伦30;十六进制31303165376463642d346338312d313165352d396361302d323230303062;asc 101e7dcd-4c81-11e5-9ca0-22000b;(总共36个字节);2:伦6;十六进制00006a893f90;asc j吗?;;3:伦7;十六进制b40001a7c71c1c;asc ;;4:伦4;十六进制80000000;asc ;;***我们回滚交易(2) 

解决方案

这类死锁称为间隙锁".我发现此帖子很有帮助./p>

此外,您可以在 MySQL手册

I have a multithreaded application that tries to INSERT a record in a table in multiple batches. Each thread processing one batch. At times I get Deadlock error, following is the trace.

The table I am trying to insert a record into is like this:

RecordBase (Col1, Col2, Col3)

Col1 and Col2 together form a composite primary key.

I earlier thought it could be due to an index-record lock but the trace clearly shows that the statements that are blocking each other do not have any duplicate records. So why is it causing deadlock?

------------------------ LATEST DETECTED DEADLOCK ------------------------ 
 2015-09-09 17:13:22 2b70324de700 

 *** (1) TRANSACTION: 
 TRANSACTION 1787379600, ACTIVE 7 sec inserting mysql tables in use 1, locked 1 LOCK WAIT 486 lock struct(s), heap size 63016, 13085 row lock(s), undo log entries 8713 MySQL thread id 537443, OS thread handle 0x2b703286c700, query id 578560605 127.0.0.1 192.168.1.195 demoreleaseroot update 
 INSERT INTO Record_Base VALUES 
 ('da5fd95c-4d8e-11e5-9761-22000bd9028a','101e7dcd-4c81-11e5-9ca0-22000bd8028c','0'),
 ('da5fcf08-4d8e-11e5-9761-22000bd9028a','101e7dcd-4c81-11e5-9ca0-22000bd8028c','0'),
 ('da5fc4eb-4d8e-11e5-9761-22000bd9028a','101e7dcd-4c81-11e5-9ca0-22000bd8028c','0'),
 ('da5fbabe-4d8e-11e5-9761-22000bd9028a','101e7dcd-4c81-11e5-9ca0-22000bd8028c','0'),
 ('da5fb087-4d8e-11e5-9761-22000bd9028a','101e7dcd-4c81-11e5-9ca0-22000bd8028c','0'),
 ('da5fa616-4d8e-11e5-9761-22000bd9028a','101e7dcd-4c81-11e5-9ca0-22000bd8028c','0'),
 ('da5f99bf-4d8e-11e5-9761-22000bd9028a','101e7dcd-4c81-11e5-9ca0-22000bd8028c','0'),
 ('da5f8f0f-4d8e-11e5-9761-22000bd9028a','101e7dcd-4c81-11e5-9ca0-22000bd8028c','0'),
 ('da5f5e2e-4d8e-11e5-9761-22000bd9028a','101e7dcd-4c81-11e5-9ca0-22000bd8028c','0'),
 ('da5f52e3-4d8e-11e5-9761-22000bd9028a','101e7d 

 *** (1) WAITING FOR THIS LOCK TO BE GRANTED: RECORD LOCKS space id 26232190 page no 5961 n bits 160 index `PRIMARY` of table `provalant101_mxradon`.`Record_Base` trx id 1787379600 lock_mode X locks gap before rec insert intention waiting Record lock, heap no 29 PHYSICAL RECORD: n_fields 5; compact format; info bits 0  0: len 30; hex 65376566306364332d353039352d313165352d393736312d323230303062; asc e7ef0cd3-5095-11e5-9761-22000b; (total 36 bytes);  1: len 30; hex 31303165376463642d346338312d313165352d396361302d323230303062; asc 101e7dcd-4c81-11e5-9ca0-22000b; (total 36 bytes);  2: len 6; hex 00006a893f90; asc   j ? ;;  3: len 7; hex b40001a7c3290f; asc      ) ;;  4: len 4; hex 80000000; asc     ;;  *** 

 (2) TRANSACTION: TRANSACTION 1787379848, ACTIVE 1 sec inserting mysql tables in use 1, locked 1 1030 lock struct(s), heap size 112168, 5801 row lock(s), undo log entries 2639 MySQL thread id 537467, OS thread handle 0x2b70324de700, query id 578563042 127.0.0.1 192.168.1.195 demoreleaseroot update INSERT INTO Record_Base VALUES 
 ('4849f98e-5094-11e5-9761-22000bd9028a','101e7dcd-4c81-11e5-9ca0-22000bd8028c','0'),
 ('4849ebe5-5094-11e5-9761-22000bd9028a','101e7dcd-4c81-11e5-9ca0-22000bd8028c','0'),
 ('4849c44c-5094-11e5-9761-22000bd9028a','101e7dcd-4c81-11e5-9ca0-22000bd8028c','0'),
 ('4849add7-5094-11e5-9761-22000bd9028a','101e7dcd-4c81-11e5-9ca0-22000bd8028c','0'),
 ('4849a0ef-5094-11e5-9761-22000bd9028a','101e7dcd-4c81-11e5-9ca0-22000bd8028c','0'),
 ('48499430-5094-11e5-9761-22000bd9028a','101e7dcd-4c81-11e5-9ca0-22000bd8028c','0'),
 ('48498752-5094-11e5-9761-22000bd9028a','101e7dcd-4c81-11e5-9ca0-22000bd8028c','0'),
 ('48496d2d-5094-11e5-9761-22000bd9028a','101e7dcd-4c81-11e5-9ca0-22000bd8028c','0'),
 ('4848731e-5094-11e5-9761-22000bd9028a','101e7dcd-4c81-11e5-9ca0-22000bd8028c','0'),
 ('4846784e-5094-11e5-9761-22000bd9028a','101e7d 

 *** (2) HOLDS THE LOCK(S): RECORD LOCKS space id 26232190 page no 5961 n bits 152 index `PRIMARY` of table `provalant101_mxradon`.`Record_Base` trx id 1787379848 lock_mode X locks gap before rec Record lock, heap no 29 PHYSICAL RECORD: n_fields 5; compact format; info bits 0  0: len 30; hex 65376566306364332d353039352d313165352d393736312d323230303062; asc e7ef0cd3-5095-11e5-9761-22000b; (total 36 bytes);  1: len 30; hex 31303165376463642d346338312d313165352d396361302d323230303062; asc 101e7dcd-4c81-11e5-9ca0-22000b; (total 36 bytes);  2: len 6; hex 00006a893f90; asc   j ? ;;  3: len 7; hex b40001a7c3290f; asc      ) ;;  4: len 4; hex 80000000; asc     ;;  

 *** (2) WAITING FOR THIS LOCK TO BE GRANTED: RECORD LOCKS space id 26232190 page no 14639 n bits 192 index `PRIMARY` of table `provalant101_mxradon`.`Record_Base` trx id 1787379848 lock_mode X locks gap before rec insert intention waiting Record lock, heap no 121 PHYSICAL RECORD: n_fields 5; compact format; info bits 0  0: len 30; hex 38393531613333352d353039342d313165352d393736312d323230303062; asc 8951a335-5094-11e5-9761-22000b; (total 36 bytes);  1: len 30; hex 31303165376463642d346338312d313165352d396361302d323230303062; asc 101e7dcd-4c81-11e5-9ca0-22000b; (total 36 bytes);  2: len 6; hex 00006a893f90; asc   j ? ;;  3: len 7; hex b40001a7c71c1c; asc        ;;  4: len 4; hex 80000000; asc     ;; 

 *** WE ROLL BACK TRANSACTION (2) 

解决方案

These kind of deadlocks are known as Gap Locks. I found this post quite helpful.

Additionally, you can read more about gap locking in the Mysql Manual

这篇关于由于多个线程插入,MySQL中出现死锁的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!

查看全文
登录 关闭
扫码关注1秒登录
发送“验证码”获取 | 15天全站免登陆