Deadlock with insert/update statements

From: cnordvik <fotmobfotmob_at_gmail.com>
Date: Tue, 14 Oct 2014 12:46:53 -0700 (PDT)
Message-ID: <6e1872b3-aa47-40d2-a93b-d0e87432088b_at_googlegroups.com>



This is posted here as well:
http://stackoverflow.com/questions/26357416/mysql-deadlock-with-update-and-delete-on-the-same-row

I have a simple table with:

deviceid
pushid
tag
external_id
When sending messages to the users, we sometimes do an update of the pushid based on the deviceid:

update user_notifications set pushid='xyz' where deviceid='abc' But at the same time we can get a new registration from the user where we reset all his notifications with

delete from user_notifications where pushid='xyz' and external_id is null This seems to trigger deadlocks regularly. I have added indexes on "deviceid" and "pushid, external_id" but it seems to still trigger a deadlock. The table have no suitable primary key so MySQL have created a GEN_CLUST_INDEX key. Can this be the reason? Should I add an auto-incrementing primary key?



LATEST DETECTED DEADLOCK

141014 8:13:38
*** (1) TRANSACTION:

TRANSACTION F5ED32, ACTIVE 0 sec starting index read mysql tables in use 1, locked 1
LOCK WAIT 3 lock struct(s), heap size 1248, 2 row lock(s) MySQL thread id 2422, OS thread handle 0x7f6295cd3700, query id 35096 localhost root Updating update user_notifications set pushid='APA91bEO5zBhpAqiNlHIlWvvb0U4KH2JWByzh5IzmJFg2GZyXX2s1wJ2pbTHWoTDDao5hoZ10e1bw70Z5nTi4dIEfsTj6q-cS9U0VuqwGkWpW4ofb4XnbjOd39845_jXsPaiFg5EmD0Y9JSd3rP3BY-M8ZQEet1So6SBOgSLdjlV5MtxYyR5kos' where deviceid='64881a83-c43b-4282-b82f-2a136395e3c6'
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 0 page no 143022 n bits 128 index `GEN_CLUST_INDEX` of table `myappdb`.`user_notifications` trx id F5ED32 lock_mode X locks rec but not gap waiting Record lock, heap no 55 PHYSICAL RECORD: n_fields 7; compact format; info bits 32
 0: len 6; hex 0000003315f7; asc    3  ;;
 1: len 6; hex 000000f5ed31; asc      1;;
 2: len 7; hex 63000c40180110; asc c  _at_   ;;
 3: len 30; hex 36343838316138332d633433622d343238322d623832662d326131333633; asc 64881a83-c43b-4282-b82f-2a1363; (total 36 bytes);
 4: len 30; hex 415041393162454f357a4268704171694e6c48496c577676623055344b48; asc APA91bEO5zBhpAqiNlHIlWvvb0U4KH; (total 183 bytes);
 5: len 9; hex 7465616d5f39383233; asc team_9823;;
 6: SQL NULL;

  • (2) TRANSACTION: TRANSACTION F5ED31, ACTIVE 0 sec updating or deleting mysql tables in use 1, locked 1 4 lock struct(s), heap size 1248, 3 row lock(s), undo log entries 1 MySQL thread id 2423, OS thread handle 0x7f6295c92700, query id 35104 localhost root updating delete from user_notifications where pushid='APA91bEO5zBhpAqiNlHIlWvvb0U4KH2JWByzh5IzmJFg2GZyXX2s1wJ2pbTHWoTDDao5hoZ10e1bw70Z5nTi4dIEfsTj6q-cS9U0VuqwGkWpW4ofb4XnbjOd39845_jXsPaiFg5EmD0Y9JSd3rP3BY-M8ZQEet1So6SBOgSLdjlV5MtxYyR5kos' and external_id is null
  • (2) HOLDS THE LOCK(S): RECORD LOCKS space id 0 page no 143022 n bits 128 index `GEN_CLUST_INDEX` of table `myappdb`.`user_notifications` trx id F5ED31 lock_mode X locks rec but not gap Record lock, heap no 55 PHYSICAL RECORD: n_fields 7; compact format; info bits 32 0: len 6; hex 0000003315f7; asc 3 ;;
    1. len 6; hex 000000f5ed31; asc 1;;
    2. len 7; hex 63000c40180110; asc c _at_ ;;
    3. len 30; hex 36343838316138332d633433622d343238322d623832662d326131333633; asc 64881a83-c43b-4282-b82f-2a1363; (total 36 bytes);
    4. len 30; hex 415041393162454f357a4268704171694e6c48496c577676623055344b48; asc APA91bEO5zBhpAqiNlHIlWvvb0U4KH; (total 183 bytes);
    5. len 9; hex 7465616d5f39383233; asc team_9823;;
    6. SQL NULL;
  • (2) WAITING FOR THIS LOCK TO BE GRANTED: RECORD LOCKS space id 0 page no 68396 n bits 232 index `index3` of table `myappdb`.`user_notifications` trx id F5ED31 lock_mode X locks rec but not gap waiting Record lock, heap no 97 PHYSICAL RECORD: n_fields 2; compact format; info bits 0 0: len 30; hex 36343838316138332d633433622d343238322d623832662d326131333633; asc 64881a83-c43b-4282-b82f-2a1363; (total 36 bytes);
    1. len 6; hex 0000003315f7; asc 3 ;;
  • WE ROLL BACK TRANSACTION (1)
Received on Tue Oct 14 2014 - 21:46:53 CEST

Original text of this message