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

Question

我有一个简单的表格:

deviceid
pushid
tag
external_id

当向用户发送消息时，我们有时会根据 deviceid 更新 Pushid:

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

但同时我们可以从用户那里获得新的注册，我们可以使用

重置他的所有通知

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

这似乎会定期触发死锁。我已经在“deviceid”和“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