我有两个 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
通过计算所有投票的总和来获得表格。
问题
如果 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;
但要准备好处理必须重做的破坏性事务。
本文内容由网友自发贡献,版权归原作者所有,本站不承担相应法律责任。如您发现有涉嫌抄袭侵权的内容,请联系:hwhale#tublm.com(使用前将#替换为@)