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.