0

Table structure:

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 transactions at the same time delete and insert the same data, sometimes a deadlock happened. The tx_isolation is RR.

For example:

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 does "lock_mode X locks gap before rec insert intention waiting" happen?

But when we execute the transactions one by one a deadlock does not happen. For example:

step1: tx1 begin; delete.....;
step2: tx2 begin; delete.....;
step3: tx1 insert....;
step4: tx2 insert....;
step5: tx1 commit;

The deadlock happens only when there is high concurrency; the deadlock looks to be caused by the concurrency of tx1's delete and tx2's insert.

  • Do you really need both uniqueness constraints? – Rick James Jun 1 '19 at 2:44
0

You are deleting exactly the same record (by code) as you are inserting. This is why the server deadlocks, because there's now way for mysql to know which action to take. You need to handle this error in your application with an appropriate action based on the business case.

There is no magic to avoid this. The index is on code already

| improve this answer | |
  • What lock does transaction 1 hold?Why transaction 2 is waiting for transaction 1 – user182054 May 31 '19 at 10:14
  • 1) idx_unq_code_data_key for the code '00226915986'. 2) Transaction 2 isn't waiting, both are marked to change the same row, so both deadlock. – danblack Jun 4 '19 at 8:02
0

The issue looks very similar to my variant. As I understand an issue in fullscan in delete statement. You do not have indexes on and delete statement trying to fullscan on table, fullscan apply locks on all rows in table, even on non committed rows from your insert.

MySQL/innodb trying to lock uncommitted row from parallel transaction, deadlock in result

Try to create index on code and yn fileds. The index can reduce deadlock events. Make sure that delete use indexes instead of fullscan.

| improve this answer | |
  • not true - the index idx_unq_code_data_key will be used in the delete. yn isn't the second part of the index, however the same yn value is in the delete and insert. – danblack May 31 '19 at 15:11

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.