0

I have a very simple "inventory management" update that under load causes InnoDB to deadlock and rollback one of the threads. There doesn't appear to be any ordering issue here, so I'm at a loss how to resolve it. MySQL version is 5.6.33-0

update product
set available_qty = available_qty - 1
where sku = arg_sku and available_qty > 0

Here is the full deadlock dump:

InnoDB: transactions deadlock detected, dumping detailed information.

*** (1) TRANSACTION:
TRANSACTION 50428499, ACTIVE 0 sec starting index read
mysql tables in use 1, locked 1
LOCK WAIT 10 lock struct(s), heap size 1184, 4 row lock(s), undo log entries 3

update product
set available_qty = available_qty - 1
where sku = arg_sku and available_qty > 0

*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 596 page no 96 n bits 152 index `PRIMARY` of table `mydbname`.`product` trx id 50428499 lock_mode X locks rec but not gap waiting

*** (2) TRANSACTION:
TRANSACTION 50428498, ACTIVE 0 sec starting index read
mysql tables in use 1, locked 1
9 lock struct(s), heap size 1184, 3 row lock(s), undo log entries 4

update product
set available_qty = available_qty - 1
where sku = arg_sku and available_qty > 0

*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 596 page no 96 n bits 152 index `PRIMARY` of table `mydbname`.`product` trx id 50428498 lock mode S locks rec but not gap

*** (2) WAITING FOR THIS LOCK TO BE GRANTED:  
RECORD LOCKS space id 596 page no 45 n bits 680 index `sku` of table `mydbname`.`product` trx id 50428498 lock_mode X locks rec but not gap waiting

*** WE ROLL BACK TRANSACTION (2)

From what I'm reading, the interaction between a shared (S) lock and an exclusive (X) lock causes my concurrent updates to deadlock. Is there anything I can do to avoid this?

Update:

I believe this is a bug in MySQL.

Two concurrent threads acquire Shared-locks and try to acquire eXclusive locks to do the update. They wait for the other to release the Shared-lock first. There is no ordering issue in the way locks are acquired; the mixing of types of locks is the issue. MySQL should be able to "upgrade" the existing shared-lock to eXclusive. If another thread wants to do the same, the first thread should make progress by obtaining eXclusive lock, do the update, then release the lock so the other Shared-lock can be upgraded, and so on.

Workaround

My UPDATE statement was modified to use the PRIMARY KEY (from previous UNIQUE KEY). In addition, I'm first calling a SELECT matching the condition of the UPDATE and skip the UPDATE entirely if the SELECT comes back empty, thus avoiding the eXclusive lock whenever possible. All of this is in a stored proc so clients aren't affected.

10
  • HI, could You also provide table product structure? and logic - how concurrent transaction handle arg_sku value? For avoid deadlocks, You must be sure - 1) sku - has index, 2) 2 separate transaction never will run update for same sku - this is in application logic – a_vlad Dec 3 '16 at 0:00
  • Naturally multiple threads (connections) want to update available_qty on the same SKU. That's the very nature of concurrency. Imagine you are selling "concert tickets" - a ton of connections want to update the same SKU's quantity at once. This mustn't create a deadlock, just serialized execution. – Slawomir Dec 3 '16 at 11:37
  • You are right IF ... and this IF include - dose it indexed? How many rows with same SKU? How big range for the available_qty > 0 and etc – a_vlad Dec 3 '16 at 11:40
  • SKU is a unique key. There is a ton of available_qty>0 for other SKUs. – Slawomir Dec 3 '16 at 11:45
  • this is was a question, so SKU is unique key, correct? some useful recommendations - percona.com/blog/2012/03/27/innodbs-gap-locks – a_vlad Dec 3 '16 at 11:54

Your Answer

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

Browse other questions tagged or ask your own question.