需要帮助了解MySQL死锁的位置

zlwx9yxi  于 2023-06-21  发布在  Mysql
关注(0)|答案(3)|浏览(135)

我们有一个长期存在的问题，在我们的数据库中有一定的僵局。冲突的查询几乎总是这两个：

UPDATE db.tbl 
    SET pid = 68111
WHERE pid IS NULL 
AND processed IS NULL 
AND `time` <= now() 
LIMIT 10

和/或

UPDATE `db`.`tbl` 
    SET `processed` = -1,
        `updated_on` = NOW() 
WHERE (`tbl`.`id` = 108588129)

pid、id和processed的值不同，但查询是相同的。
我不明白的是，究竟是什么会在那里死锁，因为第二个查询会根据ID更新特定的记录。这里只有一个锁，但是你至少需要两个锁来创建一个死锁。也没有会锁定其他行的事务-这些查询中的每一个都是独立的。
为什么会发生这种情况，我该如何避免？
show engine status innodb;的匿名/缩短输出：

------------------------
LATEST DETECTED DEADLOCK
------------------------
2023-06-08 08:16:27 0x7f70f3ef1700
*** (1) TRANSACTION:
TRANSACTION 2857804352, ACTIVE 0 sec fetching rows
mysql tables in use 1, locked 1
LOCK WAIT 2480 lock struct(s), heap size 286928, 9581 row lock(s)
MySQL thread id 25966272, OS thread handle 140144661681920, query id 847014117 x.x.x.x db_user updating
UPDATE db.tbl SET pid = ''68111'' WHERE pid IS NULL AND processed IS NULL AND `time` <= now() LIMIT 10

*** (1) HOLDS THE LOCK(S):
RECORD LOCKS space id 2714 page no 86552 n bits 1552 index processed of table `db`.`tbl` trx id 2857804352 lock_mode X
Record lock, heap no 2 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
 0: SQL NULL;
 1: len 4; hex 8678cf9b; asc  x  ;;

Record lock, heap no 3 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
 0: SQL NULL;
 1: len 4; hex 8678cfad; asc  x  ;;

<Snip a lot of Record Locks just like the one above>

*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 2714 page no 113955 n bits 160 index PRIMARY of table `db`.`tbl` trx id 2857804352 lock_mode X locks rec but not gap waiting
Record lock, heap no 92 PHYSICAL RECORD: n_fields 23; compact format; info bits 0
 0: len 4; hex 8678ec61; asc  x a;;
 1: len 6; hex 0000aa56a25d; asc    V ];;
 2: len 7; hex 0100002bc01686; asc    +   ;;
 3: len 4; hex 803ff583; asc  ?  ;;
 4: len 4; hex 73746f70; asc stop;;
 5: len 4; hex 80000002; asc     ;;
 6: len 1; hex 83; asc  ;;
 7: len 4; hex 80005737; asc   W7;;
 8: len 6; hex 4b4a38363431; asc KJ8641;;
 9: len 8; hex 80000008a515e59b; asc         ;;
 10: len 4; hex 800001be; asc     ;;
 11: len 5; hex 99b050b41a; asc   P  ;;
 12: len 4; hex 84ad8892; asc     ;;
 13: len 3; hex 736d73; asc sms;;
 14: SQL NULL;
 15: SQL NULL;
 16: len 1; hex 7f; asc  ;;
 17: SQL NULL;
 18: SQL NULL;
 19: SQL NULL;
 20: len 4; hex 53746f70; asc Stop;;
 21: len 5; hex 99b050b41b; asc   P  ;;
 22: len 5; hex 99b050b41b; asc   P  ;;

*** (2) TRANSACTION:
TRANSACTION 2857804381, ACTIVE 0 sec updating or deleting
mysql tables in use 1, locked 1
LOCK WAIT 3 lock struct(s), heap size 1136, 2 row lock(s), undo log entries 1
MySQL thread id 25966282, OS thread handle 140122483259136, query id 847014368 x.x.x.x other_db_user updating
UPDATE `db`.`tbl` SET `processed` = ''-1'', `updated_on` = NOW() WHERE (`tbl`.`id` = 108588129)

