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.