MySQL Forums
Forum List  »  InnoDB

MYSQL5.7 In the case of Read-Commit,Deadlocks caused by concurrent inserts(“Multiple-Column Indexes”)
Posted by: PengWei Wang
Date: July 17, 2021 11:15AM

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

Options: ReplyQuote


Subject
Views
Written By
Posted
MYSQL5.7 In the case of Read-Commit,Deadlocks caused by concurrent inserts(“Multiple-Column Indexes”)
165
July 17, 2021 11:15AM


Sorry, only registered users may post in this forum.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.