多线程大规模删除查询和批量插入批处理上的SQL死锁 [英] SQL deadlock on multithreaded massive delete query and bulk insert batches
本文介绍了多线程大规模删除查询和批量插入批处理上的SQL死锁的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！
问题描述
我在.NET中有一个批处理，可以读取许多文件并将其内容存储到具有以下结构的单个MySQL InnoDB表中:
I have a batch in .NET that reads a lot of files and stores their content into a single MySQL InnoDB table with the following structure:
`id` int(11) NOT NULL AUTO_INCREMENT,
`Source` varchar(2) NOT NULL,
`Period` char(1) NOT NULL,
`idItem` int(11) NOT NULL,
`StartDate` datetime NOT NULL,
`MoreData` varchar(255)
PRIMARY KEY (`id`),
UNIQUE KEY `Combination` (`Source`,`Period`,`idItem`,`StartDate`),
哪里
- idItem是外键
- 来源，期间和idItem是索引
- 组合是唯一索引.
要执行表更新，我需要执行一个包含两个步骤的事务:
To perform the table update, I then have a transaction with two steps:
- 在我的新数据集的时间间隔内删除所有具有StartDate的旧行.
-
使用单个INSERT语句插入新行(最多10,000行).
- Delete all old rows that have a StartDate within the time interval of my newer dataset.
Insert new rows with a single INSERT statement (up to 10,000 rows).
开始交易；
从数据中删除源= @源AND周期= @期间AND idItem = @idItem AND StartDate> = @FirstDate AND StartDate< = @LastDate;
DELETE FROM data WHERE Source = @Source AND Period = @Period AND idItem = @idItem AND StartDate >= @FirstDate AND StartDate <= @LastDate;
插入数据(..，.......)VALUES(..，.......)(..，......))(..， ..，..，..)(..，..，..，..);
INSERT INTO data(..,..,..,..) VALUES(..,..,..,..)(..,..,..,..)(..,..,..,..)(..,..,..,..);
COMMIT;
问题在于，即使每个线程只能更新一组不同的( Source，Period ，idItem )(不重叠)，出现1213异常:试图获取锁时发现死锁；尝试重新启动.
The problem is that when running this from multiple threads (works fine with 1 thread, breaks with 2 or more threads) at the same time, even if each thread can only update a different set of (Source,Period,idItem) (no overlapping), I get a 1213 exception: Deadlock found when trying to get lock; try restarting.
此问题与此处描述的问题非常相似:删除时SQL死锁然后批量插入
This problem is very similar to the one described here: SQL deadlock on delete then bulk insert
您建议采取什么措施来防止这种僵局?我在此处尝试了所有建议，但没有说服力结果.谢谢！
What would you suggest to prevent this kind of deadlock? I have tried everything suggested here but with no convincing results. Thanks!
附件:
------------------------
LATEST DETECTED DEADLOCK
------------------------
110911 12:00:45
*** (1) TRANSACTION:
TRANSACTION 5167, ACTIVE 3 sec starting index read
mysql tables in use 1, locked 1
LOCK WAIT 4 lock struct(s), heap size 1248, 2 row lock(s), undo log entries 1
MySQL thread id 225, query id 86933 192.168.1.1 root updating
DELETE FROM data WHERE Source = ''mysource'' AND Period = ''D'' AND idItem = 17 AND StartDate >= ''2009-07-22 00:00:00'' AND StartDate <= ''2011-08-29 00:00:00'' ORDER BY StartDate
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 61 page no 389 n bits 344 index `SourcePeriodItemStartDate` of table `crdb`.`data` trx id 5167 lock_mode X waiting
Record lock, heap no 229 PHYSICAL RECORD: n_fields 5; compact format; info bits 0
0: len 2; hex 4242; asc mysource;;
1: len 1; hex 44; asc D;;
2: len 4; hex 80000011; asc ;;
3: len 8; hex 80001245bc591c80; asc E Y ;;
4: len 4; hex 8015b9fd; asc ;;
*** (2) TRANSACTION:
TRANSACTION 5163, ACTIVE 8 sec inserting, thread declared inside InnoDB 198
mysql tables in use 1, locked 1
33 lock struct(s), heap size 6960, 2419 row lock(s), undo log entries 1625
MySQL thread id 224, query id 86924 192.168.1.1 root update
insert data(...)
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 61 page no 389 n bits 344 index `SourcePeriodItemStartDate` of table `crdb`.`data` trx id 5163 lock_mode X
Record lock, heap no 2 PHYSICAL RECORD: n_fields 5; compact format; info bits 32
0: len 2; hex 4242; asc mysource;;
1: len 1; hex 44; asc D;;
2: len 4; hex 80000010; asc ;;
3: len 8; hex 8000124821527f80; asc H!R ;;
4: len 4; hex 8015c121; asc !;;
Record lock, heap no 3 PHYSICAL RECORD: n_fields 5; compact format; info bits 32
0: len 2; hex 4242; asc mysource;;
1: len 1; hex 44; asc D;;
2: len 4; hex 80000010; asc ;;
3: len 8; hex 800012482161c1c0; asc H!a ;;
4: len 4; hex 8015c122; asc ";;
Record lock, heap no 4 PHYSICAL RECORD: n_fields 5; compact format; info bits 32
0: len 2; hex 4242; asc mysource;;
1: len 1; hex 44; asc D;;
2: len 4; hex 80000010; asc ;;
3: len 8; hex 8000124821710400; asc H!q ;;
4: len 4; hex 8015c123; asc #;;
Record lock, heap no 5 PHYSICAL RECORD: n_fields 5; compact format; info bits 32
0: len 2; hex 4242; asc mysource;;
1: len 1; hex 44; asc D;;
2: len 4; hex 80000010; asc ;;
3: len 8; hex 80001248219ecac0; asc H! ;;
4: len 4; hex 8015c124; asc $;;
Record lock, heap no 6 PHYSICAL RECORD: n_fields 5; compact format; info bits 32
0: len 2; hex 4242; asc mysource;;
1: len 1; hex 44; asc D;;
2: len 4; hex 80000010; asc ;;
3: len 8; hex 8000124821ae0d00; asc H! ;;
4: len 4; hex 8015c125; asc %;;
Record lock, heap no 7 PHYSICAL RECORD: n_fields 5; compact format; info bits 32
0: len 2; hex 4242; asc mysource;;
1: len 1; hex 44; asc D;;
2: len 4; hex 80000010; asc ;;
3: len 8; hex 8000124821bd4f40; asc H! O@;;
4: len 4; hex 8015c126; asc &;;
Record lock, heap no 8 PHYSICAL RECORD: n_fields 5; compact format; info bits 32
0: len 2; hex 4242; asc mysource;;
1: len 1; hex 44; asc D;;
2: len 4; hex 80000010; asc ;;
3: len 8; hex 8000124821cc9180; asc H! ;;
4: len 4; hex 8015c127; asc '';;
Record lock, heap no 9 PHYSICAL RECORD: n_fields 5; compact format; info bits 32
0: len 2; hex 4242; asc mysource;;
1: len 1; hex 44; asc D;;
2: len 4; hex 80000010; asc ;;
3: len 8; hex 8000124821dbd3c0; asc H! ;;
4: len 4; hex 8015c128; asc (;;
Record lock, heap no 10 PHYSICAL RECORD: n_fields 5; compact format; info bits 32
0: len 2; hex 4242; asc mysource;;
1: len 1; hex 44; asc D;;
2: len 4; hex 80000010; asc ;;
3: len 8; hex 8000124822099a80; asc H" ;;
4: len 4; hex 8015c129; asc );;
Record lock, heap no 11 PHYSICAL RECORD: n_fields 5; compact format; info bits 32
0: len 2; hex 4242; asc mysource;;
1: len 1; hex 44; asc D;;
2: len 4; hex 80000010; asc ;;
3: len 8; hex 800012482218dcc0; asc H" ;;
4: len 4; hex 8015c12a; asc *;;
Record lock, heap no 12 PHYSICAL RECORD: n_fields 5; compact format; info bits 32
0: len 2; hex 4242; asc mysource;;
1: len 1; hex 44; asc D;;
2: len 4; hex 80000010; asc ;;
3: len 8; hex 8000124822281f00; asc H"( ;;
4: len 4; hex 8015c12b; asc +;;
Record lock, heap no 13 PHYSICAL RECORD: n_fields 5; compact format; info bits 32
0: len 2; hex 4242; asc mysource;;
1: len 1; hex 44; asc D;;
2: len 4; hex 80000010; asc ;;
3: len 8; hex 8000124822376140; asc H"7a@;;
4: len 4; hex 8015c12c; asc ,;;
Record lock, heap no 14 PHYSICAL RECORD: n_fields 5; compact format; info bits 32
0: len 2; hex 4242; asc mysource;;
1: len 1; hex 44; asc D;;
2: len 4; hex 80000010; asc ;;
3: len 8; hex 800012482246a380; asc H"F ;;
4: len 4; hex 8015c12d; asc -;;
Record lock, heap no 15 PHYSICAL RECORD: n_fields 5; compact format; info bits 32
0: len 2; hex 4242; asc mysource;;
1: len 1; hex 44; asc D;;
2: len 4; hex 80000010; asc ;;
3: len 8; hex 8000124822746a40; asc H"tj@;;
4: len 4; hex 8015c12e; asc .;;
Record lock, heap no 16 PHYSICAL RECORD: n_fields 5; compact format; info bits 32
0: len 2; hex 4242; asc mysource;;
1: len 1; hex 44; asc D;;
2: len 4; hex 80000010; asc ;;
3: len 8; hex 800012482283ac80; asc H" ;;
4: len 4; hex 8015c12f; asc /;;
Record lock, heap no 17 PHYSICAL RECORD: n_fields 5; compact format; info bits 32
0: len 2; hex 4242; asc mysource;;
1: len 1; hex 44; asc D;;
2: len 4; hex 80000010; asc ;;
3: len 8; hex 800012482292eec0; asc H" ;;
4: len 4; hex 8015c130; asc 0;;
Record lock, heap no 18 PHYSICAL RECORD: n_fields 5; compact format; info bits 32
0: len 2; hex 4242; asc mysource;;
1: len 1; hex 44; asc D;;
2: len 4; hex 80000010; asc ;;
3: len 8; hex 8000124822a23100; asc H" 1 ;;
4: len 4; hex 8015c131; asc 1;;
Record lock, heap no 19 PHYSICAL RECORD: n_fields 5; compact format; info bits 32
0: len 2; hex 4242; asc mysource;;
1: len 1; hex 44; asc D;;
2: len 4; hex 80000010; asc ;;
3: len 8; hex 8000124822b17340; asc H" s@;;
4: len 4; hex 8015c132; asc 2;;
Record lock, heap no 20 PHYSICAL RECORD: n_fields 5; compact format; info bits 32
0: len 2; hex 4242; asc mysource;;
1: len 1; hex 44; asc D;;
2: len 4; hex 80000010; asc ;;
3: len 8; hex 8000124826fc1540; asc H& @;;
4: len 4; hex 8015c133; asc 3;;
Record lock, heap no 21 PHYSICAL RECORD: n_fields 5; compact format; info bits 32
0: len 2; hex 4242; asc mysource;;
1: len 1; hex 44; asc D;;
2: len 4; hex 80000010; asc ;;
3: len 8; hex 80001248270b5780; asc H'' W ;;
4: len 4; hex 8015c134; asc 4;;
Record lock, heap no 22 PHYSICAL RECORD: n_fields 5; compact format; info bits 32
0: len 2; hex 4242; asc mysource;;
1: len 1; hex 44; asc D;;
2: len 4; hex 80000010; asc ;;
3: len 8; hex 80001248271a99c0; asc H'' ;;
4: len 4; hex 8015c135; asc 5;;
Record lock, heap no 23 PHYSICAL RECORD: n_fields 5; compact format; info bits 32
0: len 2; hex 4242; asc mysource;;
1: len 1; hex 44; asc D;;
2: len 4; hex 80000010; asc ;;
3: len 8; hex 800012482729dc00; asc H'') ;;
4: len 4; hex 8015c136; asc 6;;
Record lock, heap no 24 PHYSICAL RECORD: n_fields 5; compact format; info bits 32
0: len 2; hex 4242; asc mysource;;
1: len 1; hex 44; asc D;;
2: len 4; hex 80000010; asc ;;
3: len 8; hex 8000124827391e40; asc H''9 @;;
4: len 4; hex 8015c137; asc 7;;
Record lock, heap no 25 PHYSICAL RECORD: n_fields 5; compact format; info bits 32
0: len 2; hex 4242; asc mysource;;
1: len 1; hex 44; asc D;;
2: len 4; hex 80000010; asc ;;
3: len 8; hex 800012482766e500; asc H''f ;;
4: len 4; hex 8015c138; asc 8;;
Record lock, heap no 26 PHYSICAL RECORD: n_fields 5; compact format; info bits 32
0: len 2; hex 4242; asc mysource;;
1: len 1; hex 44; asc D;;
2: len 4; hex 80000010; asc ;;
3: len 8; hex 8000124827762740; asc H''v''@;;
4: len 4; hex 8015c139; asc 9;;
Record lock, heap no 27 PHYSICAL RECORD: n_fields 5; compact format; info bits 32
0: len 2; hex 4242; asc mysource;;
1: len 1; hex 44; asc D;;
2: len 4; hex 80000010; asc ;;
3: len 8; hex 8000124827856980; asc H'' i ;;
4: len 4; hex 8015c13a; asc :;;
Record lock, heap no 28 PHYSICAL RECORD: n_fields 5; compact format; info bits 32
0: len 2; hex 4242; asc mysource;;
1: len 1; hex 44; asc D;;
2: len 4; hex 80000010; asc ;;
3: len 8; hex 800012482794abc0; asc H'' ;;
4: len 4; hex 8015c13b; asc ;;;
Record lock, heap no 29 PHYSICAL RECORD: n_fields 5; compact format; info bits 32
0: len 2; hex 4242; asc mysource;;
1: len 1; hex 44; asc D;;
2: len 4; hex 80000010; asc ;;
3: len 8; hex 8000124827a3ee00; asc H'' ;;
4: len 4; hex 8015c13c; asc <;;
Record lock, heap no 30 PHYSICAL RECORD: n_fields 5; compact format; info bits 32
0: len 2; hex 4242; asc mysource;;
1: len 1; hex 44; asc D;;
2: len 4; hex 80000010; asc ;;
3: len 8; hex 8000124827d1b4c0; asc H'' ;;
4: len 4; hex 8015c13d; asc =;;
Record lock, heap no 31 PHYSICAL RECORD: n_fields 5; compact format; info bits 32
0: len 2; hex 4242; asc mysource;;
1: len 1; hex 44; asc D;;
2: len 4; hex 80000010; asc ;;
3: len 8; hex 8000124827e0f700; asc H'' ;;
4: len 4; hex 8015c13e; asc >;;
Record lock, heap no 32 PHYSICAL RECORD: n_fields 5; compact format; info bits 32
0: len 2; hex 4242; asc mysource;;
1: len 1; hex 44; asc D;;
2: len 4; hex 80000010; asc ;;
3: len 8; hex 8000124827f03940; asc H'' 9@;;
4: len 4; hex 8015c13f; asc ?;;
Record lock, heap no 33 PHYSICAL RECORD: n_fields 5; compact format; info bits 32
0: len 2; hex 4242; asc mysource;;
1: len 1; hex 44; asc D;;
2: len 4; hex 80000010; asc ;;
3: len 8; hex 8000124827ff7b80; asc H'' { ;;
4: len 4; hex 8015c140; asc @;;
Record lock, heap no 34 PHYSICAL RECORD: n_fields 5; compact format; info bits 32
0: len 2; hex 4242; asc mysource;;
1: len 1; hex 44; asc D;;
2: len 4; hex 80000010; asc ;;
3: len 8; hex 80001248280ebdc0; asc H( ;;
4: len 4; hex 8015c141; asc A;;
Record lock, heap no 35 PHYSICAL RECORD: n_fields 5; compact format; info bits 32
0: len 2; hex 4242; asc mysource;;
1: len 1; hex 44; asc D;;
2: len 4; hex 80000010; asc ;;
3: len 8; hex 80001248283c8480; asc H(< ;;
4: len 4; hex 8015c142; asc B;;
Record lock, heap no 36 PHYSICAL RECORD: n_fields 5; compact format; info bits 32
0: len 2; hex 4242; asc mysource;;
1: len 1; hex 44; asc D;;
2: len 4; hex 80000010; asc ;;
3: len 8; hex 80001248284bc6c0; asc H(K ;;
4: len 4; hex 8015c143; asc C;;
Record lock, heap no 37 PHYSICAL RECORD: n_fields 5; compact format; info bits 32
0: len 2; hex 4242; asc mysource;;
1: len 1; hex 44; asc D;;
2: len 4; hex 80000010; asc ;;
3: len 8; hex 80001248285b0900; asc H([ ;;
4: len 4; hex 8015c144; asc D;;
Record lock, heap no 38 PHYSICAL RECORD: n_fields 5; compact format; info bits 32
0: len 2; hex 4242; asc mysource;;
1: len 1; hex 44; asc D;;
2: len 4; hex 80000010; asc ;;
3: len 8; hex 8000124828798d80; asc H(y ;;
4: len 4; hex 8015c145; asc E;;
Record lock, heap no 39 PHYSICAL RECORD: n_fields 5; compact format; info bits 32
0: len 2; hex 4242; asc mysource;;
1: len 1; hex 44; asc D;;
2: len 4; hex 80000010; asc ;;
3: len 8; hex 8000124828a75440; asc H( T@;;
4: len 4; hex 8015c146; asc F;;
Record lock, heap no 40 PHYSICAL RECORD: n_fields 5; compact format; info bits 32
0: len 2; hex 4242; asc mysource;;
1: len 1; hex 44; asc D;;
2: len 4; hex 80000010; asc ;;
3: len 8; hex 8000124828b69680; asc H( ;;
4: len 4; hex 8015c147; asc G;;
Record lock, heap no 41 PHYSICAL RECORD: n_fields 5; compact format; info bits 32
0: len 2; hex 4242; asc mysource;;
1: len 1; hex 44; asc D;;
2: len 4; hex 80000010; asc ;;
3: len 8; hex 800012482cf1f640; asc H, @;;
4: len 4; hex 8015c148; asc H;;
Record lock, heap no 42 PHYSICAL RECORD: n_fields 5; compact format; info bits 32
0: len 2; hex 4242; asc mysource;;
1: len 1; hex 44; asc D;;
2: len 4; hex 80000010; asc ;;
3: len 8; hex 800012482d013880; asc H- 8 ;;
4: len 4; hex 8015c149; asc I;;
Record lock, heap no 43 PHYSICAL RECORD: n_fields 5; compact format; info bits 32
0: len 2; hex 4242; asc mysource;;
1: len 1; hex 44; asc D;;
2: len 4; hex 80000010; asc ;;
3: len 8; hex 800012482d107ac0; asc H- z ;;
4: len 4; hex 8015c14a; asc J;;
Record lock, heap no 44 PHYSICAL RECORD: n_fields 5; compact format; info bits 32
0: len 2; hex 4242; asc mysource;;
1: len 1; hex 44; asc D;;
2: len 4; hex 80000010; asc ;;
3: len 8; hex 800012482d3e4180; asc H->A ;;
4: len 4; hex 8015c14b; asc K;;
Record lock, heap no 45 PHYSICAL RECORD: n_fields 5; compact format; info bits 32
0: len 2; hex 4242; asc mysource;;
1: len 1; hex 44; asc D;;
2: len 4; hex 80000010; asc ;;
3: len 8; hex 800012482d4d83c0; asc H-M ;;
4: len 4; hex 8015c14c; asc L;;
Record lock, heap no 46 PHYSICAL RECORD: n_fields 5; compact format; info bits 32
0: len 2; hex 4242; asc mysource;;
1: len 1; hex 44; asc D;;
2: len 4; hex 80000010; asc ;;
3: len 8; hex 800012482d5cc600; asc H-\ ;;
4: len 4; hex 8015c14d; asc M;;
Record lock, heap no 47 PHYSICAL RECORD: n_fields 5; compact format; info bits 32
0: len 2; hex 4242; asc mysource;;
1: len 1; hex 44; asc D;;
2: len 4; hex 80000010; asc ;;
3: len 8; hex 800012482d6c0840; asc H-l @;;
4: len 4; hex 8015c14e; asc N;;
Record lock, heap no 48 PHYSICAL RECORD: n_fields 5; compact format; info bits 32
0: len 2; hex 4242; asc mysource;;
1: len 1; hex 44; asc D;;
2: len 4; hex 80000010; asc ;;
3: len 8; hex 800012482d7b4a80; asc H-{J ;;
4: len 4; hex 8015c14f; asc O;;
Record lock, heap no 49 PHYSICAL RECORD: n_fields 5; compact format; info bits 32
0: len 2; hex 4242; asc mysource;;
1: len 1; hex 44; asc D;;
2: len 4; hex 80000010; asc ;;
3: len 8; hex 800012482da91140; asc H- @;;
4: len 4; hex 8015c150; asc P;;
Record lock, heap no 50 PHYSICAL RECORD: n_fields 5; compact format; info bits 32
0: len 2; hex 4242; asc mysource;;
1: len 1; hex 44; asc D;;
2: len 4; hex 80000010; asc ;;
3: len 8; hex 800012482db85380; asc H- S ;;
4: len 4; hex 8015c151; asc Q;;
Record lock, heap no 51 PHYSICAL RECORD: n_fields 5; compact format; info bits 32
0: len 2; hex 4242; asc mysource;;
1: len 1; hex 44; asc D;;
2: len 4; hex 80000010; asc ;;
3: len 8; hex 800012482dc795c0; asc H- ;;
4: len 4; hex 8015c152; asc R;;
(around 250 more record locks like this)
*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 61 page no 389 n bits 544 index `SourcePeriodItemStartDate` of table `crdb`.`data` trx id 5163 lock_mode X locks gap before rec insert intention waiting
Record lock, heap no 229 PHYSICAL RECORD: n_fields 5; compact format; info bits 0
0: len 2; hex 4242; asc mysource;;
1: len 1; hex 44; asc D;;
2: len 4; hex 80000011; asc ;;
3: len 8; hex 80001245bc591c80; asc E Y ;;
4: len 4; hex 8015b9fd; asc ;;
*** WE ROLL BACK TRANSACTION (1)
推荐答案
将ORDER BY StartDate添加到删除项. (因此行将按特定顺序锁定)
Add ORDER BY StartDate to the delete. (so the rows get locked in a specific order)
这篇关于多线程大规模删除查询和批量插入批处理上的SQL死锁的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
查看全文
