問題描述

如果我使用命令 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

這篇關于SQL Server 2005 事務級和存儲過程的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，也希望大家多多支持html5模板網！