触发器是否基于查询原子? [英] Are Triggers Based On Queries Atomic?

问题描述

我有一个带有序列号的表.该序列号将更改，并且引用自动编号将不起作用.我担心触发器的值会发生冲突.如果两个事务同时读取.

I have a table that has a Sequence number. This sequence number will change and referencing the auto number will not work. I fear that the values of the trigger will collide. If two transactions read at the same time.

我已经对3个连接进行了模拟测试，每个连接约有100万条记录，并且没有碰撞.

I have ran simulated tests on 3 connections @ ~1 million records each and no collisions.

CREATE TABLE `aut` (
  `au_id` int(10) NOT NULL AUTO_INCREMENT,
  `au_control` int(10) DEFAULT NULL,
  `au_name` varchar(50) DEFAULT NULL,
  `did` int(10) DEFAULT NULL,
  PRIMARY KEY (`au_id`),
  KEY `Did` (`did`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=latin1

---

TRIGGER `binc_control` BEFORE INSERT ON `aut` 
FOR EACH ROW BEGIN
SET NEW.AU_CONTROL = (SELECT COUNT(did)+1 FROM aut WHERE did = NEW.did);
END;

推荐答案

是的，如果两个会话同时运行触发器，则这取决于竞赛条件.您不应该使用此解决方案.

Yes, this is subject to race conditions if two sessions run the trigger at the same time. You shouldn't use this solution.

它可能不会在测试期间发生，但您可以假设它会在生产期间发生. :-)

It may not happen during testing, but you can assume it will happen during production. :-)

有句古话，百万分之一是下个星期二.

这篇关于触发器是否基于查询原子?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！