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