Change transaction isolation level inside running transaction

Question

I'm studying transaction isolation levels in SQL Server, and I'm trying to figure out how SQL Server behaves when the isolation level changes during the lifetime of the transaction.

It seems that something like this is possible in SQL Server:

BEGIN TRANSACTION;

SET TRANSACTION ISOLATION LEVEL READ COMMITTED;

/* (some selects/inserts/updates/deletes) */

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;

/* (some selects/inserts/updates/deletes) */

COMMIT TRANSACTION;

To be honest, I cannot think of an example when a decrease in the isolation level would make sense, I can only think of some scenarios where a part of the transaction would require serializable isolation while other parts wouldn't. I have a feeling that mixing isolation levels using snapshots and row-versioning with other kinds of isolation levels won't work well, but I cannot find much information to back this up.

Is it a thing that happens in practice that a single transaction switches between multiple isolation levels during its lifetime? Are there some caveats and details to know about?

Answer 1

The syntax you are using is valid, all statements executed after the SET TRANSACTION ISOLATION LEVEL command will use the specified isolation level.

Consider the following table:

CREATE TABLE dbo.test
(
    ID int identity(1,1) Primary key
    , Field nvarchar(10)
)
GO

INSERT INTO dbo.test(Field)
Values ('XXXXXXXXXX')
GO 100

Next, start a transaction and execute 2 statements.
The first statement will use REPEATABLE READ (which won't release its shared locks when the statement completes), the second statement will use SERIALIZABLE (which will take and hold range locks)

BEGIN TRANSACTION

SET TRANSACTION ISOLATION LEVEL REPEATABLE READ

SELECT *
FROM dbo.test 
WHERE id = 100

-- This will show a shared lock on key 100, the lock is kept during the transaction because repeatable read is used
SELECT *
FROM sys.dm_tran_locks 
WHERE request_session_id = @@SPID

-- Isolation level 3 = repeatable read
SELECT transaction_isolation_level 
FROM sys.dm_exec_sessions 
WHERE session_id = @@SPID

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE

SELECT *
FROM dbo.test
WHERE id >= 0 and id < 10

-- This will show the previous shared lock on key 100 + additional RangeS-S key locks taken by the serializable statement
SELECT *
FROM sys.dm_tran_locks
WHERE request_session_id = @@SPID

-- Isolation level 4 = Serializable
SELECT transaction_isolation_level 
FROM sys.dm_exec_sessions 
WHERE session_id = @@SPID

COMMIT TRANSACTION

Looking at the output of sys.dm_tran_locks and sys.dm_exec_sessions you can see that the two statements issue different locks and use different isolation levels within the same transaction. Although it's technically possible, I tend to use a single isolation level within a transaction.

When combining SNAPSHOT isolation with pessimistic locking you could get some unexpected results.

Imagine starting a transaction in SNAPSHOT isolation and reading some data.
Next, a second session updates the data you just read.
The first transaction switches to READ COMMITTED and reads the data again, SQL Server will now return the updated values.
Finally, you switch to SNAPSHOT isolation again and read the data. You'll use the previously created snapshot to read from and the old values will be returned.

-- Note: you have to set your isolation level to snapshot before starting your transaction otherwise you will get an error

SET TRANSACTION ISOLATION LEVEL SNAPSHOT 

BEGIN TRANSACTION 

-- Returns XXXXXXXXXX
SELECT * 
FROM dbo.test 
WHERE id = 1

-- Run "UPDATE dbo.test SET Field = 'YYYYYYYYYY' WHERE id = 1" in a separate session

SET TRANSACTION ISOLATION LEVEL READ COMMITTED 

-- Returns YYYYYYYYYY
SELECT * 
FROM dbo.test
WHERE id = 1

SET TRANSACTION ISOLATION LEVEL SNAPSHOT 

-- Returns XXXXXXXXXX
SELECT * 
FROM dbo.test 
WHERE id = 1

COMMIT