mysql 触发器 锁表,mysql – 处理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

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

这个问题

如果SUM()计算本身是一个非常重的(投票表有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查询运行的整个过程中表是否会被锁定？

解决方法:

关于并发问题,你有一个’简单’的方法来防止第二种方法中的任何并发问题,在你的事务内部对文章行执行选择(For update现在是隐式的).同一篇文章中的任何并发插入都无法获得相同的锁定并等待您.

使用新的默认隔离级别,甚至在事务中甚至不使用序列化级别,在事务结束之前,您不会在投票表上看到任何并发插入.所以你的SUM应保持连贯或看起来像连贯.但是如果一个并发事务在你之前插入对同一篇文章的投票并提交(并且第二个没有看到你的插入),那么最后提交的事务将覆盖该计数器并且你将放弃1票.因此,通过使用select之前对文章执行行锁定(当然,在事务中执行您的工作).它很容易测试,在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;

但是要准备好处理必须重做的破坏事务.

标签：mysql,sql,innodb,triggers,table-locking

来源： https://codeday.me/bug/20190518/1128961.html