Mysql – Deadlock on MySQL insert statments

deadlockinsertMySQL

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.

Best Answer

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?

Related Solutions

Sql-server – SELECT/INSERT Deadlock

On the face of it, this looks like a classic lookup deadlock. The essential ingredients for this deadlock pattern are:

a SELECT query that uses a non-covering nonclustered index with a Key Lookup
an INSERT query that modifies the clustered index and then the nonclustered index

The SELECT accesses the nonclustered index first, then the clustered index. The INSERT access the clustered index first, then the nonclustered index. Accessing the same resources in a different order acquiring incompatible locks is a great way to 'achieve' a deadlock of course.

In this case, the SELECT query is:

SELECT query

...and the INSERT query is:

INSERT query

Notice the green highlighted non-clustered indexes maintenance.

We would need to see the serial version of the SELECT plan in case it is very different from the parallel version, but as Jonathan Kehayias notes in his guide to Handling Deadlocks, this particular deadlock pattern is very sensitive to timing and internal query execution implementation details. This type of deadlock often comes and goes without an obvious external reason.

Given access to the system concerned, and suitable permissions, I am certain we could eventually work out exactly why the deadlock occurs with the parallel plan but not the serial (assuming the same general shape). Potential lines of enquiry include checking for optimized nested loops and/or prefetching - both of which can internally escalate the isolation level to REPEATABLE READ for the duration of the statement. It is also possible that some feature of parallel index seek range assignment contributes to the issue. If the serial plan becomes available, I might spend some time looking into the details further, as it is potentially interesting.

The usual solution for this type of deadlocking is to make the index covering, though the number of columns in this case might make that impractical (and besides, we are not supposed to mess with such things on SharePoint, I am told). Ultimately, the recommendation for serial-only plans when using SharePoint is there for a reason (though not necessarily a good one, when it comes right down to it). If the change in cost threshold for parallelism fixes the issue for the moment, this is good. Longer term, I would probably look to separate the workloads, perhaps using Resource Governor so that SharePoint internal queries get the desired MAXDOP 1 behaviour and the other application is able to use parallelism.

The question of exchanges appearing in the deadlock trace seems a red herring to me; simply a consequence of the independent threads owning resources which technically must appear in the tree. I cannot see anything to suggest that the exchanges themselves are contributing directly to the deadlocking issue.

Mysql – InnoDB deadlock with INSERT .. SELECT

the deadlock message sometimes will not show the full picture. It could be some other query is holding the lock which is not any of those two showed in the log. As you said, you have delete query in the beginning, that properly would be the case.

Here is a way I normally use to troubleshoot the deadlocks when you can not get all the info from the log.

mysql> set global log_output = 'table';
mysql> set global general_log=on;

Then you reproduce the problem, when the deadlock happens, turn off the general log. Find those two queries in the mysql.general_log, then use thread_id to find the start and end of the transaction. You will have all the queries in the transaction, then you can go through the logic, to find out which is the query or logic caused the problem.

Best Answer

Related Solutions

Sql-server – SELECT/INSERT Deadlock

Mysql – InnoDB deadlock with INSERT .. SELECT

Related Question