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.