SQL Server触发器隔离/范围文档 [英] SQL Server Trigger Isolation / Scope Documentation
问题描述
我一直在寻找有关SQL Server中触发器的隔离级别（或并发性或作用域……我不确定该怎么称呼）的权威性文档。
我发现以下来源表明我相信是正确的（也就是说，两个用户正在对同一表执行更新-甚至是相同的行-然后具有独立的隔离触发器执行）
第一个问题本质上是我试图找到答案的相同问题，但是给出的答案不提供任何来源。第二个问题也很接近答案，答案是相同的，但是同样，没有提供任何资料。
有人可以指出我可用文档的来源吗？
谢谢！
那么，隔离级别和范围是两个非常不同的事物。
隔离级别
触发器在事务内运行。默认情况下，该事务应使用默认隔离级别 READ COMMITTED 。但是，如果调用进程指定了不同的隔离级别，则它将覆盖默认值。像往常一样：如果需要，您应该能够在触发器本身内覆盖它。
根据MSDN页面上的 DML触发器：
触发器和触发它的语句被视为单个事务，可以从触发器内部回滚。如果检测到严重错误（例如，磁盘空间不足），则整个事务将自动回滚。
Scope
提供的上下文是：
{来自您}
两个用户，对同一张表执行更新–甚至是同一行
{来自第一个链接的MSDN问题中的文章基本上是我试图找到答案的相同问题}
插入和删除的表是否具有作用域到当前会话？换句话说，它们将仅包含当前作用域的插入和删除记录，还是仅包含针对同一表的所有当前更新操作的记录？
在插入之前，
c $ c>和 deleted 表应该非常清楚地表明，在任何给定时刻，在特定行上只会发生单个DML操作。两个或多个请求可能会在完全相同的纳秒内到达，但所有请求都会轮流一次，一次（是的，由于锁定）。
现在，关于插入的和删除的表中的内容：是的，只有该特定事件的行将是（并且甚至可以）在这两个伪表中。如果执行将修改5行的UPDATE，则只有这5行将位于插入的和删除的表中。并且，由于您正在寻找文档，因此使用插入和删除的表的MSDN页面指出：
已删除的表在DELETE和UPDATE语句期间存储了受影响的行的副本。在执行DELETE或UPDATE语句期间，将行从触发器表中删除，然后转移到已删除的表中。删除的表和触发器表通常没有相同的行。
插入的表在INSERT和UPDATE语句期间存储受影响的行的副本。在插入或更新事务期间，新行将同时添加到插入表和触发器表中。插入表中的行是触发器表中新行的副本。
将其与问题的另一部分联系起来，与交易隔离级别有关的部分：交易隔离级别对已插入和已删除表绝对没有影响因为它们与该事件/查询特别相关。但是，在这两个伪表中捕获的该操作的净效果，如果其他进程正在使用 READ UNCOMMITTED 可见。 $ c>隔离级别或 NOLOCK 表提示。
为了澄清一下，上面链接的MSDN页面关于插入的和删除的表在开始时就声明它们在内存中，但这并不完全正确。从SQL Server 2005开始，这两个伪表实际上基于 tempdb 。 tempdb数据库的MSDN页面状态：
tempdb 系统数据库是全局资源，可供连接到SQL Server实例的所有用户使用，并用于保留以下：
...
行版本由数据修改事务针对诸如在线索引操作，多个活动结果集（MARS）和AFTER触发器之类的功能生成的。
在SQL Server 2005之前，已插入和已删除
总而言之，已插入和删除的表：
- 在事务内操作
- 是静态（即只读）表
- 是visib仅指向当前触发器
- 仅包含触发该触发器实例的特定事件/操作/查询的行
I have been looking for definitive documentation regarding the isolation level ( or concurrency or scope ... I'm not sure EXACTLY what to call it) of triggers in SQL Server.
I have found the following sources which indicate that what I believe is true (which is to say that two users, executing updates to the same table --even the same rows-- will then have independent and isolated triggers executed):
The first question is essentially the same question I am trying to find the answer to, but the answer given doesn't provide any sources. The second question also hits near the mark, and the answer is the same, but again, no sources are provided.
Can someone point me to where the available documentation makes the same assertions?
Thanks!
Well, Isolation Level and Scope are two very different things.
Isolation Level
Triggers operate within a transaction. By default, that transaction should be using the default isolation level of READ COMMITTED. However, if the calling process has specified a different isolation level, then that would override the default. As per usual: if desired, you should be able to override that within the trigger itself.
According to the MSDN page for DML Triggers:
The trigger and the statement that fires it are treated as a single transaction, which can be rolled back from within the trigger. If a severe error is detected (for example, insufficient disk space), the entire transaction automatically rolls back.
Scope
The context provided is:
{from you}
two users, executing updates to the same table --even the same rows
{from the first linked MSDN article in the Question that is "essentially the same question I am trying to find the answer to"}
Are the inserted and deleted tables scoped to the current session? In other words will they only contain the inserted and deleted records for the current scope, or will they contain the records for all current update operations against the same table? Can there even be truely concurrent operations or will locks prevent this?
Before getting into the inserted and deleted tables it should be made very clear that there will only ever be a single DML operation happening on a particular row at any given moment. Two or more requests might come in at the exact same nanosecond, but all requests will take their turn, one at a time (and yes, due to locking).
Now, regarding what is in the inserted and deleted tables: Yes, only the rows for that particular event will be (and even can be) in those two pseudo-tables. If you execute an UPDATE that will modify 5 rows, only those 5 rows will be in the inserted and deleted tables. And since you are looking for documentation, the MSDN page for Use the inserted and deleted Tables states:
The deleted table stores copies of the affected rows during DELETE and UPDATE statements. During the execution of a DELETE or UPDATE statement, rows are deleted from the trigger table and transferred to the deleted table. The deleted table and the trigger table ordinarily have no rows in common.
The inserted table stores copies of the affected rows during INSERT and UPDATE statements. During an insert or update transaction, new rows are added to both the inserted table and the trigger table. The rows in the inserted table are copies of the new rows in the trigger table.
Tying this back to the other part of the question, the part relating to the Transaction Isolation Level: The Transaction Isolation Level has absolutely no effect on the inserted and deleted tables as they pertain specifically to that event/query. However, the net effect of that operation, which is captured in those two psuedo-tables, can still be visible to other processes if they are using the READ UNCOMMITTED Isolation Level or the NOLOCK table hint.
And just to clarify something, the MSDN page linked above regarding the inserted and deleted tables states at the very beginning that they are "in memory" but that is not exactly correct. Starting in SQL Server 2005, those two pseudo-tables are actually based in tempdb. The MSDN page for the tempdb Database states:
The tempdb system database is a global resource that is available to all users connected to the instance of SQL Server and is used to hold the following:
...
Row versions that are generated by data modification transactions for features, such as: online index operations, Multiple Active Result Sets (MARS), and AFTER triggers.
Prior to SQL Server 2005, the inserted and deleted tables were read from the Transaction Log (I believe).
To summarize, the inserted and deleted tables:
- operate within a Transaction
- are static (i.e. read-only) tables
- are visible to only the current Trigger
- only contain rows for the specific event/operation/query that fired that instance of that Trigger
这篇关于SQL Server触发器隔离/范围文档的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
