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