我收到这个序列化失败：1213死锁在我开发和管理的系统上经常发现错误。
它发生在一个非常大和繁忙的table上。它有大约1000万行，峰值时可能每秒从5台服务器得到40多个查询。
代码不使用事务。这张table上没有锁。表是innodb，有一个自动递增的主键。
在以下查询过程中发生错误：
UPDATE `Messages` SET Status='(new status)' WHERE `MessageID`='(ID)'查询是准备好的，然后用php和pdo执行。99%的时间都很好。
是什么导致了这种情况？我该如何调试它？
我使用的是php7.0.22和mysql版本14.14发行版5.7.20。mysql是在一个独立于php的服务器上运行的，并且被复制以便 SELECT 查询在其中一个从属服务器上运行。除了几个例外，它应该是 INSERT s和 UPDATE 在主数据库服务器上运行。
谢谢！
编辑：结果 SHOW ENGINE InnoDB STATUS :
------------------------
LATEST DETECTED DEADLOCK
------------------------
2018-01-06 10:36:37 0x7fe606788700
***(1) TRANSACTION:
TRANSACTION 492758175, ACTIVE 0 sec starting index read
mysql tables in use 1, locked 1
LOCK WAIT 3 lock struct(s), heap size 1136, 3 row lock(s)
MySQL thread id 2871743, OS thread handle 140625951213312, query id 167981859 sg-msg-02.company.local 10.32.80.3 myapp System lock
UPDATE Messages SET ThreadID=9 WHERE Status IS NULL AND ScheduleDate<now() AND ThreadID=0 ORDER BY FairQueuePos, ScheduleDate LIMIT 50
***(1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 63 page no 438651 n bits 184 index PRIMARY of table `Messaging`.`Messages` trx id 492758175 lock_mode X locks rec but not gap waiting
Record lock, heap no 115 PHYSICAL RECORD: n_fields 14; compact format; info bits 0
0: len 8; hex 800000000d3444e8; asc 4D ;;
1: len 6; hex 00001d5ee49e; asc ^ ;;
2: len 7; hex 7f0000033e0110; asc > ;;
3: len 8; hex 800000000000048c; asc ;;
4: len 12; hex 343437393532323333343436; asc 447952233446;;
5: len 4; hex 5a50a6b5; asc ZP ;;
6: len 4; hex 5a50a6b4; asc ZP ;;
7: len 4; hex 5a50a6b5; asc ZP ;;
8: len 4; hex 5a50a6b4; asc ZP ;;
9: len 4; hex 80000008; asc ;;
10: len 1; hex 02; asc ;;
11: len 30; hex 30313963613639662d393262662d346637372d623465632d353161363165; asc 019ca69f-92bf-4f77-b4ec-51a61e; (total 36 bytes);
12: len 2; hex 8021; asc !;;
13: len 4; hex 80000005; asc ;;
***(2) TRANSACTION:
TRANSACTION 492758174, ACTIVE 0 sec updating or deleting
mysql tables in use 1, locked 1
3 lock struct(s), heap size 1136, 2 row lock(s), undo log entries 1
MySQL thread id 2871805, OS thread handle 140625927767808, query id 167981857 sg-msg-03.company.local 10.32.80.4 myapp updating
UPDATE Messages SET Status='Sent', RemoteMessageID='(redacted)', SentDate=now(), RouteID='8' WHERE MessageID='(redacted)' LIMIT 1
***(2) HOLDS THE LOCK(S):
RECORD LOCKS space id 63 page no 438651 n bits 184 index PRIMARY of table `Messaging`.`Messages` trx id 492758174 lock_mode X locks rec but not gap
Record lock, heap no 115 PHYSICAL RECORD: n_fields 14; compact format; info bits 0
0: len 8; hex 800000000d3444e8; asc 4D ;;
1: len 6; hex 00001d5ee49e; asc ^ ;;
2: len 7; hex 7f0000033e0110; asc > ;;
3: len 8; hex 800000000000048c; asc ;;
4: len 12; hex 343437393532323333343436; asc 447952233446;;
5: len 4; hex 5a50a6b5; asc ZP ;;
6: len 4; hex 5a50a6b4; asc ZP ;;
7: len 4; hex 5a50a6b5; asc ZP ;;
8: len 4; hex 5a50a6b4; asc ZP ;;
9: len 4; hex 80000008; asc ;;
10: len 1; hex 02; asc ;;
11: len 30; hex 30313963613639662d393262662d346637372d623465632d353161363165; asc 019ca69f-92bf-4f77-b4ec-51a61e; (total 36 bytes);
12: len 2; hex 8021; asc !;;
13: len 4; hex 80000005; asc ;;
***(2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 63 page no 443436 n bits 672 index StatusThreadSchedule of table `Messaging`.`Messages` trx id 492758174 lock_mode X locks rec but not gap waiting
Record lock, heap no 398 PHYSICAL RECORD: n_fields 4; compact format; info bits 0
0: SQL NULL;
1: len 4; hex 5a50a6b4; asc ZP ;;
2: len 2; hex 8021; asc !;;
3: len 8; hex 800000000d3444e8; asc 4D ;;
***WE ROLL BACK TRANSACTION (1)
编辑2： SHOW CREATE TABLE Messages ```
CREATE TABLE Messages (MessageID bigint(20) NOT NULL AUTO_INCREMENT,UserID bigint(20) NOT NULL DEFAULT '0',DestinationAddress varchar(20) NOT NULL DEFAULT '',LastUpdate timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,CreationDate timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',SentDate timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',ScheduleDate timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',RouteID int(11) NOT NULL DEFAULT '0',Status enum('Sending','Sent','Delivered','Undeliverable','Failed','Deleted','Deleting','Rejected','Unknown','Expired') DEFAULT NULL,RemoteMessageID varchar(64) DEFAULT NULL,ThreadID smallint(6) DEFAULT NULL,FairQueuePos int(11) NOT NULL DEFAULT '1',
PRIMARY KEY (MessageID),
KEY IX_Relationship44 (UserID),
KEY IX_Relationship67 (RouteID),
KEY RemoteMessageID (RemoteMessageID),
KEY UserSchedule (ScheduleDate),
KEY StatusThreadSchedule (Status,ScheduleDate,ThreadID),
KEY UserStatus (UserID,Status)
) ENGINE=InnoDB AUTO_INCREMENT=221559699 DEFAULT CHARSET=utf8 MAX_ROWS=4294967295
两个注意事项：较旧的消息从表中移到存档表中（因此 `AUTO_INCREMENT` 当前位于）。第二个表包含与此问题无关的有关消息的更多详细信息（如消息的实际内容和其他元数据）。
而且我知道钥匙可能太多了，我很多年前加的，不敢乱动：）
1条答案
按热度按时间axzmvihb1#
如果您真的不使用事务，它看起来像是外键的问题。您可能有来自的自引用外键
RemoteMessageID至ID（还有其他人）。第一个问题，
可能会锁定第二个查询所在的行
引用以及更新的行本身，以及错误的计时可能会导致死锁。
除了一些明显的情况外，为死锁添加快速修复并不容易，尤其是在没有看到和分析完整的代码/逻辑的情况下。这里可以找到一些一般的提示。
它可能有助于实际使用事务并使用
它将尝试在使用它之前锁定它将使用的行，如果不能锁定，则等待它可以锁定，而不是死锁（尽管它仍然可能导致死锁）。对于其他外键，您可能需要类似的东西，尽管这可能是可疑的。
它还可以帮助添加一个索引来支持您的第一个查询（可能至少包括
ThreadID和/或Status，例如。FairQueuePos)以减少被该查询锁定的行数，并减少执行该查询的时间。我猜第二个查询可能甚至不涉及ThreadID=0所以他们不应该再干涉了。但是您可能有其他任务和查询不容易分离。如果没有任何帮助，重复死锁的查询也是可行的解决方案。因为您的逻辑不要求您使用事务，所以不应该对当前数据库状态有依赖关系，所以只要重复它就可以了，如果它不经常发生，这会减慢您的进程（太多）。虽然这看起来有点难看，但对于其他错误，如连接丢失或超时，也需要这样做。
这似乎是一个批处理队列系统。如果您还没有深入到您的开发中，并且当然取决于许多其他因素和需求，那么您可能需要看看其他现有的消息队列软件，这可能会使您的生活更轻松一些。