问题:处理ON INSERT触发器时innodb表如何被锁定?

我有两个 innodb 表:

文章

id     | title    | sum_votes
------------------------------
1      | art 1    | 5
2      | art 2    | 8
3      | art 3    | 35

投票

id     | article_id    | vote
------------------------------
1      | 1             | 1
2      | 1             | 2
3      | 1             | 2
4      | 2             | 10
5      | 2             | -2
6      | 3             | 10
7      | 3             | 15
8      | 3             | 12
9      | 3             | -2

当向votes表中插入一条新记录时,我想通过计算所有投票的总和来更新articles表中的sum_votes字段。

问题

如果 SUM() 计算本身非常繁重(votes表有 700K 条记录),哪种方式更有效。

1\。创建触发器

CREATE TRIGGER `views_on_insert`
AFTER INSERT
ON `votes`
FOR EACH ROW
BEGIN
   UPDATE `articles` SET
       sum_votes = (
           SELECT SUM(`vote`)
           FROM `votes`
           WHERE `id` = NEW.article_id
       )
    WHERE `id` = NEW.article_id;
END;

2\。在我的应用程序中使用两个查询

SELECT SUM(`vote`) FROM `votes` WHERE `article_id` = 1;
UPDATE `articles` 
   SET sum_votes = <1st_query_result> 
 WHERE `id` = 1;

第一种方法看起来更干净,但是在 SELECT 查询运行的整个过程中表会被锁定吗?

解答

关于并发问题,您有一个 'easy' 方法来防止第二种方法中的任何并发问题,在您的事务中在文章行上执行选择(For update现在是隐式的)。同一篇文章上的任何并发插入都将无法获得相同的锁,将等待您。

使用新的默认隔离级别,即使在事务中不使用序列化级别,您也不会看到投票表上的任何并发插入,直到事务结束。所以你的 SUM 应该保持一致或看起来一致。但是,如果并发事务对同一篇文章进行投票并在您之前提交(而第二个事务看不到您的插入),则最后提交的事务将覆盖计数器,您将失去 1 票。 因此,通过使用 select before 对文章执行行锁定(当然,在事务中完成您的工作)。它很容易测试,在 MySQL 上打开 2 个交互式会话并使用 BEGIN 启动事务。

如果您使用触发器,则默认情况下您处于事务中。但我认为你应该在文章表上执行选择,以便为运行的并发触发器创建隐式行锁定(更难测试)。

• 不要忘记删除触发器。

• 不要忘记更新触发器。

• 如果不使用触发器并留在代码中,请注意每次对投票的插入/删除/更新查询都应在事务之前对相应文章执行行锁定。忘记一个并不难。

最后一点:在开始交易之前进行更难的交易使用:

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;

这样您就不需要对文章进行行锁定,MySQL 将检测到同一行上发生的潜在写入,并将阻止其他事务直到您完成。 但不要使用你从之前的请求中计算出来的东西。更新查询将等待文章的锁释放,当第一个事务COMMIT释放锁时,应重新计算SUM以计数。因此更新查询应包含SUM或进行添加。

update articles set nb_votes=(SELECT count(*) from vote) where id=2; 

在这里你会看到 MySQL 是智能的,如果 2 个事务尝试执行此操作,同时插入已在并发时间内完成,则会检测到死锁。在序列化级别中,我还没有找到一种方法来获取错误的值:

   SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
   BEGIN;
       insert into vote (...
       update articles set nb_votes=(
         SELECT count(*) from vote where article_id=xx
       ) where id=XX;
    COMMIT;

但要准备好处理必须重做的中断事务。