如何为SQL Server 取消/重置事务隔离级别？

Question

也许我对事务或SQL Server的操作存在误解，但请考虑以下T-SQL语句：

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
GO

BEGIN TRANSACTION

-- DO SOME READS AND OTHER THINGS

COMMIT

-- OK, WHAT HAPPENS TO THE ISOLATION LEVEL AFTER THIS?

也许这并不重要，但我喜欢有一种温暖的感觉：当我完成目标后，将事物恢复到之前的状态。无论之前的状态是什么，是否可能将隔离级别重置回原始状态？

- aarona

Answer 1

如果您的代码在存储过程中执行，则更改仅适用于存储过程的范围 - 当存储过程返回时，连接的隔离级别将自动恢复为其先前的级别：

create procedure dbo.IsoTest
as
    set transaction isolation level serializable
    begin transaction

    select transaction_isolation_level FROM sys.dm_exec_sessions where session_id = @@SPID

    select object_id from sys.objects

    commit
go
select transaction_isolation_level FROM sys.dm_exec_sessions where session_id = @@SPID
exec dbo.IsoTest
select transaction_isolation_level FROM sys.dm_exec_sessions where session_id = @@SPID

(忽略sys.objects的结果集，这将输出2、4和2作为隔离级别)。

- Damien_The_Unbeliever

---

我的代码最终被存储在一个存储过程中，这是个好消息。谢谢！ - aarona

@Damien_The_Unbeliever 这也适用于触发器吗？ - Spongebob Comrade

@SpongebobComrade - 是的 - SET TRANSACTION ISOLATION LEVEL："如果您在存储过程或触发器中发出 SET TRANSACTION ISOLATION LEVEL 命令，则当对象返回控制时，隔离级别将重置为调用对象时生效的级别"。 - Damien_The_Unbeliever

@Damien_The_Unbeliever 谢谢。非常好。 - Spongebob Comrade

Answer 2

你可以从 sys.dm_exec_sessions.transaction_isolation_level 获取当前的事务隔离级别。

如果需要跨批处理，则可以使用 SET CONTEXT_INFO 保存该值，稍后还可以从 sys.dm_exec_sessions 中读取。

DECLARE @CurrentIsolationLevel smallint

SELECT @CurrentIsolationLevel = transaction_isolation_level
FROM sys.dm_exec_sessions
WHERE session_id = @@SPID

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
BEGIN TRANSACTION
-- DO SOME READS AND OTHER THINGS
COMMIT

DECLARE @SQL varchar(200)
SELECT @SQL = 'SET TRANSACTION ISOLATION LEVEL ' +
       CASE  @CurrentIsolationLevel
           WHEN 1 THEN 'READ UNCOMMITTED'
           WHEN 2 THEN 'READ COMMITTED'
           WHEN 3 THEN 'REPEATABLE READ'
           WHEN 4 THEN 'SERIALIZABLE'
           WHEN 5 THEN 'SNAPSHOT'
       END
EXEC (@sql)

- gbn

Answer 3

GBN的解决方案对我无效，我怀疑在其他地方也不会有用。

问题在于返回到先前的隔离级别仅在EXEC的上下文中才有效。我的脚本如下。请注意，如果当前隔离级别包括快照，则它也不会尝试更改隔离级别（如果您尝试，则会失败）。

DECLARE @initalIsoloationLevel nvarchar(25)

SELECT @initalIsoloationLevel =
      CASE
          WHEN transaction_isolation_level = 1 
             THEN 'READ UNCOMMITTED' 
          WHEN transaction_isolation_level = 2 
               AND is_read_committed_snapshot_on = 1 
             THEN 'READ COMMITTED SNAPSHOT' 
          WHEN transaction_isolation_level = 2 
               AND is_read_committed_snapshot_on = 0 
             THEN 'READ COMMITTED' 
          WHEN transaction_isolation_level = 3 
             THEN 'REPEATABLE READ' 
          WHEN transaction_isolation_level = 4 
             THEN 'SERIALIZABLE' 
          WHEN transaction_isolation_level = 5 
             THEN 'SNAPSHOT' 
          ELSE NULL
       END 
FROM   sys.dm_exec_sessions AS s
WHERE  session_id = @@SPID

  /* Changing isolation level is not supported with snapshots */
IF @initalIsoloationLevel NOT LIKE '%SNAP%' AND @initalIsoloationLevel is NOT NULL
    SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;


/****** DO  YOUR  STUFF HERE   ******/


  /* Changing isolation level is not supported with snapshots */
IF @initalIsoloationLevel = 'READ COMMITTED'  
     SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
ELSE IF @initalIsoloationLevel = 'REPEATABLE READ' 
     SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
ELSE IF @initalIsoloationLevel = 'SERIALIZABLE'
     SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;

- Joe Mroczek