MySQL deadlock with update and delete on the same row -

MySQL deadlock with update and delete on the same row -

i have simple table with:

deviceid pushid tag external_id

when sending messages users, update of pushid based on deviceid:

update user_notifications set pushid='xyz' deviceid='abc'

but @ same time can new registration user reset notifications

delete user_notifications pushid='xyz' , external_id null

this seems trigger deadlocks regularly. have added indexes on "deviceid" , "pushid, external_id" seems still trigger deadlock. table have no suitable primary key mysql have created gen_clust_index key. can reason? should add together auto-incrementing primary key?

------------------------ latest detected deadlock ------------------------ 141014 8:13:38 *** (1) transaction: transaction f5ed32, active 0 sec starting index read mysql tables in utilize 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' deviceid='64881a83-c43b-4282-b82f-2a136395e3c6' *** (1) waiting lock 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 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 @ ;; 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 utilize 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 user_notifications pushid='apa91beo5zbhpaqinlhilwvvb0u4kh2jwbyzh5izmjfg2gzyxx2s1wj2pbthwotddao5hoz10e1bw70z5nti4diefstj6q-cs9u0vuqwgkwpw4ofb4xnbjod39845_jxspaifg5emd0y9jsd3rp3by-m8zqeet1so6sbogsldjlv5mtxyyr5kos' , external_id null *** (2) holds 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 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 @ ;; 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 lock 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 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 ;; *** roll transaction (1)

as said , log shows, updating 'pushid' value , deleting row happening @ same time. should easy avoid. official tips, take @ mysql 'how cope deadlocks" document: http://dev.mysql.com/doc/refman/5.5/en/innodb-deadlocks.html

mysql deadlock google-cloud-sql