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

本文介绍了处理ON INSERT触发器时如何锁定innodb表?的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

我有两个innodb表:

I have two innodb tables:

文章

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字段.

When a new record is inserted into the votes table, I want to update the sum_votes field in articles table by calculating the sum of all votes.

如果SUM()计算本身非常繁琐(votes表具有700K记录)，则哪种方法更有效.

Which way is more efficient, if the SUM() calculation itself is a very heavy one (votes table has 700K records).

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查询运行期间，表是否将被锁定?

1st way seems cleaner, but will the table be locked the whole time the SELECT query runs?

推荐答案

关于并发问题，您可以通过'easy'方式来防止第二种方法中的任何并发问题，并且在事务内部执行在文章行中进行选择(For update现在是隐式的).同一篇文章上的所有并发插入都将无法获得相同的锁，并且将等待您.

About the concurrency problems, you have an 'easy' way to prevent any concurrency problems in the 2nd method, inside your transaction perform a select on the articles line (the For update is now implicit). Any concurrent insert on the same article will not be able to obtain this same lock and will wait for you.

使用新的默认隔离级别，甚至在事务中甚至不使用序列化级别，您都不会在投票表上看到任何并发插入，直到事务结束为止.因此，您的SUM应该保持连贯性或看起来连贯性.但是，如果并发事务在同一文章上插入一个投票并在您之前提交(而第二个事务看不到您的插入)，则最后一次提交的事务将覆盖计数器，您将失去1票. 因此，请使用之前的选择来对文章进行行锁定(当然，可以在事务中完成工作).它很容易测试，可以在MySQL上打开2个交互式会话并使用BEGIN开始交易.

With the new default isolation levels, without even using serialization level in the transaction you wouldn't see any concurrent insert on the vote table until the end of your transaction. So your SUM should stay coherent or looks like coherent. But if a concurrent transaction insert a vote on same article and commit before you (and this 2nd one does not see your insert), the last transaction to commit will overwrite the counter and you'll loose 1 vote. So perform a row lock on article by using a select before (and do your work in a transaction, of course). It's easy to test, open 2 interactive sessions on MySQL and start transactions with BEGIN.

如果使用触发器，则默认情况下您处于事务中.但是我认为您也应该在商品表上执行选择，以为运行中的并发触发器制作一个隐式行锁(很难测试).

If you use the trigger you are in a transaction by default. But I think you should perform as well the select on the article table to make an implicit row lock for concurrent triggers running (harder to test).

• 不要忘记删除触发器.
• 不要忘记更新触发器.
• 如果您不使用触发器并停留 在代码中，要小心 投票插入/删除/更新查询 应该在 之前的相应文章 交易.这不是很难 忘记一个.

• Do not forget delete triggers.
• Do not forget update triggers.
• If you do not use triggers and stay in code, be careful every insert/delete/update query on vote should perform a row lock on the corresponding article before in the transaction. It's not very hard to forget one.

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

Last point: make harder transactions, before starting the transaction use:

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;

这样，您就不需要在文章上进行行锁定，MySQL将检测到在同一行上发生了潜在的写入，并将阻塞其他事务，直到您完成操作为止. 但是不要使用您先前请求中计算出的内容.更新查询将等待商品的锁释放，当第一个事务COMMIT释放锁时，应再次进行SUM的计算以进行计数.因此，更新查询应包含SUM或进行添加.

This way you do not need row locks on articles, MySQL will detect that a potential write on the same row occurs and will block the others transaction until you finish. But do not use something you have computed from a previous request. The update query will be waiting for a lock release on articles, when the lock is released by the 1st transaction COMMIT the computing of SUM should be done again to count. So the update query should contain the SUM or make an addition.

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

在这里您将看到My​​SQL很聪明，如果在同时执行插入操作的同时有2个事务试图执行此操作，则会检测到死锁.在序列化级别，我还没有找到一种使用:

And here you'll see that MySQL is smart, a deadlock is detected if 2 transactions are trying to do this while insert has been done in a concurrent time. In serialization levels I haven't found a way to obtain a wrong value with :

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;

但是准备处理必须重做的中断事务.

But be ready to handle breaking transaction that you must redo.