MySQL – How to a deadlock b caused by the same transaction

deadlockMySQL

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.

The players:

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:

Transaction 1

*** (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

Transaction 2

*** (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

Result

*** WE ROLL BACK TRANSACTION (2)

SO.. to me it looks like:

  1. Transaction 2 gets a shared lock on single record
  2. Transaction 1 takes an exclusive lock on range of records
  3. 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?

Thanks!

Best Answer

I suggest you add code to make the product more robust. Check for deadlocks after every statement. When you encounter such, replay the statement(s) in the transaction.

That may be cheaper than figuring out all the possible causes of deadlocks (you have shown one, but there could be more). Later you can worry about the extra unique key. But leave the checks for deadlocks in place even if they never happen again.

Adding to FOR UPDATE to the SELECT.

Related Question