InnoDB deadlock with INSERT .. SELECT

Question

I am trying to understand why a deadlock occurs when two workers in parallel are running INSERT .. SELECT queries on a table which has both 1) auto incremented primary key, 2) a unique index on multiple columns first of which is a date column. Workers normally work with rows from different dates. The unique index column value ranges should not overlap (they were overlapping before, but I changed the index to have date column as the first one).

SHOW ENGINE INNODB STATUS\G shows that 1st transaction (active for 20secs) is waiting for unique_rows lock (mode S), while the 2nd transaction (active for 11secs) holds unique_rows lock (mode X) and is waiting for auto-inc table lock.

How far I understand this: when 1st transaction starts, it acquires auto-inc table lock (I use the default auto-inc lock mode = 1), then it acquires some gap locks on the unique_rows index. The 2nd transaction could be acquiring gap locks on the unique_rows index, but they should not conflict with the locks acquired by 1st transaction.

Having written the question down, I suspect my assumptions about gap locks are wrong.

Another thing is I always have a DELETE FROM WHERE query in the same transaction just before INSERT .. SELECT, but that DELETE query has never been mentioned in deadlock logs.

The whole process works like this: 1) delete a date range from the destination table, 2) insert a date range in the destination table by selecting data from some other table.

Answer 1

the deadlock message sometimes will not show the full picture. It could be some other query is holding the lock which is not any of those two showed in the log. As you said, you have delete query in the beginning, that properly would be the case.

Here is a way I normally use to troubleshoot the deadlocks when you can not get all the info from the log.

mysql> set global log_output = 'table';
mysql> set global general_log=on;

Then you reproduce the problem, when the deadlock happens, turn off the general log. Find those two queries in the mysql.general_log, then use thread_id to find the start and end of the transaction. You will have all the queries in the transaction, then you can go through the logic, to find out which is the query or logic caused the problem.