时间戳

首页 > 解决方案 > 选择相同外键行的多个事务导致锁定等待超时

mysql - 选择相同外键行的多个事务导致锁定等待超时

问题描述

我在用 sylius（symfony 电子商务）编写的应用程序中有一些重要的数据导入功能。

我们有一堆用于同步数据的 csv 文件。整个工作流程是用rabbitmq和supervisor processes实现的。我们为每个消费者提供多个流程。我在下图中展示的工作流程。

在此处输入图像描述

当插入第一个表的动作完成时，触发事件并触发第二行中的动作。

这就是过程出现问题的地方。第二行中的所有这些操作都选择插入到第一个操作中的表中的外键，所以我进入lock wait timeout error。

消息重新排队，但该过程似乎一次又一次地停止。

在这种情况下，我可以通过并发交易做些什么吗？

查询的形式是这样的，或多或少会根据用例稍作修改：

INSERT INTO product_variant(productid, other_attribute)
VALUES ((SELECT id FROM product WHERE ...), 'value')

标签： mysqldatabasetransactions

解决方案

你的插入过程是这样的。

 INSERT INTO products (this, that, something, else)
               VALUES (?,?,?,?);
 SELECT id FROM products WHERE this=? AND that=?;
 INSERT INTO product_variants ( product_id, col,...) 
                      VALUES   (<<that id you just SELECTED>>, ...);

换句话说，您有一系列 INSERT，然后是 SELECT。这是在繁忙的 DBMS 上造成死锁的工业秘诀。

尝试通过使用MySQL 的LAST_INSERT_ID()function来避免 SELECT 。它会在任何插入之后为您返回自动递增的 ID 值，通常是主键。做这样的事情：

 INSERT INTO products (this, that, something, else)
               VALUES (?,?,?,?);
 SET @product_id := LAST_INSERT_ID();
 INSERT INTO product_variants ( product_id, col,...) 
                      VALUES   (@product_id, ...);
 SET @product_variant_id := LAST_INSERT_ID();

然后，您无需将 INSERT 与 SELECT 穿插以获取 id 值。我的示例中的 SET 操作允许您将 id 值重新用于 INSERT 操作；下一个 INSERT 会覆盖 LAST_INSERT_ID 值，因此如果在后续 INSERT 中将其用作外键，则需要保留该值。

另外，尝试将您的 INSERT 序列包装在BEGIN TRANSACTION;和中COMMIT;。

此外，请尝试减少使用队列中数据的进程数量。矛盾的是，DBMS 上过多的并发操作会降低性能。并发肯定会增加死锁的机会。

编辑当然，所有相关的插入都必须在单个 DBMS 连接中完成，才能使 LAST_INSERT_ID 有用。

另一种可能的方法来检索外键的正确值，然后将其用于插入。

BEGIN TRANSACTION;
SELECT id INTO @fk
  FROM master_table
 WHERE whatever
   AND whatever
   FOR UPDATE;
 INSERT INTO detail_table (master_id, col, col, col)
                   VALUES (@fk, ?, ?);
 COMMIT;

此 SELECT ... FOR UPDATE 将事务锁放在您正在使用的主表的行上。如果您仔细遵循此模式，它应该会导致每个操作以相同的顺序获取所需的锁。这是防止死锁的工业配方。如果您需要从多个表中获取 FK 值来执行操作，请始终在 SELECT ... FOR UPDATE 语句中以相同的顺序提及表

但是，LAST_INSERT_ID 仍然是迄今为止完成这项工作的最佳性能和最可靠的方法。您应该考虑重构代码以尽可能使用它。

推荐阅读