MySQL中的触发器和表锁

Question

MySQL中的触发器和表锁

17

场景：我有一些触发器，用于跟踪一个表的记录数量和其他有用信息。这些触发器会在对该表进行添加/删除/更新时触发，并负责将这些信息写入另一个补充表中。

现在这些触发器将在一个多线程环境中运行，可能会对表进行并发访问。我希望能做出类似于这样的东西，但被禁止了（错误：错误代码：1314。不允许在存储过程中使用LOCK）：

DELIMITER $$
DROP TRIGGER IF EXISTS del_alarmCount$$
CREATE TRIGGER del_alarmCount AFTER DELETE ON Alarm
FOR EACH ROW
BEGIN
SET autocommit=0;
LOCK TABLES AlarmCount WRITE, AlarmMembership READ;
  UPDATE AlarmCount SET num = num - 1 
  WHERE RuleId = OLD.RuleId AND
      MemberId = 0 AND
      IsResolved = OLD.IsResolved;

  UPDATE AlarmCount SET num = num - 1 
  WHERE RuleId = OLD.RuleId AND
      IsResolved = OLD.IsResolved AND
      MemberId IN (SELECT MemberId FROM AlarmMembership WHERE AlarmId=OLD.Id);
COMMIT;
UNLOCK TABLES;
END $$
DELIMITER ;

通过这些锁定（或替代结构）要实现的目标是：

避免两个触发器同时运行写入AlarmCount表并更新相关记录（我猜可能有两个触发器运行，分别针对Alarm表中不同的记录，但更新AlarmCount的同一条记录）。
确保在此期间不会修改AlarmMembership表（例如，目标MemberId在此期间被删除）。

欢迎任何建议！

- DocDbg

1个回答

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- Evan Volgas · Accepted Answer

我认为处理这个问题的最佳方法是使用在这里描述的SELECT ... FOR UPDATE模式：http://dev.mysql.com/doc/refman/5.0/en/innodb-locking-reads.html

参考资料：

让我们看另一个例子：我们有一个整数计数器字段在一个名为child_codes的表中，我们用它来为添加到child表中的每个子项分配唯一标识符。使用一致性读或共享模式读取计数器的当前值并不是一个好主意，因为数据库的两个用户可能会看到计数器的相同值，如果两个用户试图将具有相同标识符的子项添加到表中，则会出现重复键错误。

在这里，LOCK IN SHARE MODE不是一个好的解决方案，因为如果两个用户同时读取计数器，其中至少一个会在尝试更新计数器时陷入死锁。

要实现读取和增加计数器，请先使用FOR UPDATE进行锁定读取计数器，然后再增加计数器。例如：

 SELECT counter_field FROM child_codes FOR UPDATE; UPDATE child_codes
 SET counter_field = counter_field + 1;

使用 SELECT ... FOR UPDATE 会读取最新可用的数据，并在读取每一行时设置排他锁。因此，它会像搜索 SQL UPDATE 语句所设定的那样，在行上设置相同的锁。

. . .

注意：仅当自动提交被禁用（通过使用 START TRANSACTION 开始事务或将 autocommit 设置为 0）时，才可以使用 SELECT FOR UPDATE 锁定行进行更新。如果启用了自动提交，则不会锁定符合规范的行。

因此，在您的情况下，您需要替换：

LOCK TABLES AlarmCount WRITE, AlarmMembership READ;
  UPDATE AlarmCount SET num = num - 1 
  WHERE RuleId = OLD.RuleId AND
      MemberId = 0 AND
      IsResolved = OLD.IsResolved;

有类似的东西

SELECT num FROM AlarmCount WHERE RuleId = OLD.RuleId AND
          MemberId = 0 AND
          IsResolved = OLD.IsResolved FOR UPDATE;
UPDATE AlarmCount SET num = num - 1;

我这样说是因为我不完全清楚OLD.RuleId和OLD.IsResolved在引用什么。此外，值得注意的是来自http://dev.mysql.com/doc/refman/5.0/en/innodb-locking-reads.html:

上述描述仅是SELECT ... FOR UPDATE如何工作的示例。在MySQL中，实际上可以仅使用一次访问表来生成唯一标识符：

UPDATE child_codes SET counter_field = LAST_INSERT_ID(counter_field +
1); 
SELECT LAST_INSERT_ID();

SELECT语句仅检索标识符信息（特定于当前连接）。它不访问任何表。换句话说，您可能可以通过仅访问一次表来进一步优化此模式......但是关于架构的一些细节我并不完全了解，而且我不确定我能够提供您需要的实际语句。我认为如果您查看SELECT ... FOR UPDATE，您将看到该模式的要点以及在您的环境中所需做的事情。我也应该提到，有一些存储引擎环境和事务隔离级别需要考虑。关于这个问题，在SO上有一个非常好的讨论，链接在这里：When to use SELECT ... FOR UPDATE? 希望这能帮助到你！