Deadlock in MySQL due to Insert by multiple threads

Question

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) 

Answer 1

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

Answer 2

Deadlock happened alot in my application at past due to mysql's mechanisms. I solved it with two ways. First, I put the batch jobs affecting same table in same thread and run them sequentially, second I put a try-catch block around query executions to catch deadlock error and let it try the same query execution 5+ times including a sleep function between tries.

Answer 3

I had this same issue pop up to me, @Aashish 's answer explained it to me, so if you want to understand why this worked for me you need to read his answer.

The scenario I had:

I had a UNIQUE Index on columns Date and idSomething (The order is important, Date was 1st and idSomething second)

I had a pool of 12 threads inserting the same range of dates (3 days) for every idSomething (About 1500 different idSomethings).

The fix:

Changing the UNIQUE index to be on column idSomething (1st) and Date (2nd). Then the process instead of processing "Somethings" in a orderly manner, I added a shuffle to the order, so it would be less likely that idSomething I and I+1 would be pooled at the same time. That way the Gap Locks didn't overlap eachother and the deadlocks disapeared.