触发器是否以当前事务隔离级别执行？
考虑一个包含 3 列的表：ID（唯一，取自 Oracle 序列）、CATEGORY 和 CODE（对最后两列没有限制）。
每个类别都附加有多个代码，但这些代码在该类别中必须是唯一的。示例：
ID CATEGORY CODE
1 1 X
2 1 Y
3 1 Y //wrong
第三个不正确，因为我们已经有了类别 1 的代码 Y。
现在考虑一个在插入之前运行的触发器，并检查要插入的值是否正确。也就是说，对于要插入的记录，触发器读取类别，然后从表中读取具有该类别的所有代码，如果必须插入的记录中的代码已经存在，则引发异常，以便该记录未插入。
我的问题是，如果事务隔离级别为 READ_COMMITED，并且几乎完全相同的时间在两个不同的事务中执行了两次插入，但事务稍后提交，那么触发器将在表中“看到”什么？
示例：
(1) 最初，该表如下所示：
ID CATEGORY CODE
1 1 X
(2) 有两个事务 T1 和 T2（两者的隔离级别为 READ_COMMITED）；
(3) 两个事务都想插入category=1和code=Y；
(4)T1执行插入，执行触发器。表中没有Y所以插入即可；
(5)T2执行插入，执行触发器。表中没有Y（T1还没有提交）所以插入就可以了；
(6) T1 提交，表现在如下所示：
ID CATEGORY CODE
1 1 X
2 1 Y
(7) T2 现在提交。这里会发生什么？我是否收到错误且未插入记录，或者收到下表：
ID CATEGORY CODE
1 1 X
2 1 Y
3 1 Y //this is wrong
？！
触发器“看到”什么以及插入物会发生什么？
Consider a table with 3 columns: ID (unique, taken from an Oracle sequence), CATEGORY and CODE (no constraints on these last two).
Each category has multiple codes attached to it, but the codes must be unique within that category. Example:
ID CATEGORY CODE
1 1 X
2 1 Y
3 1 Y //wrong
The third one is not OK since we already have code Y for the category 1.
Now consider a trigger that runs before inserts and checks to see if the values to be inserted are OK. That is, for the record that is being inserted the trigger reads the category, and then reads all codes from the table that have that category, and if the code from the record that must be inserted already exists then raises an exception so that the record is not inserted.
My question is what will the trigger "see" in the table if the transaction isolation level is READ_COMMITED and there are two inserts executed in two different transactions at almost exactly the same time but the transaction is committed later?
Example:
(1) Initially, the table looks like this:
ID CATEGORY CODE
1 1 X
(2) there are two transactions T1 and T2 (isolation level READ_COMMITED for both);
(3) both transactions want to insert category = 1 and code = Y;
(4) T1 performs the insert and the trigger is executed. There is no Y in the table so it is OK to insert;
(5) T2 performs the insert and the trigger is executed. There is no Y in the table (T1 has not yet commited) so it is OK to insert;
(6) T1 commits and the table now looks like this:
ID CATEGORY CODE
1 1 X
2 1 Y
(7) T2 now commits. What happens here? Do I get an error and the record is not inserted or I get the following table:
ID CATEGORY CODE
1 1 X
2 1 Y
3 1 Y //this is wrong
?!
What do the triggers "see" and what happens to the insert?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（1）
不要使用触发器进行此类验证。触发器无法扩展。此外，正如您所注意到的，它们不适用于多用户环境。这就是为什么大自然给了我们独特的限制。
Don't use triggers for such validation. Triggers don't scale. Also, as you have noticed they don't work in multi-user environments. This is why Nature gave us unique constraints.