How do I unset/reset a transaction isolation level for SQL Server?

Question

Maybe I'm misunderstanding something about transactions or what SQL Server is doing but consider the following 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?

Maybe it doesn't matter, but I like the warm fuzzy feeling that I'm leaving things the way they were once I'm done doing what I'm doing. Is it possible to reset the isolation level back to the original state regardless of what the state was before?

Numerical

Answer 1

If your code is executed inside a stored procedure, the change only applies during the scope of the stored proc - when the stored proc returns, the isolation level for the connection will automatically revert to it's previous level:

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

(Ignoring result set from sys.objects, this outputs 2, 4 and 2 as the isolation levels).

Domesticate

My code ended up in a stored proc so that is good to know. Thanks! – Numerical

@Domesticate Does this apply to triggers as well? – Lehet

@SpongebobComrade - yes - SET TRANSACTION ISOLATION LEVEL: "If you issue SET TRANSACTION ISOLATION LEVEL in a stored procedure or trigger, when the object returns control the isolation level is reset to the level in effect when the object was invoked" – Domesticate

Answer 2

You know the current level from sys.dm_exec_sessions.transaction_isolation_level

If you need to span batches, then use SET CONTEXT_INFO to preserve the value which can also be read from sys.dm_exec_sessions later on.

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)

Bartolomeo

Answer 3

GBNs solution does not work for me. I doubt it will work elsewhere.

The problem is that return to previous isolation level is only valid within the context of the EXEC. My script is below. Note, that it also does not attempt to change the isolation if current isolation includes snapshots. (It will fail if you try).

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;

Silage