主页 / dba / 问题 / 10370
Accepted
Alan
Asked: 2012-01-08 18:07:54 +0800 CST

并发 MySQL 更新与 InnoDB 挂起（在 Amazon RDS 上）

  • 772

我遇到了一个问题，即同时执行多个 MySQL 更新将锁定并需要几分钟才能完成。我正在使用 InnoDB，所以我很困惑为什么会发生这种情况，因为每次更新只更新 1 行。我还使用了一个 m2.4xlarge RDS 实例（它们是最大的）。

卡住更新

这就是我正在做的事情：我有一个包含大约 100M 行的表，其中“视图”是一列（已编入索引），我想更新大约 1M 行的视图。在几个不同的服务器上，我有一个这样的循环，其中每个服务器都有自己的一组要更新的行（伪代码）：

mysql("set autocommit=0");
mysql("start transaction");

foreach($rows as $row) {
    mysql("update table set views=views+1 where id=$row[id]");
}

mysql("commit");

这将遍历所有需要更新的行。当服务器数量很少时（例如 4 台左右），它可以完美运行，但是当它增加到 10 台以上时，更新开始同时处于“正在更新”状态。没有什么说它正在等待锁定，它只是“更新”。这发生了大约 5 分钟，它最终将进行更新并继续循环并最终再次发生。

我不是在寻找其他方法来进行更新。拥有像 tmp 表和

update table,tmp_table set table.views = table.views+tmp_table.views where
  table.id = tmp_table.id

锁定所有正在更新的行，直到它们全部完成（可能是几个小时），这对我不起作用。他们必须在这些可怕的循环中。

我想知道为什么他们会陷入“更新”状态，以及我能做些什么来防止它。

tldr; 拥有 10 多个“更新”循环最终将同时锁定所有正在完成的更新，原因不明，直到他们决定最终进行更新并继续通过循环，只是为了它在几秒钟后再次发生。

显示变量：http : //pastebin.com/NdmAeJrz

显示引擎 INNODB 状态： http: //pastebin.com/Ubwu4F1h

mysql innodb

3 个回答

  1. Best Answer

    我不是在寻找其他方法来进行更新。拥有像 tmp 表这样的东西 [将] 锁定所有正在更新的行，直到它们全部完成（可能是几个小时），这对我不起作用。他们必须在这些可怕的循环中。

    我不同意。

    RDBMS 的优势在于执行集合操作，例如“更新所有这些行 plz”。鉴于此，您的直觉应该告诉您，这些“可怕的循环”不是最好的方法，除非在非常罕见的情况下。

    让我们看一下您当前的更新逻辑并了解它在做什么。

    首先，脚本中的set autocommit=0行是不必要的。因为您在使用 之后立即显式打开事务start transaction，所以autocommit 会自动禁用COMMIT，直到您使用or结束事务ROLLBACK。

    现在是逻辑的核心：您已经将所有这些单独的更新包装在一个大事务中的循环中。如果您在迭代更新背后的意图是减少锁定并增加并发性，则包装事务会破坏该意图。MySQL 必须在它更新的每一行上保持锁，直到事务提交，这样如果事务失败或被取消，它就可以一次将它们全部回滚。此外，不是提前知道它将要锁定这个范围的行（这将使 MySQL 能够以适当的粒度发出锁），引擎被迫快速发出大量的行级锁。鉴于您要更新 100 万行，这对引擎来说是一个巨大的负担。

    我提出两个解决方案：

    1. 打开autocommit并删除事务包装器。 MySQL 将能够在完成更新行后立即释放每个行锁。它仍然被迫在短时间内发布和释放大量锁，所以我怀疑这是否适合您。此外，如果在循环的中途发生了一些错误，则不会回滚任何内容，因为工作不是事务绑定的。

    2. 在临时表中批量更新。你提到然后驳回了这个解决方案，但我敢打赌它会表现最好。你已经试过了吗？我将首先测试完整的百万行更新。如果这花费的时间太长，那么将工作分批成越来越小的块，直到找到最佳点：批次足够大，可以快速完成全部工作，但没有单个批次会阻塞其他过程太长时间。这是DBA 在实时操作期间必须修改大量行时使用的常用技术。请记住，由于您的目标是最大化您的并发性，因此请继续autocommit进行并且不要将任何这些工作包装到大量事务中，以便 MySQL 尽快释放其锁。

      请注意，随着批次逐渐变小，此解决方案最终会接近第一个解决方案。这就是为什么我相信这个解决方案会表现得更好：当数据库引擎可以将其工作分成块时，它就会飞起来。

    • 11

  2. 即使使用 InnoDB，也总是存在迫在眉睫的死锁威胁。在这种特殊情况下，我甚至可以在 InnoDB 中看到行首先进入死锁情况，因为您正在通过视图表的 PRIMARY KEY 更新数据。这将在聚集索引内启动积极锁定。

    你可以看到这个锁定使用SHOW ENGINE INNODB STATUS\G

    我回答了三个非常棘手的问题来解决类似的问题。

    SELECT/UPDATE 查询可以在通过 PRIMARY KEY 更新时对gen_clust_index（即聚集索引）执行锁定。

    以下是我与提出这些问题的人@RedBlueThing积极研究的三个 DBA Stack Exchanges问题。@RedBlueThing 为他的问题找到了解决方法。

    在所有这三个问题中，行锁都涉及同一张表的聚集索引中的相应锁。涉及锁定行的相邻键，因此导致了问题。

    故事的寓意：仍然有可能与 InnoDB 发生死锁。为单独的行级锁设置适当的算法并单独更新有问题的行比任何时候通过多个行级锁进行批量更新要安全得多。

    确保autocommit=1在以这种方式大量更新表时使用。即便如此，在 InnoDB 中更新一行将导致各种MVCC数据被包围在该行的先前内容周围以允许并发事务。鉴于 UPDATE 的性质，将生成大量 MVCC 数据。

    • 1

  3. 查看您的 innodb 状态，我看到带有视图表的最新死锁也是由于此查询：

    update low_priority reddit_new 
join images_new on images_new.hash = reddit_new.hash 
set reddit_new.score = images_new.views 
where date > date(now() - interval 1 day)

    是否被reddit_new.date索引？两个表中的哈希列是否被索引？

    • 0

相关问题