问题描述
如果我使用命令 SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED 然后在同一上下文中使用 EXEC storedProcedureName 执行存储过程,该存储过程是否会使用该事务之前声明的级别还是将使用默认级别?
If I use the command SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED and then execute a stored procedure using the EXEC storedProcedureName on the same context, will the stored procedure use the transaction level stated previously or will use a default one?
如果我想强制每个存储过程在事务级别使用,我是否必须在代码顶部包含相同的语句(SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED)?
If I want to force every stored procedure to use on transaction level do I have to include at the top of the code the same statement (SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED)?
PS.:该系统建立在 .NET 2.0 和专有第三方产品之上,但存在局限性,因此需要这些变通方法.
PS.: the system is built on top of .NET 2.0 and proprietary third party products with limitations, hence the need of these workarounds.
推荐答案
存储过程将使用调用时生效的事务隔离.
The stored procedure will use the transaction isolation in effect when called.
如果存储过程本身设置了一个明确的隔离级别,这将在存储过程退出时重置.
If the stored procedure itself sets an explicit isolation level this will be reset when the stored procedure exits.
(刚刚检查过,这是与 BOL 所说的相反...它保持为该连接设置,直到它被明确更改"但可以从下面看到)
( Just checked and this is contrary to what BOL says "... it remains set for that connection until it is explicitly changed" but can be seen from the below)
CREATE PROC CheckTransLevel AS DECLARE @Result varchar(20) SELECT @Result = CASE transaction_isolation_level WHEN 0 THEN 'Unspecified' WHEN 1 THEN 'ReadUncomitted' WHEN 2 THEN 'Readcomitted' WHEN 3 THEN 'Repeatable' WHEN 4 THEN 'Serializable' WHEN 5 THEN 'Snapshot' END FROM sys.dm_exec_sessions WHERE session_id = @@SPID PRINT @Result GO CREATE PROC SetRCTransLevel AS PRINT 'Enter: SetRCTransLevel' SET TRANSACTION ISOLATION LEVEL READ COMMITTED EXEC CheckTransLevel PRINT 'Exit: SetRCTransLevel' GO SET NOCOUNT ON SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED EXEC CheckTransLevel EXEC SetRCTransLevel EXEC CheckTransLevel
结果
ReadUncomitted Enter: SetRCTransLevel Readcomitted Exit: SetRCTransLevel ReadUncomitted