0

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!

2
  • Two unique keys? Can you get rid of one? Deadlocks take both into account. – Rick James Jan 9 '20 at 23:55
  • In a pinch I could probably remove the unique constraint on the secondary_key. We're in a transition period where we have to keep two systems in sync with one another until the old one can be shut down. So that unique constraint was more of a sanity check to ensure data integrity during the transition, since it is the PK from the old system. – Gangie Jan 10 '20 at 0:21
0

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.

2
  • Thanks for the suggestion! Much appreciated. I'm still really interested in how this causes a deadlock at all... To me it seems intuitive that if transaction 2 has a shared lock on a record, it should be able to later on in the transaction take out an exclusive lock on that record despite what other transaction may be queued up for an exclusive lock. I'm failing to see where this is a deadlock scenario and not just two transactions waiting on one another. – Gangie Jan 10 '20 at 14:01
  • @Gangie - Oops, I forgot to suggest FOR UPDATE. – Rick James Jan 10 '20 at 16:16

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.