MySQL Forums
Forum List  »  InnoDB

mysql 5.6.26 high concurrency dead lock
Posted by: huafeng qu
Date: May 30, 2019 08:41PM

table struce:
CREATE TABLE `extend_0` (
`id` bigint(20) NOT NULL,
`code` varchar(30) NOT NULL,
`data_key` varchar(50) NOT NULL,
`data_value` varchar(200) NOT NULL,
`create_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
`update_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
`yn` tinyint(3) NOT NULL DEFAULT '1',
PRIMARY KEY (`id`,`create_time`),
UNIQUE KEY `idx_unq_code_data_key` (`code`,`data_key`,`create_time`) USING BTREE,
KEY `idx_code` (`code`) USING BTREE
) ENGINE=InnoDB;

when two transaction at the same time delete and insert the same data,
when high concurrency it happend deadlock.

Forexample:

DELETE FROM extend_0 WHERE code = '00226915986' AND yn = 1;

INSERT INTO extend_0 (id, code, data_key, data_value, create_time) VALUES (1133296779049299970, '00226915986', 'consignWare', 'food', '2019-05-28 16:59:42.418'), (1133296779049299971, '00226915986', 'productType', '0001,0006', '2019-05-28 16:59:42.418');

deadlock log:
------------------------------------------------------------------------------
2019-05-30 14:48:07 0x7fbb7872c700
*** (1) TRANSACTION:
TRANSACTION 125554670, ACTIVE 0 sec starting index read
mysql tables in use 1, locked 1
LOCK WAIT 2 lock struct(s), heap size 1136, 1 row lock(s)
MySQL thread id 5148727, OS thread handle 140443189679872, query id 6111057236 192.168.162.16 waybill updating
DELETE FROM extend_0 WHERE code = '00226915986' AND yn = 1
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 77 page no 3145 n bits 360 index idx_unq_code_data_key of table `waybill_0`.`extend_0` trx id 125554670 lock_mode X waiting
Record lock, heap no 285 PHYSICAL RECORD: n_fields 4; compact format; info bits 32
0: len 15; hex 4a4456433030323236393135393836; asc 00226915986;;
1: len 11; hex 636f6e7369676e57617265; asc consignWare;;
2: len 4; hex 5cecf87e; asc \ ~;;
3: len 8; hex 911d8ce2e1ddf000; asc ;;

*** (2) TRANSACTION:
TRANSACTION 125554668, ACTIVE 0 sec inserting
mysql tables in use 1, locked 1
5 lock struct(s), heap size 1136, 6 row lock(s), undo log entries 3
MySQL thread id 5148728, OS thread handle 140443156399872, query id 6111057237 192.168.162.16 waybill update
INSERT INTO extend_0 (id, code, data_key, data_value, create_time) VALUES (i-1, '00226915986', 'consignWare', 'food', '2019-05-28 16:59:42.418'), (i, '00226915986', 'productType', '0001,0006', '2019-05-28 16:59:42.418')
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 77 page no 3145 n bits 360 index idx_unq_code_data_key of table `waybill_0`.`extend_0` trx id 125554668 lock_mode X
Record lock, heap no 285 PHYSICAL RECORD: n_fields 4; compact format; info bits 32
0: len 15; hex 4a4456433030323236393135393836; asc 00226915986;;
1: len 11; hex 636f6e7369676e57617265; asc consignWare;;
2: len 4; hex 5cecf87e; asc \ ~;;
3: len 8; hex 911d8ce2e1ddf000; asc ;;

Record lock, heap no 287 PHYSICAL RECORD: n_fields 4; compact format; info bits 32
0: len 15; hex 4a4456433030323236393135393836; asc 00226915986;;
1: len 11; hex 70726f6475637454797065; asc productType;;
2: len 4; hex 5cecf87e; asc \ ~;;
3: len 8; hex 911d8ce2e1ddf020; asc ;;

*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 77 page no 3145 n bits 360 index idx_unq_waybill_code_data_key of table `waybill_0`.`extend_0` trx id 125554668 lock_mode X locks gap before rec insert intention waiting
Record lock, heap no 285 PHYSICAL RECORD: n_fields 4; compact format; info bits 32
0: len 15; hex 4a4456433030323236393135393836; asc JDVC00226915986;;
1: len 11; hex 636f6e7369676e57617265; asc consignWare;;
2: len 4; hex 5cecf87e; asc \ ~;;
3: len 8; hex 911d8ce2e1ddf000; asc ;;

*** WE ROLL BACK TRANSACTION (1)
------------------------------------------------------------------------------

the transaction2 has lock_mode X already. why wait lock_mode X locks gap before rec insert intention waiting.

Options: ReplyQuote


Subject
Views
Written By
Posted
mysql 5.6.26 high concurrency dead lock
264
May 30, 2019 08:41PM


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.