首页
学习
活动
专区
圈层
工具
发布
社区首页 >问答首页 >更新时mysql死锁

问更新时mysql死锁
EN

Stack Overflow用户
提问于 2018-08-20 23:02:01
回答 2查看 1.4K关注 0票数 0

我需要选择,做操作和更新很多数据不到3分钟。并决定创建某种锁定机制,使其能够运行单独的进程(并行),并且每个进程都应该锁定、选择和更新自己的行。

为了使之成为可能,决定将列worker_id添加到表中。

表结构:

代码语言:javascript
复制
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)的不同进程选择同一行。

每个过程都遵循以下步骤:

  1. 使用查询: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_id
  2. 选择那些行:select * from offers where worker_id =: process_id order by offer_id asc limit 1000
  3. 使用数据进行操作,将最后的offer_id存储到变量,准备好的数据存储到另一个变量以进行进一步更新
  4. 从步骤1运行相同的查询以锁定下1000行
  5. 使用附加where子句and offer_id > :last_selected_id运行与步骤2中相同的查询,以选择下1000行
  6. 在循环中执行相同的步骤,直到锁定所有行为止。
  7. 删除所有锁,update offers set worker_id = null where worker_id =: process_id
  8. 运行查询以更新所有收集的数据。

以及其他4个过程的相同步骤

这里的问题是,当所有5个进程同时运行从步骤1到锁行(设置为worker_id)的查询时,都会出现死锁,但是每个进程都会根据公式对自己的行进行锁定。我试图将事务隔离级别设置为READ COMMITED,但问题仍然相同。

我是锁机制的新手,我需要帮助防止这里的死锁,或者创建更好的机制

EN

回答 2

Stack Overflow用户

发布于 2018-08-21 00:01:21

表达式offer_id%5 = :process_number不能使用索引,因此只能扫描第一个条件worker_id is null匹配的所有行。

你可以用两个窗口来证明这一点:

代码语言:javascript
复制
mysql1> begin;
mysql1> set @p=1;
mysql1> update offers set worker_id = @p where worker_id is null and offer_id%5 = @p;

还不要在窗口1中提交事务。

代码语言:javascript
复制
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死锁诊断信息。

我确实注意到,您的表有一个次要的唯一键,它还需要锁。由于锁分配的非原子性,出现了一些死锁情况。

代码语言:javascript
复制
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列进行第二次更新,这为竞争条件带来了另一个机会。我还没有完全解决这个问题,但我认为这是可能的。

下面是关于一种可以避免这些问题的不同制度的建议:

还有另一个问题,就是您没有真正平衡工作与流程之间的关系,以实现最佳的完成时间。当你用模数除以每个组的报价时,可能不会有相同数量的报价。而且,无论如何,每一份报价都可能不需要同样的时间来处理。所以你的一些工人可以完成,没有什么可做的,而最后一个工人仍在处理它的工作。

您可以解决两个问题,锁定和负载平衡:

以下列方式更改表列:

代码语言:javascript
复制
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值如何,都以同样的方式排队。

代码语言:javascript
复制
SELECT id FROM offers WHERE work_state = 'todo'
/* push each id onto the queue */

然后,每个工作人员都可以每次从消息队列中提取一个id。工作人员对每个id执行以下步骤:

  1. UPDATE offers SET work_state = 'in progress' WHERE id = :id
  2. 工人为了一份工作而完成这项工作。
  3. UPDATE offers SET work_state = 'done' WHERE id = :id

这些工作人员的查询一次只引用一个邀请,并且通过主键来处理提供,这将使用PK索引,并且一次只锁定一行。

一旦它完成了一个提议,然后工人从队列中提取下一个提议。

这样,工人们就会同时完成工作,工作就会比工人更好地平衡。此外,您可以在任何时候启动或停止工人,并且您不关心他们是什么工人号码,因为您的报价不需要由与offer_id模数相同的工人处理。

当员工完成所有的报价后,消息队列将为空。大多数消息队列允许工作人员进行阻塞读取,因此当队列为空时,工作人员将只等待读取返回。当您使用数据库时,员工必须经常轮询新工作。

工人有可能在工作中失败,永远不会表示“已完成”。您需要定期检查孤儿的报价。假设他们不会完成,并标记他们的状态“待办”。

代码语言:javascript
复制
UPDATE offers SET work_state = 'todo' 
WHERE work_state = 'in progress' AND updated_at < NOW() - INTERVAL 5 MINUTE

选择间隔长度,这样就可以肯定,任何工人都会在那个时候完成它,除非有什么地方出了问题。您可能会在dispatcher查询当前报价之前进行“重置”,因此已经忘记的报价将重新排队。

票数 1
EN

Stack Overflow用户

发布于 2018-08-21 00:00:16

我发现了问题。这是因为在执行更新操作时,我的ORM默认是更新时间戳字段(为了简化上面的示例,我从表结构中删除了它们),并且在我关闭它之后死锁消失了。但是,我仍然不明白updated_at字段是如何导致这个问题的,因为我还在更新其他字段

票数 0
EN
页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接:

https://stackoverflow.com/questions/51939560

复制
相关文章

相似问题

领券
问题归档专栏文章快讯文章归档关键词归档开发者手册归档开发者手册 Section 归档