Description:
For this deadlock case, we combined with the source code analysis:
Concluded that transaction T1 when performing a second insert statement, the record is in transaction T1 to perform the insert for the first time, have obtained next_rec (ua record of 10) LOCK_REC | LOCK_X | LOCK_REC_NOT_GAP lock.
However, a look at the source code reveals that since lock_rec_insert_check_and_lock() does not have lock_rec_lock_slow(), the function lock_rec_has_expl() checks for a stronger lock on a transaction. Instead, the lock_rec_get_first() is used to determine if there is a lock on another transaction, causing the transaction to execute the second insert statement, unable to acquire the lock and blocked. A deadlock is formed.
Therefore, it is suggested that the lock_rec_insert_check_and_lock() function, after the lock_rec_get_first() function determines that the record has a lock, then the lock_rec_has_expl() function checks for a stronger lock on the transaction. To avoid unnecessary deadlocks.
Specific source analysis path, see the attached figure. Among them:
The blue line represents the logic executed by T1 for the first insertion;
The purple line represents the logic executed by T2's first insertion;
The black line indicates the logic performed by T1's second insertion;
Lock operations associated with unique index insertion records are marked with red short arrows;
A vertical bar crossed with a red spike indicates that the function was executed, otherwise it is not executed.
How to repeat:
After verification, it can be repeated in the following scenarios:
CREATE TABLE `dl_tab` (
`id` int NOT NULL AUTO_INCREMENT,
`name` int NOT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `ua` (`name`)
) ENGINE=InnoDB AUTO_INCREMENT=41 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
insert into dl_tab values(1,3);
insert into dl_tab values(5,7);
insert into dl_tab values(9,13);
insert into dl_tab values(17,23);
T2: | T1:
begin; | begin;
|
| insert into dl_tab(id,name) values(26,10);
|
insert into dl_tab(id,name) values(30,10); |
|
| insert into dl_tab(id,name) values(40,8);
ERROR 1213 (40001): Deadlock found when trying |
to get lock; try restarting transaction |
Description: For this deadlock case, we combined with the source code analysis: Concluded that transaction T1 when performing a second insert statement, the record is in transaction T1 to perform the insert for the first time, have obtained next_rec (ua record of 10) LOCK_REC | LOCK_X | LOCK_REC_NOT_GAP lock. However, a look at the source code reveals that since lock_rec_insert_check_and_lock() does not have lock_rec_lock_slow(), the function lock_rec_has_expl() checks for a stronger lock on a transaction. Instead, the lock_rec_get_first() is used to determine if there is a lock on another transaction, causing the transaction to execute the second insert statement, unable to acquire the lock and blocked. A deadlock is formed. Therefore, it is suggested that the lock_rec_insert_check_and_lock() function, after the lock_rec_get_first() function determines that the record has a lock, then the lock_rec_has_expl() function checks for a stronger lock on the transaction. To avoid unnecessary deadlocks. Specific source analysis path, see the attached figure. Among them: The blue line represents the logic executed by T1 for the first insertion; The purple line represents the logic executed by T2's first insertion; The black line indicates the logic performed by T1's second insertion; Lock operations associated with unique index insertion records are marked with red short arrows; A vertical bar crossed with a red spike indicates that the function was executed, otherwise it is not executed. How to repeat: After verification, it can be repeated in the following scenarios: CREATE TABLE `dl_tab` ( `id` int NOT NULL AUTO_INCREMENT, `name` int NOT NULL, PRIMARY KEY (`id`), UNIQUE KEY `ua` (`name`) ) ENGINE=InnoDB AUTO_INCREMENT=41 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci; insert into dl_tab values(1,3); insert into dl_tab values(5,7); insert into dl_tab values(9,13); insert into dl_tab values(17,23); T2: | T1: begin; | begin; | | insert into dl_tab(id,name) values(26,10); | insert into dl_tab(id,name) values(30,10); | | | insert into dl_tab(id,name) values(40,8); ERROR 1213 (40001): Deadlock found when trying | to get lock; try restarting transaction |