THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

John Paul Cook

Scope of SET TRANSACTION ISOLATION LEVEL

To help you better understand the scope of SET TRANSACTION ISOLATION LEVEL, I wrote some code for you to run on your own machine to experiment with and see for yourself. I think you'll remember it better by running the script below and carefully analyzing the output.

Whatever the TRANSACTION ISOLATION LEVEL is before executing a stored procedure is what it will be when the stored procedure starts executing. If it is changed inside the stored procedure, the change will persist within the stored procedure, but it will not persist outside the stored procedure.

If sp_executesql is executed within the stored procedure, the current TRANSACTION ISOLATION LEVEL in the stored procedure is carried through into the statement executed by sp_executesql. This is not a trivial point. There are some things in a stored procedure that don't carry through into sp_executesql. For example, a table valued function declared in a stored procedure isn't scoped into sp_executesql, but a temporary table declared in a stored procedure does scope into sp_executesql, which is why I used a temporary table instead of a table variable.

Documentation for sys.dm_exec_requests and transaction_isolation_level is found here. Quoting from said document, we see the following definitions of transaction_isolation_level:

      0 = Unspecified
      1 = ReadUncomitted
      2 = ReadCommitted
      3 = Repeatable
      4 = Serializable
      5 = Snapshot

I recommend setting SSMS to display the results as text instead of in a grid. Go to Query menu, then select Results To and from that menu, select Results to Text. Or more simply, do CTRL-T to accompish the same thing.

Have fun!

SET NOCOUNT ON

USE tempdb;
GO

CREATE PROCEDURE dbo.TIL
AS
    DECLARE @transaction_isolation_level SMALLINT;
   
DECLARE @sqlString NVARCHAR(1000);

    CREATE TABLE #TILstate (
       
context NVARCHAR(100)
      
,transaction_isolation_level SMALLINT
   
);

    SELECT @transaction_isolation_level = transaction_isolation_level
   
FROM sys.dm_exec_requests
   
WHERE session_id = @@spid;

    INSERT INTO #TILstate (context, transaction_isolation_level)
   
VALUES ('in stored proc, initial transaction_isolation_level before changing anything', @transaction_isolation_level);

    SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

    SELECT @transaction_isolation_level = transaction_isolation_level
   
FROM sys.dm_exec_requests
    WHERE session_id = @@spid;

    INSERT INTO #TILstate (context, transaction_isolation_level)
   
VALUES ('in stored proc, after SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED', @transaction_isolation_level);

    SET @sqlString = 'INSERT INTO #TILstate (context, transaction_isolation_level)'
                  
+ ' SELECT ''in stored proc, transaction_isolation_level in sp_executesql'','
                  
+ ' transaction_isolation_level FROM sys.dm_exec_requests WHERE session_id = @@spid';

    PRINT @sqlString;
    PRINT '';

    EXEC sp_executesql @sqlString;

    SELECT * FROM #TILstate;

    DROP TABLE #TILstate;

GO --need this statement to separate the CREATE PROCEDURE from what's next

/* If you execute the following statements a second time, you're likely to see slightly different results. */
/* You should be able to figure out why by carefully considering what condition changed. */

SELECT N'default transaction_isolation_level before executing anything' as context, transaction_isolation_level
FROM sys.dm_exec_requests
WHERE session_id = @@spid;

SET TRANSACTION ISOLATION LEVEL SNAPSHOT

SELECT N'transaction_isolation_level after setting it and before executing stored procedure' as context, transaction_isolation_level
FROM sys.dm_exec_requests
WHERE session_id = @@spid;

exec dbo.TIL;

SELECT N'transaction_isolation_level after executing stored procedure' as context, transaction_isolation_level
FROM sys.dm_exec_requests
WHERE session_id = @@spid;

 

 

 

Published Tuesday, February 05, 2013 10:31 PM by John Paul Cook

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

No Comments

Leave a Comment

(required) 
(required) 
Submit

About John Paul Cook

John Paul Cook is both a Registered Nurse and a Microsoft SQL Server MVP experienced in Microsoft SQL Server and Oracle database application design, development, and implementation. He has spoken at many conferences including Microsoft TechEd and the SQL PASS Summit. He has worked in oil and gas, financial, manufacturing, and healthcare industries. Experienced in systems integration and workflow analysis, John is passionate about combining his IT experience with his nursing background to solve difficult problems in healthcare. He sees opportunities in using business intelligence and Big Data to satisfy healthcare meaningful use requirements and improve patient outcomes. John graduated from Vanderbilt University with a Master of Science in Nursing Informatics and is an active member of the Sigma Theta Tau nursing honor society. Contributing author to SQL Server MVP Deep Dives and SQL Server MVP Deep Dives Volume 2.

This Blog

Syndication

Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement