Why does Deadlock occur for this INSERT/UPDATE combination despite seemingly holding an X lock?

Question

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:

1. Check if any rows already exist, grabbing a lock so no one else can INSERT and invalidate this query
2. If any rows exist, return that row and end the transaction.
3. If no rows exist, INSERT the new row
4. 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 a SELECT .. 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 the user_offer table and offer table are related by a foreign key on the user_offer table).

i.e.:

t₀: T2: SELECT .. FOR UPDATE locks offer with X lock

t₁: T1: UPDATE asks for offer X lock and waits

t₂: T2: INSERT INTO user_offer asks for S lock for offer

S lock can't be granted at t₂ because X lock is already held by t₀ (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.

Answer 1

Can some or all of the 4 steps be replaced by a single query:

INSERT ... ON DUPLICATE KEY UPDATE ...

or maybe

INSERT IGNORE ... and check rows_affected?

It would be better if you showed us all the SQL between BEGIN and COMMIT.

Are you doing any time-consuming processing between BEGIN and COMMIT? Can any of it be moved out. The faster the transaction runs, the less likely it will deadlock.

Do you have code to rerun the transaction when it deadlocks? This a useful "last resort". (It is not yet obvious whether the particular deadlock can always be avoided.)