*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 2714 page no 113955 n bits 160 index PRIMARY of table `db`.`tbl` trx id 2857804381 lock_mode X locks rec but not gap
Record lock, heap no 92 PHYSICAL RECORD: n_fields 23; compact format; info bits 0
 0: len 4; hex 8678ec61; asc  x a;;
 1: len 6; hex 0000aa56a25d; asc    V ];;
 2: len 7; hex 0100002bc01686; asc    +   ;;
 3: len 4; hex 803ff583; asc  ?  ;;
 4: len 4; hex 73746f70; asc stop;;
 5: len 4; hex 80000002; asc     ;;
 6: len 1; hex 83; asc  ;;
 7: len 4; hex 80005737; asc   W7;;
 8: len 6; hex 4b4a38363431; asc KJ8641;;
 9: len 8; hex 80000008a515e59b; asc         ;;
 10: len 4; hex 800001be; asc     ;;
 11: len 5; hex 99b050b41a; asc   P  ;;
 12: len 4; hex 84ad8892; asc     ;;
 13: len 3; hex 736d73; asc sms;;
 14: SQL NULL;
 15: SQL NULL;
 16: len 1; hex 7f; asc  ;;
 17: SQL NULL;
 18: SQL NULL;
 19: SQL NULL;
 20: len 4; hex 53746f70; asc Stop;;
 21: len 5; hex 99b050b41b; asc   P  ;;
 22: len 5; hex 99b050b41b; asc   P  ;;

*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 2714 page no 86552 n bits 1552 index processed of table `db`.`tbl` trx id 2857804381 lock_mode X locks rec but not gap waiting
Record lock, heap no 1462 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
 0: SQL NULL;
 1: len 4; hex 8678ec61; asc  x a;;

*** WE ROLL BACK TRANSACTION (2)

此外，还有一个简化的表定义：

