我收到这个序列化失败: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
所以他们不应该再干涉了。但是您可能有其他任务和查询不容易分离。如果没有任何帮助,重复死锁的查询也是可行的解决方案。因为您的逻辑不要求您使用事务,所以不应该对当前数据库状态有依赖关系,所以只要重复它就可以了,如果它不经常发生,这会减慢您的进程(太多)。虽然这看起来有点难看,但对于其他错误,如连接丢失或超时,也需要这样做。
这似乎是一个批处理队列系统。如果您还没有深入到您的开发中,并且当然取决于许多其他因素和需求,那么您可能需要看看其他现有的消息队列软件,这可能会使您的生活更轻松一些。