A blog about SQL Server, SSIS, C# and whatever else I happen to be dealing with in my professional life.

Find ramblings

Thursday, November 9, 2017

What's my transaction isolation level

What's my transaction isolation level

That's an easy question to answer - StackOverflow has a fine answer.

But, what if I use sp_executesql to run some dynamic sql - does it default the connection isolation level? If I change isolation level within the query, does it propagate back to the invoker? That's a great question, William. Let's find out.

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

SELECT CASE transaction_isolation_level 
WHEN 0 THEN 'Unspecified' 
WHEN 1 THEN 'ReadUncommitted' 
WHEN 2 THEN 'ReadCommitted' 
WHEN 3 THEN 'Repeatable' 
WHEN 4 THEN 'Serializable' 
WHEN 5 THEN 'Snapshot' END AS TRANSACTION_ISOLATION_LEVEL 
FROM sys.dm_exec_sessions 
where session_id = @@SPID;

DECLARE
    @query nvarchar(max) = N'-- Identify iso level
SELECT CASE transaction_isolation_level 
WHEN 0 THEN ''Unspecified'' 
WHEN 1 THEN ''ReadUncommitted'' 
WHEN 2 THEN ''ReadCommitted'' 
WHEN 3 THEN ''Repeatable'' 
WHEN 4 THEN ''Serializable'' 
WHEN 5 THEN ''Snapshot'' END AS TRANSACTION_ISOLATION_LEVEL 
FROM sys.dm_exec_sessions 
where session_id = @@SPID;

SET TRANSACTION ISOLATION LEVEL READ COMMITTED;

-- Test iso level
SELECT CASE transaction_isolation_level 
WHEN 0 THEN ''Unspecified'' 
WHEN 1 THEN ''ReadUncommitted'' 
WHEN 2 THEN ''ReadCommitted'' 
WHEN 3 THEN ''Repeatable'' 
WHEN 4 THEN ''Serializable'' 
WHEN 5 THEN ''Snapshot'' END AS TRANSACTION_ISOLATION_LEVEL 
FROM sys.dm_exec_sessions 
where session_id = @@SPID'

EXECUTE sys.sp_executesql @query, N'';

SELECT CASE transaction_isolation_level 
WHEN 0 THEN 'Unspecified' 
WHEN 1 THEN 'ReadUncommitted' 
WHEN 2 THEN 'ReadCommitted' 
WHEN 3 THEN 'Repeatable' 
WHEN 4 THEN 'Serializable' 
WHEN 5 THEN 'Snapshot' END AS TRANSACTION_ISOLATION_LEVEL 
FROM sys.dm_exec_sessions 
where session_id = @@SPID;

I begin my session in read uncommitted aka "nolock". I then run dynamic sql which identifies my isolation level, still read uncommitted, change it to a different level, confirmed at read committed, and then exit and check my final state - back to read uncommitted.

Finally, thanks to Andrew Kelly (b|t) for answering the #sqlhelp call.

No comments: