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?

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).

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)