MySQL死锁，更新和删除在同一行

Question

我有一个简单的表，其中包含：

deviceid
pushid
tag
external_id

当向用户发送消息时，我们有时会根据设备ID更新推送ID：

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

但与此同时，我们可以从用户那里获得新的注册，我们用重置他的所有通知

delete from user_notifications where pushid='xyz' and external_id is null

这似乎经常引发死锁。我已经在"deviceid"one_answers"pushid，external_id"上添加了索引，但它似乎仍然会引发死锁。该表没有合适的主键，因此MySQL创建了一个GEN_CLUST_INDEX键。这会是原因吗？我应该添加一个自动递增主键吗？

------------------------
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  @   ;;
 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  @   ;;
 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)

Answer 1

正如您所说，日志显示的，更新"pushid"值和删除行同时发生。这应该很容易避免。有关一些官方提示，请参阅MySQL的"如何应对死锁"文档：http://dev.mysql.com/doc/refman/5.5/en/innodb-deadlocks.html