本文介绍了MySQL中的触发器和表锁的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!

问题描述

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

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

DELIMITER $$如果存在则删除触发器 del_alarmCount$$CREATE TRIGGER del_alarmCount AFTER DELETE ON Alarm每行开始设置自动提交=0;锁定表 AlarmCount 写入,AlarmMembership 读取;更新 AlarmCount SET num = num - 1WHERE RuleId = OLD.RuleId AND成员 ID = 0 ANDIsResolved = OLD.IsResolved;更新 AlarmCount SET num = num - 1WHERE RuleId = OLD.RuleId ANDIsResolved = OLD.IsResolved ANDMemberId IN (SELECT MemberId FROM AlarmMembership WHERE AlarmId=OLD.Id);犯罪;解锁表;结束 $$分隔符;

使用这些锁定(或替代结构)实现的目标是:

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

非常欢迎任何建议!

解决方案

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

供参考:

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

在这里,LOCK IN SHARE MODE 不是一个好的解决方案,因为如果两个用户同时读取计数器,其中至少一个最终在尝试更新计数器时出现死锁.

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

 SELECT counter_field FROM child_codes FOR UPDATE;更新 child_codesSET counter_field = counter_field + 1;

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

...

注意使用 SELECT FOR UPDATE 锁定行以进行更新仅适用当自动提交被禁用时(通过开始事务START TRANSACTION 或通过将 autocommit 设置为 0.如果 autocommit 是启用,匹配规范的行不会被锁定.

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

LOCK TABLES AlarmCount WRITE, AlarmMembership READ;更新 AlarmCount SET num = num - 1WHERE RuleId = OLD.RuleId AND成员 ID = 0 ANDIsResolved = OLD.IsResolved;

类似的东西

SELECT num FROM AlarmCount WHERE RuleId = OLD.RuleId AND成员 ID = 0 ANDIsResolved = OLD.IsResolved FOR 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更新工作.在 MySQL 中,生成唯一的特定任务标识符实际上可以仅使用对表:

UPDATE child_codes SET counter_field = LAST_INSERT_ID(counter_field +1);选择 LAST_INSERT_ID();

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

换句话说,您可以通过仅访问一次表来进一步优化此模式……但同样,关于您的架构的一些详细信息我不太了解,而且我不确定我是否可以提供实际你需要的声明.不过,我确实认为,如果您看一下 SELECT ... FOR UPDATE,您会看到该模式归结为什么,以及您需要做什么才能使其在您的环境中工作.

我还应该提到,您需要考虑一些存储引擎环境和事务隔离级别.这里有一个关于这个主题的非常非常好的讨论:何时使用 SELECT ... 更新?

希望这有帮助!

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

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:

  1. 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)
  2. Make sure AlarmMembership table does not get modified meanwhile (e.g. the target MemberId gets deleted meanwhile).

Any advice is very welcome!

解决方案

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

For reference:

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

...

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:

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

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.

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?

Hope this helps!

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

08-03 21:36
查看更多