MySQL中的触发器和表锁

Scenario: I have some triggers that keep track of number of records of one table, together with other useful information. These triggers are fired upon add/delete/update on this table and take care of writing this information in another complementary table.

场景：我有一些触发器可以跟踪一个表的记录数以及其他有用信息。这些触发器在此表上添加/删除/更新时触发，并负责在另一个补充表中写入此信息。

Now these triggers will run on a multi-threaded environment where possibly I may have concurrent access to tables. I wish I could make something like this, but it is forbidden (ERROR: Error Code: 1314. LOCK is not allowed in stored procedures):

现在，这些触发器将在多线程环境中运行，在这种环境中，我可能可以同时访问表。我希望我可以做这样的事情，但它被禁止（错误：错误代码：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 ;

The goals to achieve with these LOCKS (or alternative constructs) are:

使用这些LOCKS（或替代构造）实现的目标是：

Avoid two triggers running simultaneously write on AlarmCount table and update related records (I guess I may have two triggers running for different records of Alarm table updating the same record of AlarmCount)
避免两个触发器同时运行写入AlarmCount表并更新相关记录（我想我可能有两个触发器运行，不同的Alarm表记录更新了相同的AlarmCount记录）
Make sure AlarmMembership table does not get modified meanwhile (e.g. the target MemberId gets deleted meanwhile).
确保同时不修改AlarmMembership表（例如同时删除目标MemberId）。

Any advice is very welcome!

任何建议都非常欢迎！

1 个解决方案

#1

I think the best way to handle this would be to use the SELECT ... FOR UPDATE pattern described here: http://dev.mysql.com/doc/refman/5.0/en/innodb-locking-reads.html

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

For reference:

以供参考：

Let us look at another example: We have an integer counter field in a table child_codes that we use to assign a unique identifier to each child added to table child. It is not a good idea to use either consistent read or a shared mode read to read the present value of the counter because two users of the database may then see the same value for the counter, and a duplicate-key error occurs if two users attempt to add children with the same identifier to the table.

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

Here, LOCK IN SHARE MODE is not a good solution because if two users read the counter at the same time, at least one of them ends up in deadlock when it attempts to update the counter.

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

To implement reading and incrementing the counter, first perform a locking read of the counter using FOR UPDATE, and then increment the counter. For example:

要实现读取和递增计数器，首先使用FOR UPDATE执行计数器的锁定读取，然后递增计数器。例如：

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

A SELECT ... FOR UPDATE reads the latest available data, setting exclusive locks on each row > it reads. Thus, it sets the same locks a searched SQL UPDATE would set on the rows.

SELECT ... FOR UPDATE读取最新的可用数据，在每行上设置独占锁>它读取。因此，它设置搜索的SQL UPDATE将在行上设置的相同锁。

. . .

。。。

Note Locking of rows for update using SELECT FOR UPDATE only applies when autocommit is disabled (either by beginning transaction with START TRANSACTION or by setting autocommit to 0. If autocommit is enabled, the rows matching the specification are not locked.

注意使用SELECT FOR UPDATE锁定要更新的行仅适用于禁用自动提交时（通过使用START TRANSACTION开始事务或将自动提交设置为0.如果启用了自动提交，则不会锁定与规范匹配的行。

So in your case, you would replace

所以在你的情况下，你会替换

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

With something like

有类似的东西

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

I say "something like" because it's not entirely clear to me what OLD.RuleId and OLD.IsResolved is referencing. Also worth noting from http://dev.mysql.com/doc/refman/5.0/en/innodb-locking-reads.html is:

我说“类似”，因为我并不完全清楚OLD.RuleId和OLD.IsResolved正在引用什么。另外值得注意的是http://dev.mysql.com/doc/refman/5.0/en/innodb-locking-reads.html是：

The preceding description is merely an example of how SELECT ... FOR UPDATE works. In MySQL, the specific task of generating a unique identifier actually can be accomplished using only a single access to the table:

前面的描述仅仅是SELECT ... FOR UPDATE如何工作的一个例子。在MySQL中，生成唯一标识符的具体任务实际上只需对表进行一次访问即可完成：

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

The SELECT statement merely retrieves the identifier information (specific to the current connection). It does not access any table.

SELECT语句仅检索标识符信息（特定于当前连接）。它不访问任何表。

In other words, you can probably optimize this pattern further by only accessing the table once... but again there's some details about your schema that I don't quite follow, and I'm not sure I could provide the actual statement you'd need. I do think if you take a look SELECT ... FOR UPDATE, though, that you'll see what the pattern boils down to, and what you need to do to make this work in your environment.

换句话说，你可以通过只访问一次表来进一步优化这种模式......但是还有一些关于你的模式的细节我不太关注，而且我不确定我能提供你的实际陈述'需要。我确实认为，如果你看看SELECT ... FOR UPDATE，你会看到模式归结为什么，以及你需要做些什么才能使你的环境工作。

I should mention as well that there are some storage engine environment and transaction isolation levels that you'll want to consider. There is a very, very good discussion on SO on this topic here: When to use SELECT ... FOR UPDATE?

我还应该提一下，您需要考虑一些存储引擎环境和事务隔离级别。关于此主题的SO有一个非常非常好的讨论：何时使用SELECT ... FOR UPDATE？

Hope this helps!

希望这可以帮助！

#1