I am debugging a deadlock caused by the duplicate-key error. According to MySQL document,
If a duplicate-key error occurs, a shared lock on the duplicate index record is set. This use of a shared lock can result in deadlock should there be multiple sessions trying to insert the same row if another session already has an exclusive lock. This can occur if another session deletes the row.
The example it gives is easy to understand because it involves 3 sessions. But my case just involves 2 transactions. According to the logs, the following deadlock happened, transaction A holds the lock lock_mode X locks rec but not gap and waiting lock_mode X insert intention waiting while transaction B waiting lock mode S locks rec but not gap waiting
And before the deadlock happened we found one of the transactions already inserted into a record, then another transaction tried to insert the same data. But I still do not figure out why the duplicate-key error caused the deadlock.
Can anyone shed some light on it?
*** (1) TRANSACTION:
TRANSACTION 3802025924, ACTIVE 0 sec inserting
...
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 1469239 page no 46337 n bits 128 index PRIMARY of table `emicall_cc_man`.`seat_c_state_record` /* Partition `p4` */ trx id 3802025924 lock mode S locks rec but not gap waiting
*** (2) TRANSACTION:
TRANSACTION 3802025922, ACTIVE 0 sec inserting
...
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 1469239 page no 46337 n bits 128 index PRIMARY of table `emicall_cc_man`.`seat_c_state_record` /* Partition `p4` */ trx id 3802025922 lock_mode X locks rec but not gap
*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 1469239 page no 46338 n bits 128 index PRIMARY of table `emicall_cc_man`.`seat_c_state_record` /* Partition `p4` */ trx id 3802025922 lock_mode X insert intention waiting
