Deadlock in MySQL due to Insert by multiple threads

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.

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.

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