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?
Best Answer
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:
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 useSERIALIZABLE
(which will take and hold range locks)Looking at the output of
sys.dm_tran_locks
andsys.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.