I am experiencing a deadlock scenario, and it's possible that I'm just not reading this correctly but here are the details, my interpretation is at the bottom.
Transaction 1: An ETL that is deleting records in batches.
Transaction 2: A store front.
The offending table is as such:
CREATE TABLE items ( id BIGINT PRIMARY KEY, secondary_key BIGINT UNIQUE )
T1 is attempting to delete records by the
secondary_key, meanwhile T2 is attempting to update the
secondary_key on an existing record
So T1 is doing:
DELETE FROM items where secondary_key IN (1,2,3,4,5,6);
And T2 is doing:
SET TRANSACTION ISOLATION LEVEL READ COMMITTED; -- This is set at the beginning of a larger transaction UPDATE items SET secondary_key = 7 where id = 4;
The deadlock log that I the get is as follows:
*** (1) TRANSACTION: TRANSACTION 42065889, ACTIVE 25 sec fetching rows mysql tables in use 2, locked 2 LOCK WAIT 62416 lock struct(s), heap size 7577808, 9631608 row lock(s) MySQL thread id 177980, OS thread handle 47144643110656, query id 45451329 preparing DELETE FROM items WHERE secondary_id IN ( SELECT li.id FROM staging.legacy_items li WHERE change_operation = 'D' AND li.id BETWEEN 9082453 AND 29059282 ) AND legacy_id IS NOT NULL *** (1) WAITING FOR THIS LOCK TO BE GRANTED: RECORD LOCKS space id 878323 page no 160719 n bits 96 index PRIMARY of table `items` trx id 42065889 lock_mode X waiting
*** (2) TRANSACTION: TRANSACTION 42065914, ACTIVE 1 sec starting index read mysql tables in use 1, locked 1 34 lock struct(s), heap size 3520, 19 row lock(s), undo log entries 10 MySQL thread id 177524, OS thread handle 47137066059520, query id 45451721 updating UPDATE `items` SET `items`.`secondary_id` = 29059300 WHERE `items`.`id` = 15558171 *** (2) HOLDS THE LOCK(S): RECORD LOCKS space id 878323 page no 160719 n bits 96 index PRIMARY of table `items` trx id 42065914 lock mode S locks rec but not gap *** (2) WAITING FOR THIS LOCK TO BE GRANTED: RECORD LOCKS space id 878323 page no 160719 n bits 96 index PRIMARY of table `items` trx id 42065914 lock_mode X locks rec but not gap waiting
*** WE ROLL BACK TRANSACTION (2)
SO.. to me it looks like:
- Transaction 2 gets a shared lock on single record
- Transaction 1 takes an exclusive lock on range of records
- Transaction 2 tries to take an exclusive lock on the single record that it already has a shared lock for.
I can't understand why Transaction 2 wouldn't be able to get an exclusive lock on the record that it already has a shared lock for.
Am I interpreting this completely wrong?