Question 415246 in Mysql Question posted in
The official MySQL documentation can be found here.

Mysql – InnoDB deadlock caused by two transactions holding the same exclusive lock

I am running MySql v8.0 with InnoDB. I have a simple transaction that has a SELECT (not a SELECT ... FOR UPDATE) followed by an INSERT. I’m getting frequent deadlocks when many DB inserts are happening simultaneously. All inserts use the same code, so should have the same order of locks acquired. The primary index for the table is based on id, which is autoincrementing. But the deadlock appears to be happening because of simultaneous locks on the secondary index. The output of SHOW ENGINE INNODB STATUS is:

------------------------
LATEST DETECTED DEADLOCK
------------------------
2024-11-11 05:43:03 70378481278848
*** (1) TRANSACTION:
TRANSACTION 75372951333, ACTIVE 0 sec inserting
mysql tables in use 1, locked 1
LOCK WAIT 3 lock struct(s), heap size 1128, 2 row lock(s), undo log entries 1
MySQL thread id 10225859, OS thread handle 70369406140288, query id 1436757204 172.22.120.99 _99952768SM1GL7w update
insert into shoes
            ( account_id
            , premium
            , name
            , state
            )
          values
            ( 22496527
            , 0
            , 'boots'
            , 'active'
*** (1) HOLDS THE LOCK(S):
RECORD LOCKS space id 1087 page no 634440 n bits 432 index index_shoes_on_account_id_and_premium_and_name of table `staging`.`shoes` trx id 75372951333 lock_mode X
Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0
 0: len 8; hex 73757072656d756d; asc supremum;;
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 1087 page no 634440 n bits 432 index index_shoes_on_account_id_and_premium_and_name of table `staging`.`shoes` trx id 75372951333 lock_mode X insert intention waiting
Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0
 0: len 8; hex 73757072656d756d; asc supremum;;
*** (2) TRANSACTION:
TRANSACTION 75372951336, ACTIVE 0 sec inserting
mysql tables in use 1, locked 1
LOCK WAIT 3 lock struct(s), heap size 1128, 2 row lock(s), undo log entries 1
MySQL thread id 10225858, OS thread handle 70373717872512, query id 1436757206 172.22.120.99 _99952768SM1GL7w update
insert into shoes
            ( account_id
            , premium
            , name
            , state
            )
          values
            ( 22496526
            , 0
            , 'boots'
            , 'active'
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 1087 page no 634440 n bits 432 index index_shoes_on_account_id_and_premium_and_name of table `staging`.`shoes` trx id 75372951336 lock_mode X
Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0
 0: len 8; hex 73757072656d756d; asc supremum;;
*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 1087 page no 634440 n bits 432 index index_shoes_on_account_id_and_premium_and_name of table `staging`.`shoes` trx id 75372951336 lock_mode X insert intention waiting
Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0
 0: len 8; hex 73757072656d756d; asc supremum;;
*** WE ROLL BACK TRANSACTION (2)

How is it that the two transactions both hold an exclusive lock on the record that have the same space id? And because the rows being inserted are different and will have a different index, why would this be a problem? Is it something to do with the fact that both of these transactions would result in an index that is greater than the current greatest index? Ie, both would result in an update to the supremum?

Because both transactions are inserting different rows and will create distinct index records, I was expecting both transactions to proceed without causing a deadlock.

Edit: the output of SHOW CREATE TABLE shoes is:

CREATE TABLE `shoes` (
  `id` int NOT NULL AUTO_INCREMENT,
  `account_id` int NOT NULL,
  `premium` tinyint(1) DEFAULT '0',
  `name` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL,
  `state` varchar(32) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `index_shoes_on_account_id_and_premium_and_name` (`account_id`,`premium`,`name`),
  KEY `shoes_account_id_name_idx2` (`account_id`,`name`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci ROW_FORMAT=DYNAMIC;

Edit: This is a Scala codebase that uses Doobie to construct the SQL queries, and there’s a bit of logic between the select + insert in shoes and the insert in kafka_audits, which is a black hole table. Then the Doobie ConnectionIOs are composed together to run in a single transaction. I’ve done my best to show just the SQL:

begin

select account_id
        , premium
        , name
        , state
from shoes
where
    account_id  = ${accountId}
    and name    = ${dbShoe.name}
    and premium = ${dbShoe.premium}
for update


insert into shoes
    ( account_id
    , premium
    , name
    , state
    )
values
    ( $accountId
    , ${dbShoe.premium}
    , ${dbShoe.name}
    , ${dbShoe.state}
    )
on duplicate key update
    state = values(state)

<whatever is returned from the initial select and whatever is inserted is used to
construct the value string inserted below>

insert into kafka_audits (
    `account_id`,
    `topic`,
    `partition_key`,
    `key`,
    `value`,
    `metadata`
) values ($accountId, $topic, $partitionKey, $key, $value, $metadata)

commit
Tags:

2

Answers


  1. 0

    Perhaps this is what is happening.

    The two INSERTs are inserting new rows into the BTree handling

    UNIQUE KEY (`account_id`,`premium`,`name`),

    The INSERT with the lower account_id is doing a lock to avoid someone else sneaking in at the same spot in that BTree. The other INSERT lands in the same spot and punts.

    So, you ask, "why can’t it be resolved?" I think the answer is that the effort to resolve it would be too high for such a rare thing.

    This should be a viable fix: After every INSERT, check for failure. If a deadlock occurs, replay the aborted transaction. By then, the other query would have finished and released the locks.

    Some handwaving:

    This is one of many subtle deadlocks, leading to the rule of always checking and replaying if necessary.

    "gap locking" and "supremum" are clues that the processing is locking not just the row but also the space around the row. This is especially critical when an INSERT and/or AUTO_INCREMENT id is involved. Sure, InnoDB could work harder, but in your example the extra effort is probably not worth it.

    If the "first" query gets ROLLBACK'd, that impacts what the second one should do. This may explain why it can’t be turned into a "lock_wait_timeout".

    A philosophy in InnoDB: If a use case is very rare, punt instead of spending too much time on it.

    There may be a way to change your code slightly to avoid the deadlock. But, as I said, there could be other deadlock cases, so you should handle it in your app.

    Login or Signup to reply.
  2. 0

    Looks like you could get away with just

    • the IODKU No BEGIN/COMMIT around it. (But need autocommit=ON)
    • a TRIGGER that does the insert into the audit table. (Probably two triggers — one for ON INSERT, one for ON UPDATE, both essentially the same.)

    No need for the SELECT ... FOR UPDATE.

    I don’t know if this will avoid the deadlock, but at least it is likely to be faster.

    (Don’t get me going on how frameworks make things more difficult.)

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search