THE SQL Server Blog Spot on the Web

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

The Rambling DBA: Jonathan Kehayias

The random ramblings and rantings of frazzled SQL Server DBA

The Scope of Transaction Isolation Levels

This week a forums post had some incorrect information in it about the scope and effect of SET TRANSACTION ISOLATION LEVEL during query execution.  I tried to politely correct the information, but I also thought I’d blog about it as well.  The incorrect information was:

CREATE PROCEDURE Mysp 
AS 
  BEGIN 
    SET TRANSACTION  ISOLATION  LEVEL  READ  UNCOMMITTED 
     
    BEGIN TRAN 
--you are changing isoloation level from default read commited to read uncommited 
     
   
SELECT
   
FROM   mytable 
     
   
COMMIT TRAN  --back to default isolation level 
 
END

Now honestly, depending on how you read this, the answer could be right or wrong.  The problem lies in the COMMIT TRAN comment.  It can be read two different ways.  If it is read that the COMMIT TRAN returns the execution context back to the default isolation level, then the information is incorrect.  However, if you look at the procedure as a whole, the COMMIT TRAN is the last statement before it ends its execution, so when the scope changes back to the caller of the stored procedure, the isolation level returns to isolation level of the calling session, which is correct information. 

Kalen points out in the comments below that there is actually another mistake here.  The first comment could be both correct/incorrect as well.  It says changing from the default READ COMMITTED, which is not necessarily the change that could be being made.  The default for SQL is READ COMMITTED (http://msdn.microsoft.com/en-us/library/ms175909.aspx), but as shown in the example below, it will be changed to READ UNCOMMITTED from whatever the isolation level of the calling session was currently set at and only for the duration of the exection of the stored procedure.

To demonstrate this, consider the following:

SET NOCOUNT ON

USE
tempdb;
GO
CREATE PROCEDURE dbo.Read_Uncommitted
AS
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

DECLARE
@isolation_level VARCHAR(100)

SELECT @isolation_level
  
CASE transaction_isolation_level
     
WHEN 0 THEN 'Unspecified'
     
WHEN 1 THEN 'ReadUncomitted'
     
WHEN 2 THEN 'ReadCommitted'
     
WHEN 3 THEN 'Repeatable'
     
WHEN 4 THEN 'Serializable'
     
WHEN 5 THEN 'Snapshot'
     
ELSE 'UNKNOWN'
  
END
FROM
sys.dm_exec_requests
WHERE session_id = @@spid;

PRINT 'Inside of dbo.Read_Uncommitted the isolation level is: ' + @isolation_level

GO
CREATE PROCEDURE dbo.Serial_izable
AS
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE

DECLARE
@isolation_level VARCHAR(100)

SELECT @isolation_level
  
CASE transaction_isolation_level
     
WHEN 0 THEN 'Unspecified'
     
WHEN 1 THEN 'ReadUncomitted'
     
WHEN 2 THEN 'ReadCommitted'
     
WHEN 3 THEN 'Repeatable'
     
WHEN 4 THEN 'Serializable'
     
WHEN 5 THEN 'Snapshot'
     
ELSE 'UNKNOWN'
  
END
FROM
sys.dm_exec_requests
WHERE session_id = @@spid;

PRINT 'Inside of dbo.Serial_izable the isolation level is: ' + @isolation_level

PRINT 'Executing dbo.Read_Uncommitted from dbo.Serializable'

EXEC dbo.Read_Uncommitted;

PRINT 'Execution of dbo.Read_Uncommitted completed'

SELECT @isolation_level
  
CASE transaction_isolation_level
     
WHEN 0 THEN 'Unspecified'
     
WHEN 1 THEN 'ReadUncomitted'
     
WHEN 2 THEN 'ReadCommitted'
     
WHEN 3 THEN 'Repeatable'
     
WHEN 4 THEN 'Serializable'
     
WHEN 5 THEN 'Snapshot'
     
ELSE 'UNKNOWN'
  
END
FROM
sys.dm_exec_requests
WHERE session_id = @@spid;

PRINT 'Inside of dbo.Serial_izable the isolation level is: ' + @isolation_level

GO


-- Test it all out
SET TRANSACTION ISOLATION LEVEL SNAPSHOT 

DECLARE @isolation_level VARCHAR(100)

SELECT @isolation_level
  
CASE transaction_isolation_level
     
WHEN 0 THEN 'Unspecified'
     
WHEN 1 THEN 'ReadUncomitted'
     
WHEN 2 THEN 'ReadCommitted'
     
WHEN 3 THEN 'Repeatable'
     
WHEN 4 THEN 'Serializable'
     
WHEN 5 THEN 'Snapshot'
     
ELSE 'UNKNOWN'
  
END
FROM
sys.dm_exec_requests
WHERE session_id = @@spid;

PRINT 'The isolation level is: ' + @isolation_level


PRINT 'Beginning Transaction'
BEGIN TRAN  --you are changing isoloation level from the current level to read uncommited

SELECT 
  
CASE transaction_isolation_level
     
WHEN 0 THEN 'Unspecified'
     
WHEN 1 THEN 'ReadUncomitted'
     
WHEN 2 THEN 'ReadCommitted'
     
WHEN 3 THEN 'Repeatable'
     
WHEN 4 THEN 'Serializable'
     
WHEN 5 THEN 'Snapshot'
     
ELSE 'UNKNOWN'
  
END AS transaction_isolation_level_name
FROM sys.dm_exec_requests
WHERE session_id = @@spid;

COMMIT TRAN  -- does not affect the isolation level

SELECT @isolation_level
  
CASE transaction_isolation_level
     
WHEN 0 THEN 'Unspecified'
     
WHEN 1 THEN 'ReadUncomitted'
     
WHEN 2 THEN 'ReadCommitted'
     
WHEN 3 THEN 'Repeatable'
     
WHEN 4 THEN 'Serializable'
     
WHEN 5 THEN 'Snapshot'
     
ELSE 'UNKNOWN'
  
END
FROM
sys.dm_exec_requests
WHERE session_id = @@spid;

PRINT 'After the transaction the isolation level is: ' + @isolation_level

PRINT 'Executing dbo.Serial_izable'

EXEC dbo.Serial_izable

PRINT 'Execution of dbo.Serial_izable complete'

SELECT @isolation_level
  
CASE transaction_isolation_level
     
WHEN 0 THEN 'Unspecified'
     
WHEN 1 THEN 'ReadUncomitted'
     
WHEN 2 THEN 'ReadCommitted'
     
WHEN 3 THEN 'Repeatable'
     
WHEN 4 THEN 'Serializable'
     
WHEN 5 THEN 'Snapshot'
     
ELSE 'UNKNOWN'
  
END
FROM
sys.dm_exec_requests
WHERE session_id = @@spid;

PRINT 'The isolation level is: ' + @isolation_level

GO

DROP PROCEDURE dbo.Serial_izable
DROP PROCEDURE dbo.Read_Uncommitted

The output of this should be:

The isolation level is: Snapshot
Beginning Transaction
After the transaction the isolation level is: Snapshot
Executing dbo.Serial_izable
Inside of dbo.Serial_izable the isolation level is: Serializable
Executing dbo.Read_Uncommitted from dbo.Serializable
Inside of dbo.Read_Uncommitted the isolation level is: ReadUncomitted
Execution of dbo.Read_Uncommitted completed
Inside of dbo.Serial_izable the isolation level is: Serializable
Execution of dbo.Serial_izable complete
The isolation level is: Snapshot

This shows that the changes made to the TRANSACTION ISOLATION LEVEL are only scoped to the stored procedure and that issuing a COMMIT TRAN won’t revert the isolation level back.  Maybe it helps someone, maybe not, but I thought I’d blog it anyway.

Published Friday, November 20, 2009 3:21 PM by Jonathan Kehayias
Filed under:

Comments

 

Jonathan Kehayias said:

Thank you for pointing that out Kalen.  I corrected the wording to reflect that it would return back to the isolation level of the calling session.  Using the word default can be misleading because the default isolation level depends on what client is actually making the connection.  You could for example change the default isolation level of SSMS to something other than READ COMMITTED in Tools -> Options -> Query Execution -> SQL Server -> Advanced.  

November 20, 2009 4:07 PM
 

Kalen Delaney said:

I would really prefer to always think of READ COMMITTED as the default. If people think they can change the default in SSMS, what happens when that code is then run using another tool that doesn't change the isolation level for them?

November 20, 2009 5:56 PM
 

Kalen Delaney said:

Also, the comments in the code still say you are changing from the default READ COMMITTED, when you are actually changing from something else.

November 20, 2009 5:57 PM
 

noeldr said:

This has always been very clear in BOL:

http://msdn.microsoft.com/en-us/library/ms173763.aspx

Relevant Part:

"

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. For example, if you set REPEATABLE READ in a batch, and the batch then calls a stored procedure that sets the isolation level to SERIALIZABLE, the isolation level setting reverts to REPEATABLE READ when the stored procedure returns control to the batch.

"

November 20, 2009 6:22 PM
 

Lee Everest said:

Jonathan - super job on this post. Thanks.

Lee

November 20, 2009 7:13 PM
 

Jonathan Kehayias said:

Kalen,

Thank you again.  I didn't realize I had copied the comments down into my code block that I was putting in here for the example.  I corrected it and added your note about the original code example as well.

I always thought of READ COMMITTED as the default until I worked with a legacy COM+ application that still forced Serializable as the default, but point taken.

November 20, 2009 7:22 PM
 

Jonathan Kehayias said:

Noel,

Thanks for posting the link to the BOL. The scope of changes to isolation level and set options might be clear to someone who has been using SQL Server for a while and has read the BOL, but its not to someone new to SQL which is generally the people who are asking these kinds of questions, and sometimes trying to answer them as well.

November 20, 2009 7:29 PM
Anonymous comments are disabled

This Blog

Syndication

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