MYSQL5.7 In the case of Read-Commit,Deadlocks caused by concurrent inserts(“Multiple-Column Indexes”)
We have a table like this:
-----------DDL-----------------
CREATE TABLE `dep_emp` (
`id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
`department_number` varchar(100) NOT NULL DEFAULT '',
`display_number` varchar(100) NOT NULL DEFAULT '' ,
PRIMARY KEY (`id`),
UNIQUE KEY `idx_department_number` (`department_number`, `display_number`)
) ENGINE = InnoDB AUTO_INCREMENT = 522991 DEFAULT CHARSET = utf8mb4
---------------------------
In the case of concurrent inserts, there is a deadlock. The following is the deadlock log：
------------------------
LATEST DETECTED DEADLOCK
------------------------
0x7f5abb8bd700
*** (1) TRANSACTION:
TRANSACTION 413911143, ACTIVE 3 sec inserting
mysql tables in use 1, locked 1
LOCK WAIT 3 lock struct(s), heap size 1136, 1 row lock(s), undo log entries 1
MySQL thread id 14005442, OS thread handle 140017585878784, query id 1503012797 10.xx.xx.xx a_user update
INSERT INTO dep_emp ( department_number,
display_number ) VALUES ( '10010630',
'10049237' )
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 176 page no 1447 n bits 328 index idx_department_number of table `organization`.`dep_emp` trx id 413911143 lock mode S waiting
Record lock, heap no 22 PHYSICAL RECORD: n_fields 3; compact format; info bits 0
0: len 8; hex 3130303130363330; asc 10010630;;
1: len 8; hex 3130303439323337; asc 10049237;;
2: len 8; hex 0000000000052e85; asc . ;;
*** (2) TRANSACTION:
TRANSACTION 413911068, ACTIVE 6 sec inserting
mysql tables in use 1, locked 1
4 lock struct(s), heap size 1136, 2 row lock(s), undo log entries 3197
MySQL thread id 14005599, OS thread handle 140027670288128, query id 1503014645 10.xx.xx.xx a_user update
INSERT INTO dep_emp ( department_number,
display_number ) VALUES ( '10010630',
'10049002' )
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 176 page no 1447 n bits 328 index idx_department_number of table `organization`.`dep_emp` trx id 413911068 lock_mode X locks rec but not gap
Record lock, heap no 22 PHYSICAL RECORD: n_fields 3; compact format; info bits 0
0: len 8; hex 3130303130363330; asc 10010630;;
1: len 8; hex 3130303439323337; asc 10049237;;
2: len 8; hex 0000000000052e85; asc . ;;
*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 176 page no 1447 n bits 352 index idx_department_number of table `organization`.`dep_emp` trx id 413911068 lock_mode X locks gap before rec insert intention waiting
Record lock, heap no 22 PHYSICAL RECORD: n_fields 3; compact format; info bits 0
0: len 8; hex 3130303130363330; asc 10010630;;
1: len 8; hex 3130303439323337; asc 10049237;;
2: len 8; hex 0000000000052e85; asc . ;;
*** WE ROLL BACK TRANSACTION (1)
What I don't understand is why the transaction(413911068) generates X locks on the record (10010630,10049237) and why there is a deadlock.
My transaction isolation level is read-committed.
Looking forward to answer