MySQL TRIGGER 上的并发更新 [英] Concurrent UPDATE's on MySQL TRIGGER

问题描述

我在 MySQL 中有这个触发器:

I have this TRIGGER in MySQL:

CREATE DEFINER = CURRENT_USER TRIGGER `test`.`view_AFTER_INSERT` AFTER INSERT ON `views` FOR EACH ROW
BEGIN
UPDATE metrics SET met_nu_vie = met_nu_vie + 1 WHERE usp_id = NEW.usp_id;   
END

基本上，当用户在 Web 应用程序中收到来自另一个用户的视图"时，系统会在表视图"中创建一个新行，并在插入后增加另一个表中的计数器值(指标).

Basically, when an user receives a 'view' from another user in a web application, the system creates a new row in table 'views', and after insert, increments a counter value in another table (metrics).

我的问题是:如果用户收到来自 10 个不同用户的 10 个并发视图......这个更新安全吗?

My question is: If the user receives 10 concurrent views from 10 different users... ¿is this UPDATE safe?

我的架构中的所有表都是 INNODB.

All tables on my schema are INNODB.

谢谢.

推荐答案

如果用户收到 X 个并发视图，则计数器 eill 会随着问题中的更新语句增加 X.

If the user receives X concurrent views, then the counter eill be incremented by X with the update statement in the question.

作为关于 innodb 锁的 mysql 手册 解释:

UPDATE ... WHERE ... 为每条记录设置一个独占 next-key 锁搜索遇到.但是，只需要一个索引记录锁for 语句使用唯一索引锁定行以搜索唯一行.

UPDATE ... WHERE ... sets an exclusive next-key lock on every record the search encounters. However, only an index record lock is required for statements that lock rows using a unique index to search for a unique row.

Mysql 排他锁手册说:

Mysql manual on exclusive locks says:

如果事务 T1 在行 r 上持有排他 (X) 锁，则请求来自某个不同的事务 T2，用于 r 上的 [独占或共享] 类型的锁不能立即授予.相反，事务 T2 必须等待事务 T1 释放其对 r 行的锁.

If a transaction T1 holds an exclusive (X) lock on row r, a request from some distinct transaction T2 for a lock of either type [exclusive or shared] on r cannot be granted immediately. Instead, transaction T2 has to wait for transaction T1 to release its lock on row r.

总结一下:在你的应用程序级别，查看器可能是并发的，但在 mysql 级别更新是使用排他锁序列化的.

To sum it up: the viewers may be concurrent at your application level, but at mysql level updates are serialised using exclusive locks.

这篇关于MySQL TRIGGER 上的并发更新的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！