我需要选择,使操纵和更新不到3分钟的大量数据。并决定创建某种锁定机制,使其能够运行单独的进程(并行),并且每个进程应该锁定、选择和更新自己的行。
为了使之成为可能,决定增加这一列 worker_id
到table上去。
表结构:
CREATE TABLE offers
(
id int(10) unsigned PRIMARY KEY NOT NULL AUTO_INCREMENT,
offer_id int(11) NOT NULL,
offer_sid varchar(255) NOT NULL,
offer_name varchar(255),
account_name varchar(255),
worker_id varchar(255),
);
CREATE UNIQUE INDEX offers_offer_id_offer_sid_unique ON offers (offer_id, offer_sid);
CREATE INDEX offers_offer_id_index ON offers (offer_id);
CREATE INDEX offers_offer_sid_index ON offers (offer_sid);
此外,我们决定从5个并行进程开始,不允许不同进程选择同一行,我们使用以下公式: offer_id % max_amount_of_processes = process_number
(进程编号从0开始,因此第一个是0,最后一个是4)
每个过程都遵循以下步骤:
套 worker_id
使用查询将当前进程id设置为前1000行: update offers set worker_id =: process_id where worker_id is null and offer_id%5 =: process_number order by offer_id asc limit 1000
选择这些行: select * from offers where worker_id =: process_id order by offer_id asc limit 1000
对数据进行操作,最后存储 offer_id
并将准备好的数据传输到另一个变量以供进一步更新
从步骤1运行相同的查询以锁定接下来的1000行
使用额外的where子句运行与步骤2中相同的查询 and offer_id > :last_selected_id
选择下1000行
在循环中执行相同的步骤,直到锁定所有行
拆下所有锁 update offers set worker_id = null where worker_id =: process_id
运行查询以更新所有收集的数据
其他4道工序步骤相同
这里的问题是,当所有5个进程同时运行步骤1中的查询以锁定行(set)时,会出现死锁 worker_id
)但是每个进程都对自己的行进行锁定,这取决于公式。我试图将事务隔离级别设置为 READ COMMITED
但还是一样的问题。
我是一个新手在锁定机制,我需要一个帮助,以防止这里死锁或创建更好的机制
2条答案
按热度按时间20jt8wwn1#
我发现了问题。这是因为在执行更新操作时,orm在默认情况下更新时间戳字段(为了简化上面的示例,我将它们从表结构中删除),关闭后死锁消失。但我还是不明白
updated_at
字段可能会导致问题,因为我仍在更新其他字段vh0rcniy2#
表达式
offer_id%5 = :process_number
无法使用索引,因此它只能扫描与第一个条件匹配的所有行,worker_id is null
.你可以用两个窗口来证明这一点:
不要在窗口1中提交事务。
这表明每个并发会话都会锁定重叠的行集,而不仅仅是匹配模表达式的行。所以会话排队时会碰到对方的锁。
如果你像@sloather建议的那样把所有步骤都放到一个事务中,情况会变得更糟。使每个工人的工作花费更长的时间将使他们只持有他们的锁的时间更长,并进一步延迟等待这些锁的其他进程。
我不明白更新的\u at字段是如何导致问题的,因为我仍在更新其他字段
我不确定,因为你还没有发布innodb死锁诊断
SHOW ENGINE INNODB STATUS
.我注意到您的表有一个辅助唯一键,这也需要锁。由于锁分配的非原子性,有时会出现死锁。
因此,worker 1和worker 2可以互相等待,并进入死锁。
这只是猜测。另一种可能性是orm正在对
updated_at
列,这将引入另一个竞争条件的机会。我心里还没想清楚,但我认为这是可能的。以下是为避免这些问题的不同系统提供的建议:
还有一个问题,你没有真正平衡工作与过程,以达到最佳的完成时间。按模数拆分时,每组中的报价数量可能不相等。而且每一个报价可能不会花费相同的时间来处理。因此,当最后一个工人仍在处理其工作时,您的一些工人可以完成工作而无事可做。
您可以同时解决锁定和负载平衡两个问题:
按以下方式更改表列:
创建一个进程,定期从表中读取数据,并添加主键
id
消息队列中处于“todo”状态的提供的值。所有的报价,不管其报价的id值是多少,都以相同的方式排队。然后每个工人都可以拉一个
id
一次从消息队列中删除。工人对每个id执行以下步骤:UPDATE offers SET work_state = 'in progress' WHERE id = :id
工人只为一份工作而工作。UPDATE offers SET work_state = 'done' WHERE id = :id
这些worker查询一次只引用一个offer,它们通过主键处理offer,主键将使用pk索引,一次只锁定一行。一旦它完成了一个报价,那么工人就从队列中提取下一个报价。
这样,工人们就可以同时完成工作,而且工作会比工人们更好地平衡。此外,您可以随时启动或停止员工,而且您不必关心他们的员工编号,因为您的招聘不需要由与招聘id模数相同的员工处理。
当工作人员完成所有提供时,消息队列将为空。大多数消息队列允许worker执行阻塞读取,因此当队列为空时,worker将只等待读取返回。当你使用一个数据库时,工人们必须经常投票寻找新的工作。
一个工人在工作中很可能会失败,而且永远不会把一份工作标为“完成”。你需要定期检查孤立的提议。假设它们不会完成,并将其状态标记为“todo”。
选择间隔长度,这样就可以确定,除非出了什么问题,否则任何工人都会在那个时候完成它。您可能会在dispatcher查询当前提供的todo之前执行此“重置”,以便将忘记的提供重新排队。