I am regularly seeing deadlocks in a production MySQL 5.5 database but cannot determine exactly what the cause is.
For context, we're inserting new records into a database which are triggered by another system. In some cases, these actions can be triggered twice, which has caused exceptions where the system correctly complains the row already exists.
We have therefore attempted to implement something of the form:
- Check if any rows already exist, grabbing a lock so no one else can INSERT and invalidate this query
- If any rows exist, return that row and end the transaction.
- If no rows exist,
INSERT
the new row - Release the lock, allowing other transactions to complete step 1, and all being well, jumping out via 2.
The theory being that any duplicate attempts to INSERT
will hit step 1 first, and as they'll have to wait for 4 to complete, will not be able to attempt a duplicate.
Here's the deadlock trace:
------------------------ LATEST DETECTED DEADLOCK ------------------------ 160324 12:27:51 *** (1) TRANSACTION: TRANSACTION 52CA7682, ACTIVE 0 sec fetching rows mysql tables in use 1, locked 1 LOCK WAIT 31 lock struct(s), heap size 6960, 2499 row lock(s) MySQL thread id 21789278, OS thread handle 0x2ae64b6ca700, query id 652918526 hostname ip db_name Updating update offer set OfferClickCounter = OfferClickCounter + 1 where quicklinkcode = 'yycajzzw' *** (1) WAITING FOR THIS LOCK TO BE GRANTED: RECORD LOCKS space id 3625 page no 45 n bits 168 index `PRIMARY` of table `db_name`.`offer` trx id 52CA7682 lock_mode X waiting Record lock, heap no 33 PHYSICAL RECORD: n_fields 25; compact format; info bits 0 0: len 4; hex 800108d5; asc ;; ... 24: len 4; hex 80000081; asc ;; *** (2) TRANSACTION: TRANSACTION 52CA7679, ACTIVE 0 sec inserting mysql tables in use 1, locked 1 16 lock struct(s), heap size 3112, 12 row lock(s), undo log entries 1 MySQL thread id 21789619, OS thread handle 0x2ae64afde700, query id 652918532 hostname ip the_db_name update INSERT INTO user_offer (CreatedDate, ...) VALUES ('2016-03-24 12:27:51', ....) *** (2) HOLDS THE LOCK(S): RECORD LOCKS space id 3625 page no 45 n bits 168 index `PRIMARY` of table `db_name`.`offer` trx id 52CA7679 lock_mode X locks rec but not gap Record lock, heap no 33 PHYSICAL RECORD: n_fields 25; compact format; info bits 0 0: len 4; hex 800108d5; asc ;; ... 24: len 4; hex 80000081; asc ;; *** (2) WAITING FOR THIS LOCK TO BE GRANTED: RECORD LOCKS space id 3625 page no 45 n bits 168 index `PRIMARY` of table `db_name`.`offer` trx id 52CA7679 lock mode S locks rec but not gap waiting Record lock, heap no 30 PHYSICAL RECORD: n_fields 25; compact format; info bits 0 0: len 4; hex 800108d2; asc ;; ... 24: len 4; hex 80001ee1; asc ;; *** WE ROLL BACK TRANSACTION (2)
My understanding from this Deadlock trace is that:
- Transaction 2 is attempting to INSERT into the
user_offer
table - which as mentioned earlier required aSELECT .. FOR UPDATE
. Transaction 2 therefore holds an X lock. - Transaction 1 wants an X lock to UPDATE the
offer
table, and is thus waiting on Transaction 2 (so far, so good) - Transaction 2 also needs an S lock to be granted on the same
offer
table (I believe due to the fact that theuser_offer
table and offer table are related by a foreign key on theuser_offer
table).
i.e.:
t0: T2: SELECT .. FOR UPDATE
locks offer
with X lock
t1: T1: UPDATE
asks for offer
X lock and waits
t2: T2: INSERT INTO user_offer
asks for S lock for offer
S lock can't be granted at t2 because X lock is already held by t0 (even though it's the same transaction), and another X lock is in the 'queue' before hand. Deadlock.
Have I got that right? How would I resolve this?
Any thoughts welcome and much appreciated.
WHERE