CREATE TABLE `tbl` (
  `id` int NOT NULL AUTO_INCREMENT,
  `pid` int DEFAULT NULL,
  `processed` tinyint DEFAULT NULL,
  `time` datetime DEFAULT NULL,
  `created_on` datetime DEFAULT NULL,
  `updated_on` datetime DEFAULT NULL,

  `other` varchar(10) NOT NULL,
  
  PRIMARY KEY (`id`),
  KEY `ix_other` (`other`),
  KEY `ix_processed` (`processed`),
  KEY `ix_time` (`time`),
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

我还应该注意到，有许多行（目前超过5000行，但数量在一天中不断变化）使用processed=null, pid=null，但将来使用time。

**添加：**更多的上下文，根据评论：

目前这个表中有500多万行。这是一种队列，我们每个月得到大约140万行。最早的排在二月。当time在未来时，它几乎总是在同一天。
pid和processed与队列相关。当插入一行（作业）时，pid和processed都是null。time是应该执行作业的时间（某些作业只需要在指定的时刻之后执行;其他需要尽快执行）。
当其中一个处理器准备好执行某些作业时，它首先运行第一个查询，并通过将其PID（Linux进程ID）设置为这些行来为自己“保留”一批10个作业。然后，它选择这些行并逐个处理它们，完成时设置processed标志（第二个查询）。
然而，有时这两个查询会发生冲突，导致死锁。

mysql

3条答案

按热度按时间
vsnjm48y

vsnjm48y1#

我对锁定和死锁的理解非常有限，所以这过于简单，因为我不确定“何时请求哪个锁”（初始二级索引扫描，PRIMARY，然后其他二级索引受更新影响？）通过 mainUPDATE查询：
1.会话1发送初始UPDATE查询，选择ix_processed索引作为最具选择性的索引，并开始锁定索引 rows，直到收集到足够的行来满足查询。
1.会话2发送UPDATE on PK查询，该查询立即获取聚集索引中 row 上的x锁。
1.会话2尝试将更新应用于processed列，该列需要ix_processed上的x锁，但它已被会话1锁定，因此它将等待。
1.会话1尝试为1.中收集的行获取聚集索引上的x锁，但正在更新的行被会话2（2.）锁定，因此它将等待。

  1. 💣死锁💣
    您当前的UPDATE需要的锁比您想象的要多得多。
    当使用默认的可重复读取隔离级别（select @@session.tx_isolation）时，排他锁将被放置在为UPDATE检查的每一行上，而不仅仅是那些被更新的行。
    你可以用如下代码来测试：
START TRANSACTION;

UPDATE tbl 
    SET pid = 68111
WHERE pid IS NULL 
AND processed IS NULL 
AND `time` <= now() 
LIMIT 10;

SELECT dl.object_name, dl.index_name, dl.lock_type, dl.lock_mode, dl.lock_status, dl.lock_data, tbl.*
FROM performance_schema.data_locks dl
JOIN performance_schema.threads t ON dl.THREAD_ID = t.THREAD_ID
JOIN tbl ON tbl.id = dl.lock_data
WHERE dl.object_name = 'tbl'
AND dl.index_name = 'PRIMARY'
AND t.PROCESSLIST_ID = CONNECTION_ID();

ROLLBACK;

在(processed, pid, time)上添加复合索引将把UPDATE语句所需的锁数量减少到10个。

ALTER TABLE tbl ADD INDEX ix_processed_pid_time (processed, pid, time);

如果不想添加索引，那么可以尝试为UPDATE中的processed列设置一个独特的值（可能是-2），因为这意味着它将立即从后续更新中排除。
如果你使用的是MySQL 8，另一种方法可能是使用SELECT ... FOR UPDATE SKIP LOCKED：

START TRANSACTION;

SELECT id FROM tbl
WHERE pid IS NULL AND processed IS NULL AND `time` <= NOW()
LIMIT 10
FOR UPDATE SKIP LOCKED;

UPDATE tbl
    SET pid = 68111,
        processed = -2
WHERE id IN ( ... );

COMMIT;
赞(0）分享  回复(0）举报  2023-06-21
qij5mzcb

qij5mzcb2#

“……理解……”
如果没有合适的索引（请参阅user1191247答案），将锁定太多的行，从而导致死锁。
如果你有一个最新版本的MySQL，EXPLAIN UPDATE ...将提供更多关于正在使用的索引的信息。
（我怀疑使用子查询是否有帮助。）

赞(0）分享  回复(0）举报  2023-06-21
mhd8tkvw

mhd8tkvw3#

我会建议更新你的更新

UPDATE db.tbl 
    SET pid = 68111
WHERE pid IS NULL 
AND processed IS NULL 
AND `time` <= now() 
LIMIT 10

要更新ID IN（选择）。至少我是这么想的。更新在尝试收集和弄清楚它应该更新什么时锁定。通过预先查询您想要作为选择的内容，并根据这些ID进行更新，选择不会锁定，完成获取所需的少数记录，然后通过它们的密钥ID将更新应用于它们。
因此，时间索引首先用于选择（应该是快速的），然后根据从选择返回的PKID完成更新。就像

UPDATE db.tbl 
   SET pid = 68111
   WHERE id in ( select t2.id
                    from db.tbl t2
                    where t2.time <= now()
                      and t2.pid is null
                      and t2.processed is null 
                    limit 10 )

现在，为了优化Select查询，我需要一个索引来包含它需要的部分，而不必转到原始数据页面。因此，我将在TIME列上创建索引，而不是只在TIME列上创建索引。

(time, pid, processed, id )

所以整个where是限定的，但也有返回外部更新WHERE ID IN条件的ID。

赞(0）分享  回复(0）举报  2023-06-21
我来回答

相关问题

    查看更多

    热门标签

    更多
    JavaquerypythonNode开发语言requestUtil数据库Table后端算法LoggerMessageElementParser

    最新问答

    更多
    • 蜀ICP备13028337号-1 大数据知识库 https://www.saoniuhuo.com © All rights reserved
    • 本站内容来源互联网,如果侵犯您的权益请联系我们删除, 联系方式：448109455@qq.com