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

继明

我有两个innodb表：
文章
id     | title    | sum_votes
------------------------------
6 i4 N7 H2 O- |  I1 W) v2 X3 ^1      | art 1    | 5
2      | art 2    | 8
3      | art 3    | 35
- y" j4 v& ?3 w9 |! O1 ?7 D票数
9 d; Y) E8 t: R" Y% O+ `! Hid     | article_id    | vote
------------------------------
1      | 1             | 1
$ |; [: a, C% p* ]$ y7 r2 V2      | 1             | 2
3      | 1             | 2
4      | 2             | 10
6 ~% I4 G4 V% X5      | 2             | -2
- Q; d( u$ \6 @! t6      | 3             | 10
2 a/ M- l6 {) q" ?! f! R; X7      | 3             | 15
8      | 3             | 12
9      | 3             | -2
6 y) t/ m$ E9 z将新记录插入votes表中时，我想通过计算所有投票的总和来更新表中的sum_votes字段articles。
问题
如果SUM（）计算本身非常votes繁琐（表有700K条记录），则哪种方法更有效。
1.创建触发器
CREATE TRIGGER `views_on_insert`
AFTER INSERT
* S" b8 V5 S2 B" h" R# P6 \ON `votes`
FOR EACH ROW
: R7 K4 d; y  BBEGIN
2 z6 D" y. ?3 e   UPDATE `articles` SET
  l% N: O7 J9 L5 n+ K- D' M* A       sum_votes = (
           SELECT SUM(`vote`)
0 Q, Z! o+ U) `- p7 T( I. b( e% O           FROM `votes`
  i6 _# ~; s- J8 @0 S5 F5 R           WHERE `id` = NEW.article_id
       )
/ ^# ?) w# r/ I1 J    WHERE `id` = NEW.article_id;
7 E3 h# G0 B# p- D# j+ BEND;
$ p" t3 i3 }3 D& F2.在我的应用程序中使用两个查询
SELECT SUM(`vote`) FROM `votes` WHERE `article_id` = 1;
% |" f- C4 n/ P5 D$ UUPDATE `articles`
" s8 I" h7 M0 f6 h- \- L   SET sum_votes =  
: a0 K# a0 W6 @( F2 x: C WHERE `id` = 1;
$ n, O1 k4 ?; C1 f) v! c第一种方法看起来更干净，但是 在整个SELECT查询运行期间，表是否将被锁定？
               
解决方案:
& f" f% j( p% K( f               


                关于并发问题，您有一种 “简便”的 方法来防止第二种方法中的任何并发问题，在事务内部，在商品行上执行选择（For
6 a# R$ R' p/ P2 |, C0 R1 ^7 Bupdate现在是隐式的）。同一篇文章上的任何并发插入将无法获得此相同的锁，并且将等待您。
使用新的默认隔离级别，甚至在事务中甚至不使用序列化级别，您都不会在投票表上看到任何并发插入，直到事务结束。因此，您的SUM应该保持连贯性
或看起来像连贯性
。但是，如果并发事务在同一文章上插入一个投票并在您之前提交（而第二个事务看不到您的插入），则最后一次提交的事务将覆盖计数器，您将失去1票。
因此，请使用之前的select方法对文章进行行锁定
4 U1 W8 l% L* B* s$ p: q7 o（当然，并在事务中完成您的工作）。它很容易测试，可以在MySQL上打开2个交互式会话，并使用BEGIN开始交易。
如果使用触发器，则默认情况下您处于事务中。但是我认为您也应该在商品表上执行选择，以为运行中的并发触发器创建隐式行锁（难以测试）。
不要忘记删除触发器。
# g4 ]+ `- n8 Y7 b8 L( D  p不要忘记更新触发器。
如果您不使用触发器而留在代码中，则在进行交易之前，请小心对投票的每个插入/删除/更新查询都应在相应的文章上执行行锁定。忘记一个不是很困难。

最后一点：在开始使用交易之前，进行更困难的交易：
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
- j7 ~5 r& n9 y+ F$ b这样，您不需要对文章进行行锁定，MySQL将检测到同一行上可能发生的写入，并会阻塞其他事务，直到您完成操作为止。
但是，请勿使用您根据上一个请求计算出的内容
  \5 i- G# `; ^。更新查询将等待商品的锁释放，当第一个事务释放锁时COMMIT，SUM应该再次进行计算。因此，更新查询应包含SUM或进行添加。
update articles set nb_votes=(SELECT count(*) from vote) where id=2;
在这里，您会看到MySQL很聪明，如果在同时执行插入操作的同时有2个事务试图执行此操作，则会检测到死锁。在序列化级别中，我还没有找到一种使用以下方法获得错误值的方法：
$ {) v" n, r: J* X   SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
6 ?( ^+ F( j/ u4 C* _" Y   BEGIN;
       insert into vote (...
4 U0 C; [1 P- `" A7 X& X' Z       update articles set nb_votes=(
$ d7 h) F( `  N0 Q" E9 }; k8 m. F         SELECT count(*) from vote where article_id=xx
       ) where id=XX;
    COMMIT;
但是请准备好处理您必须重做的突破性交易。