MySQL中的触发器和表锁定 [英] Triggers and table lock in 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.

现在，这些触发器将在多线程环境中运行，在该环境中，我可能可以并发访问表. 我希望可以做这样的事情，但是它是被禁止的(错误:错误代码:1314.存储过程中不允许使用锁):

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):

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 ;

使用这些LOCK(或其他构造)实现的目标是:

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

1. 避免同时运行两个触发器写入AlarmCount表并更新相关记录(我想我可能有两个触发器针对不同的警报表记录运行，以更新相同的AlarmCount记录)
2. 确保同时未修改AlarmMembership表(例如，同时删除目标MemberId).

任何建议都非常欢迎！

推荐答案

我认为处理此问题的最佳方法是使用此处描述的SELECT ... FOR UPDATE模式:

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

供参考:

让我们看另一个示例:在 我们用来为每个表分配唯一标识符的表child_codes 子项添加到表子项中.两者都不是一个好主意 一致读取或共享模式读取以读取当前值 计数器，因为数据库的两个用户随后可能会看到相同的值 计数器，如果有两个用户尝试，则会发生重复密钥错误 将具有相同标识符的子代添加到表中.

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.

在这里，锁定共享模式"不是一个好的解决方案，因为如果有两个用户 同时读取计数器，其中至少有一个以 尝试更新计数器时出现死锁.

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.

要实现读取和递增计数器，请先执行 使用FOR UPDATE锁定计数器的读取，然后递增 柜台.例如:

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:

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

SELECT ... FOR UPDATE读取最新的可用数据，并在读取的每一行上设置排他锁.因此，它设置了与行中搜索的SQL UPDATE相同的锁.

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锁定行以进行更新仅适用 当自动提交被禁用时(通过使用 开始交易或通过将自动提交设置为0.如果自动提交为 启用后，符合规范的行不会被锁定.

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.

因此，在您的情况下，您将替换

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;

类似

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 是:

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:

前面的描述仅是SELECT ... FOR的示例. 更新工作.在MySQL中，生成唯一的特定任务 实际上，仅通过一次访问即可完成标识符 表格:

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:

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

SELECT语句仅检索标识符信息(特定于当前 联系).它不访问任何表.

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

换句话说，您可能只需要访问表一次就可以进一步优化此模式...但是同样，您对架构的某些细节我不太了解，我不确定是否可以提供实际的您需要的陈述.我确实认为，如果您看一下SELECT ... FOR UPDATE，您将看到该模式归结为什么，以及在环境中进行此工作所需要做的事情.

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.

我还要提到，您需要考虑一些存储引擎环境和事务隔离级别.此处有一个关于SO的非常非常好的讨论:何时使用SELECT. ..要更新吗?

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?

希望这会有所帮助！

这篇关于MySQL中的触发器和表锁定的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！