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) 

Formalism

Deadlocks occur when more transactions hold requests for a lock. In your case, you have multiple threads hitting the same table so it's expected that the deadlock will occur. A deadlock is not reason to be alarmed. InnoDB automatically detects them and throws errors. For us, developers who use MySQL, that means we just have to repeat the query in case of error. In some pseudo code, it would be something like while(true) if(do_query()) break; – Crambo

@Crambo I did the same to make my application work. I would just like to know in detail what is causing this deadlock. – Formalism

The deadlock is caused by your threads accessing the same table at the same time (concurrent access). In order to prevent overwriting the data, MySQL allows sequential access to the table, for write. For a thread or process to be allowed to write to a table, it must acquire a lock. However, when two threads are fighting to acquire a lock - neither will ever acquire it - which puts is in an infinite loop. To break from this loop, MySQL throws errors and calls this a "deadlock". TL;DR: your concurrent insert from multiple threads to a table is causing deadlocks. (this is simplified explanation) – Crambo

@Crambo I don't think this is the correct explanation. Innodb has row-level locking and there is nothing like sequential access unless the row being accessed is same. In case of Insert, the records are anyways unique so row-level locks may not cause deadlocks. I did a bit more research and found this article explaining the issue perfectly – Formalism

Ok, if you're fine with another explanation then I won't butt in. When you write, you can't access the table, for write, concurrently - two things can't write to it at the same time. You also can't lock a row if there is no row to be locked. You also can't calculate the auto_increment safely unless you sequentially access the counter by placing a lock, incrementing, releasing and letting the waiting thread access it. However, since you found the explanation then I assume everything's fine :) – Crambo

@Aashish, on Stack Overflow it's helpful for you to create an answer to your own question when you figure it out. That way the next person can benefit from your wisdom and research. Thanks. – Tseng

I suspect frequency of deadlocks will go down significantly with shorter insertion batches. But so will baseline throughput. – Tseng

@OllieJones : Thanks for the suggestion. I added an answer. – Formalism

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

Formalism

In addition to what is said in the linked post, you can have a deadlock on concurrent inserts without a unique key constraint, just having a combination of auto generated primary key and another index can lead to the issue. See dba.stackexchange.com/questions/86878/… – Try

I've written this article medium.com/@syl.fabre/… to explain how we've dealt with this it in my company – Naos

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.

Linette

Upvoted because of the "including a sleep function between tries" part, I believe it is important and it is often overlooked. – Verjuice

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.

Karlenekarlens

Can you elaborate, can't understand the fix – Andino

Answer 4

The deadlocks happen when one monolithic operation inserts values (A, .., B) while simultaneously another insert values (B, .., A).

The simplest solution, is to sort your values by the unique key before inserting, so that all the inserts all have A before B in the list of values.

PS: because of gap locks, deadlocks can happen even if the inserts don't have the same values. Nevertheless, sorting the values will solve the problems.

Patio