1

Using MySQL v5.7.18 on Windows Server 2008 R2, we are having intermittent deadlock issues during insert queries and are looking for some guidance on how to mitigate them. Below are the inserts that caused the latest deadlock:

INSERT INTO tran_detail
(store,tran_date,tran_nbr,line_nbr,register_nbr,sku,qty,unit_price,void_flag)
values ('173','20171125','475422',1,2,'347504',1,32,'') ON DUPLICATE
KEY UPDATE  sku='347504'

INSERT INTO tran_detail
(store,tran_date,tran_nbr,line_nbr,register_nbr,sku,qty,unit_price,void_flag)
values ('308','20171125','435126',4,1,'194071',2,10,'') ON DUPLICATE
KEY UPDATE  sku='194071'

On the tran_detail table we have these two unique indexes:

PRIMARY KEY (`Store`,`Tran_Date`,`Tran_Nbr`,`Line_Nbr`)
UNIQUE KEY `rec_id` (`rec_id`)

Here is the structure of the tran_detail table:

CREATE TABLE `tran_detail` (
       `rec_id` INT(11) NOT NULL AUTO_INCREMENT,
       `Store` INT(4) NOT NULL,
       `Tran_Date` DATE NOT NULL,
       `Tran_Nbr` INT(9) NOT NULL,
       `Line_Nbr` INT(9) NOT NULL,  
       `Register_Nbr` INT(4) DEFAULT NULL,
       `SKU` CHAR(8) DEFAULT NULL,  
       `Qty` INT(4) DEFAULT NULL,
       `Unit_Price` DECIMAL(10,2) DEFAULT NULL, 
       `Void_Flag` CHAR(1) DEFAULT,
       `Exch_Flag` CHAR(1) DEFAULT NULL,  
       `Proc_Flag` CHAR(1) DEFAULT NULL,
       PRIMARY KEY (`Store`,`Tran_Date`,`Tran_Nbr`,`Line_Nbr`),
       UNIQUE KEY `rec_id` (`rec_id`),
       KEY `SKU` (`SKU`),
       KEY `Tran_Nbr` (`Tran_Nbr`),
       KEY `Proc_Flag` (`Proc_Flag`),
       KEY `Void_Flag` (`Void_Flag`)
       )
ENGINE=INNODB

Here is the Last Detected Deadlock information:

2017-11-25 13:35:43 0xd230

*** (1) TRANSACTION: TRANSACTION 11845989, ACTIVE 0 sec inserting mysql tables in use 1, locked 1

LOCK WAIT 3 lock struct(s), heap size 1136, 2 row lock(s), undo log entries 1

MySQL thread id 2043132, OS thread handle 76844, query id 25422767 st1c8mtm23.tridm.com 172.28.103.166 trsportal update

INSERT INTO tran_detail (store,tran_date,tran_nbr,line_nbr,register_nbr,sku,qty,unit_price,void_flag)

values ('173','20171125','475422',1,2,'347504',1,32,'')

ON DUPLICATE KEY UPDATE sku='347504'

*** (1) WAITING FOR THIS LOCK TO BE GRANTED:

RECORD LOCKS space id 615 page no 8553 n bits 688 index rec_id of table trsspo.tran_detail trx id 11845989 lock_mode X insert intention waiting

Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0

0: len 8; hex 73757072656d756d; asc supremum;;

*** (2) TRANSACTION: TRANSACTION 11845988, ACTIVE 0 sec inserting, thread declared inside InnoDB 1 mysql tables in use 1, locked 1

4 lock struct(s), heap size 1136, 3 row lock(s), undo log entries 1

MySQL thread id 2043131, OS thread handle 53808, query id 25422764 st1c8mtm08.tridm.com 172.28.103.130 trsportal update

INSERT INTO tran_detail (store,tran_date,tran_nbr,line_nbr,register_nbr,sku,qty,unit_price,void_flag)

values ('308','20171125','435126',4,1,'194071',2,10,'')

ON DUPLICATE KEY UPDATE sku='194071'

*** (2) HOLDS THE LOCK(S):

RECORD LOCKS space id 615 page no 8553 n bits 688 index rec_id of table trsspo.tran_detail trx id 11845988 lock_mode X

Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0

0: len 8; hex 73757072656d756d; asc supremum;;

*** (2) WAITING FOR THIS LOCK TO BE GRANTED:

RECORD LOCKS space id 615 page no 8553 n bits 688 index rec_id of table trsspo.tran_detail trx id 11845988 lock_mode X insert intention waiting

Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0

0: len 8; hex 73757072656d756d; asc supremum;;

We think the following links sum up what is most likely occurring:

The tran_detail table is insert heavy, but we do have processes that regularly read from it for various tasks.

The rec_id field was added later to assist with a process in which we produce a CSV file for another department’s consumption, so it is not possible to remove.

The requests that insert the data are generated from POS systems that send GET requests created after sales are rung up, and the detail information from these sales are sent as separate HTTP requests.

The system where the database resides is not being taxed at all, so we believe it is a gap lock situation causing the deadlocks. We are currently not retrying the failed insert, though we are looking to do that.

Apart from retrying the failed insert, what other steps are recommended to clear the deadlocks? Let us know if additional information is needed.

0

There is probably no necessity to make rec_id UNIQUE; make it simply INDEX.

  • IODKU must check all both unique keys -- the PK and the one on rec_id. I think (without proof) that the deadlock is on rec_id.
  • The only way for an AUTO_INCREMENT to get in trouble without UNIQUE is if you deliberately insert two rows with the same rec_id. If you never do such, INDEX is sufficient.

But... Will this suffice? I am not sure. So... Plan for deadlocks -- and simply replay the transaction.

Side notes:

  • INT(4) and INT(11) are identical; the 4 and 11 mean nothing. Perhaps you want SMALLINT UNSIGNED for store?
  • If the columns are defaulting to utf8, CHAR is inefficient; most of the char fields could be CHARACTER SET ascii.
  • Flags are rarely useful by themselves for indexes. What query might se them? Perhaps a 'composite' index would be better?
  • Is there any need for rec_id?
  • Thanks, Rick. Sorry for the delayed response. We intend to look at implementing your suggestion regarding the rec_id index and retries. On the side notes, the table structure had been in place prior to our taking over the project, so we left well enough alone. As far as the rec_id, we do use it for other processes so will need to keep it. Thanks again. – Doug Van De Motter Dec 8 '17 at 14:45
  • @DougVanDeMotter - Keeping rec_id - OK. Making it UNIQUE instead of just INDEX - this may be aggravating the deadlock situation. – Rick James Dec 8 '17 at 21:57

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.