处理 ON INSERT 触发器时，innodb 表如何锁定？

Question

我有两个 innodb 表：

articles

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

votes

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 表中时，我想更新 sum_votes< articles 表中的 /code> 字段通过计算所有投票的总和。

问题

如果 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 查询运行的整个过程中被锁定吗？

Answer 1

关于并发问题，您有一个“简单”的方法来防止第二种方法中的任何并发问题，在事务内对文章行执行选择（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;

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

Answer 2

试试这个：

PHP：星级评级系统概念？

编辑：更改了架构以允许用户多次为同一图像投票：

drop table if exists image;
create table image
(
image_id int unsigned not null auto_increment primary key,
caption varchar(255) not null,
num_votes int unsigned not null default 0,
total_score int unsigned not null default 0,
rating decimal(8,2) not null default 0
)
engine = innodb;

drop table if exists image_vote;
create table image_vote
(
vote_id int unsigned not null auto_increment primary key,
image_id int unsigned not null,
user_id int unsigned not null,
score tinyint unsigned not null default 0,
key (image_id, user_id)
)
engine=innodb;

delimiter #

create trigger image_vote_after_ins_trig after insert on image_vote
for each row
begin
 update image set 
    num_votes = num_votes + 1,
    total_score = total_score + new.score,
    rating = total_score / num_votes  
 where 
    image_id = new.image_id;
end#

delimiter ;

insert into image (caption) values ('image 1'),('image 2'), ('image 3');

insert into image_vote (image_id, user_id, score) values
(1,1,5),(1,2,4),(1,3,3),(1,4,2),(1,5,1),(1,5,2),(1,5,3),
(2,1,2),(2,2,1),(2,3,4),(2,3,2),
(3,1,4),(3,5,2);

select * from image;
select * from image_vote;