在正在執行的事務中更改事務隔離級別

我正在研究 SQL Server 中的事務隔離級別，並試圖弄清楚當隔離級別在事務生命週期內發生變化時 SQL Server 的行為方式。

似乎在 SQL Server 中可以執行以下操作：

BEGIN TRANSACTION;

SET TRANSACTION ISOLATION LEVEL READ COMMITTED;

/* (some selects/inserts/updates/deletes) */

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;

/* (some selects/inserts/updates/deletes) */

COMMIT TRANSACTION;

老實說，我想不出一個降低隔離級別有意義的例子，我只能想到一些場景，其中一部分事務需要可序列化隔離，而其他部分則不需要。我有一種感覺，將使用快照的隔離級別和行版本控制與其他類型的隔離級別混合起來效果不佳，但我找不到太多資訊來支持這一點。

單個事務在其生命週期中在多個隔離級別之間切換是否會在實踐中發生？是否有一些注意事項和細節需要了解？

您使用的語法是有效的，在SET TRANSACTION ISOLATION LEVEL命令之後執行的所有語句都將使用指定的隔離級別。

考慮下表：

CREATE TABLE dbo.test
(
   ID int identity(1,1) Primary key
   , Field nvarchar(10)
)
GO

INSERT INTO dbo.test(Field)
Values ('XXXXXXXXXX')
GO 100

接下來，啟動一個事務並執行 2 個語句。

第一條語句將使用REPEATABLE READ（在語句完成時不會釋放其共享鎖），第二條語句將使用SERIALIZABLE（它將獲取並保持範圍鎖）

BEGIN TRANSACTION

SET TRANSACTION ISOLATION LEVEL REPEATABLE READ

SELECT *
FROM dbo.test 
WHERE id = 100

-- This will show a shared lock on key 100, the lock is kept during the transaction because repeatable read is used
SELECT *
FROM sys.dm_tran_locks 
WHERE request_session_id = @@SPID

-- Isolation level 3 = repeatable read
SELECT transaction_isolation_level 
FROM sys.dm_exec_sessions 
WHERE session_id = @@SPID

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE

SELECT *
FROM dbo.test
WHERE id >= 0 and id < 10

-- This will show the previous shared lock on key 100 + additional RangeS-S key locks taken by the serializable statement
SELECT *
FROM sys.dm_tran_locks
WHERE request_session_id = @@SPID

-- Isolation level 4 = Serializable
SELECT transaction_isolation_level 
FROM sys.dm_exec_sessions 
WHERE session_id = @@SPID

COMMIT TRANSACTION

查看 的輸出，sys.dm_tran_locks您sys.dm_exec_sessions可以看到這兩個語句在同一事務中發出不同的鎖並使用不同的隔離級別。儘管在技術上是可行的，但我傾向於在事務中使用單個隔離級別。

將SNAPSHOT隔離與悲觀鎖定相結合時，您可能會得到一些意想不到的結果。

想像一下單獨啟動一個事務SNAPSHOT並讀取一些數據。

接下來，第二個會話會更新您剛剛讀取的數據。

第一個事務切換到READ COMMITTED並再次讀取數據，SQL Server 現在將返回更新的值。

最後，您再次切換到SNAPSHOT隔離並讀取數據。您將使用之前創建的快照進行讀取，並返回舊值。

-- Note: you have to set your isolation level to snapshot before starting your transaction otherwise you will get an error

SET TRANSACTION ISOLATION LEVEL SNAPSHOT 

BEGIN TRANSACTION 

-- Returns XXXXXXXXXX
SELECT * 
FROM dbo.test 
WHERE id = 1

-- Run "UPDATE dbo.test SET Field = 'YYYYYYYYYY' WHERE id = 1" in a separate session

SET TRANSACTION ISOLATION LEVEL READ COMMITTED 

-- Returns YYYYYYYYYY
SELECT * 
FROM dbo.test
WHERE id = 1

SET TRANSACTION ISOLATION LEVEL SNAPSHOT 

-- Returns XXXXXXXXXX
SELECT * 
FROM dbo.test 
WHERE id = 1

COMMIT

引用自：https://dba.stackexchange.com/questions/249361