MySQL deadlock due to multiple thread insertion

Question

I have a multi-threaded application that tries to INSERT write several batches to the table. Each thread processes one batch. From time to time I get a Deadlock error, the next one is a trace.

The table I'm trying to insert a record in is as follows:

RecordBase (Col1, Col2, Col3)

Col1 and Col2 together form a composite primary key.

I used to think that this could be due to index-record locking, but tracing clearly shows that statements that lock each other do not have any duplicate records. So why does this cause a dead end?

 ------------------------ 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) 

Answer 1

These dead ends are known as blocking blocks. I found this post very helpful.

You can also learn more about blocking spaces in the Mysql manual.

Answer 2

A dead end has occurred in my application in the past due to mysql mechanisms. I solved this in two ways. Firstly, I placed batch jobs affecting the same table in the same thread and ran them sequentially, and secondly, I placed a try-catch block around query execution to catch a deadlock and let it try one and the same query execution 5+ times, including the sleep function between attempts.

Answer 3

I had the same problem as me, @Aashish answer explained this to me, so if you want to understand why this worked for me, you need to read his answer.

The script that I had:

I had a UNIQUE index in Date and idSomething columns (order is important, date was first and idSomething second)

I had a pool of 12 threads inserting the same date range (3 days) for each idSomething (about 1500 different idSomethings).

Correction:

Change the UNIQUE index to the idSomething (1st) and Date (2nd) columns. Then the process, instead of processing "Somethings" in an orderly way, added order to the order, so it would be less likely that idSomething me and I + 1 would be merged at the same time. Thus, the locks did not overlap with each other, and the dead ends were devoid of.