我需要选择,做操作和更新很多数据不到3分钟。并决定创建某种锁定机制,使其能够运行单独的进程(并行),并且每个进程都应该锁定、选择和更新自己的行。
为了使之成为可能,决定将列worker_id添加到表中。
表结构:
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 (process_number从0开始,所以首先是0,最后是4)的不同进程选择同一行。
每个过程都遵循以下步骤:
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将当前进程id设置为前1000行的worker_idselect * from offers where worker_id =: process_id order by offer_id asc limit 1000offer_id存储到变量,准备好的数据存储到另一个变量以进行进一步更新and offer_id > :last_selected_id运行与步骤2中相同的查询,以选择下1000行update offers set worker_id = null where worker_id =: process_id以及其他4个过程的相同步骤
这里的问题是,当所有5个进程同时运行从步骤1到锁行(设置为worker_id)的查询时,都会出现死锁,但是每个进程都会根据公式对自己的行进行锁定。我试图将事务隔离级别设置为READ COMMITED,但问题仍然相同。
我是锁机制的新手,我需要帮助防止这里的死锁,或者创建更好的机制
发布于 2018-08-21 00:01:21
表达式offer_id%5 = :process_number不能使用索引,因此只能扫描第一个条件worker_id is null匹配的所有行。
你可以用两个窗口来证明这一点:
mysql1> begin;
mysql1> set @p=1;
mysql1> update offers set worker_id = @p where worker_id is null and offer_id%5 = @p;还不要在窗口1中提交事务。
mysql2> set @p=2;
mysql2> update offers set worker_id = @p where worker_id is null and offer_id%5 = @p;
...waits for about 50 seconds, or value of innodb_lock_wait_timeout, then...
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction这说明每个并发会话都锁定重叠的行集,而不仅仅是与模数表达式匹配的行。因此,会话在彼此的锁上排队。
如果你把所有的步骤都投入到一笔交易中去,情况会变得更糟,比如斯隆- This建议。让每个工作人员的工作时间更长,将使他们只持有更长的锁,并进一步推迟其他进程等待这些锁。
我不明白updated_at字段是如何导致这个问题的,因为我还在更新其他字段
我不确定,因为您还没有发布来自InnoDB的SHOW ENGINE INNODB STATUS死锁诊断信息。
我确实注意到,您的表有一个次要的唯一键,它还需要锁。由于锁分配的非原子性,出现了一些死锁情况。
Worker 1 Worker 2
UPDATE SET worker_id = 1
(acquires locks on PK)
UPDATE SET worker_id = 2
(waits for PK locks held by worker 1)
(waits for locks on UNIQUE KEY)因此,worker 1和worker 2都可以互相等待,并进入死锁。
这只是猜测而已。另一种可能是ORM正在对updated_at列进行第二次更新,这为竞争条件带来了另一个机会。我还没有完全解决这个问题,但我认为这是可能的。
下面是关于一种可以避免这些问题的不同制度的建议:
还有另一个问题,就是您没有真正平衡工作与流程之间的关系,以实现最佳的完成时间。当你用模数除以每个组的报价时,可能不会有相同数量的报价。而且,无论如何,每一份报价都可能不需要同样的时间来处理。所以你的一些工人可以完成,没有什么可做的,而最后一个工人仍在处理它的工作。
您可以解决两个问题,锁定和负载平衡:
以下列方式更改表列:
ALTER TABLE offers
CHANGE worker_id work_state ENUM('todo', 'in progress', 'done') NOT NULL DEFAULT 'todo',
ADD INDEX (work_state),
ADD COLUMN updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
ADD INDEX (updated_at);创建定期从表中读取的ONE进程,并将“todo”状态下的提供的主键id值添加到消息队列中。所有的报价,不管它们的offer_id值如何,都以同样的方式排队。
SELECT id FROM offers WHERE work_state = 'todo'
/* push each id onto the queue */然后,每个工作人员都可以每次从消息队列中提取一个id。工作人员对每个id执行以下步骤:
UPDATE offers SET work_state = 'in progress' WHERE id = :idUPDATE offers SET work_state = 'done' WHERE id = :id这些工作人员的查询一次只引用一个邀请,并且通过主键来处理提供,这将使用PK索引,并且一次只锁定一行。
一旦它完成了一个提议,然后工人从队列中提取下一个提议。
这样,工人们就会同时完成工作,工作就会比工人更好地平衡。此外,您可以在任何时候启动或停止工人,并且您不关心他们是什么工人号码,因为您的报价不需要由与offer_id模数相同的工人处理。
当员工完成所有的报价后,消息队列将为空。大多数消息队列允许工作人员进行阻塞读取,因此当队列为空时,工作人员将只等待读取返回。当您使用数据库时,员工必须经常轮询新工作。
工人有可能在工作中失败,永远不会表示“已完成”。您需要定期检查孤儿的报价。假设他们不会完成,并标记他们的状态“待办”。
UPDATE offers SET work_state = 'todo'
WHERE work_state = 'in progress' AND updated_at < NOW() - INTERVAL 5 MINUTE选择间隔长度,这样就可以肯定,任何工人都会在那个时候完成它,除非有什么地方出了问题。您可能会在dispatcher查询当前报价之前进行“重置”,因此已经忘记的报价将重新排队。
发布于 2018-08-21 00:00:16
我发现了问题。这是因为在执行更新操作时,我的ORM默认是更新时间戳字段(为了简化上面的示例,我从表结构中删除了它们),并且在我关闭它之后死锁消失了。但是,我仍然不明白updated_at字段是如何导致这个问题的,因为我还在更新其他字段
https://stackoverflow.com/questions/51939560
复制相似问题