Adrian
Adrian

Reputation: 476

Switch isolation level from SNAPSHOT on active transaction and run DDL

I would like to force MS SQL Server to use SNAPSHOT isolation level, but in the same transaction, I have to run DML and DDL operation. As I know we can't run DDL (most of DDL operation) in a transaction with SNAPSHOT isolation level.

In article https://learn.microsoft.com/en-us/sql/t-sql/statements/set-transaction-isolation-level-transact-sql?view=sql-server-2017 we can read

If a transaction starts in the SNAPSHOT isolation level, you can change it to another isolation level and then back to SNAPSHOT. A transaction starts the first time it accesses data.

So my idea is to run a transaction with SNAPSHOT isolation level do DML and then switch transaction to READ COMMITTED isolation level and here is a problem.

/* the problematic part */
EXEC print_curr_il 'on start'
SET TRANSACTION ISOLATION LEVEL SNAPSHOT
EXEC print_curr_il 'after setting il to snapshot'

BEGIN TRAN 
    EXEC print_curr_il 'before inserting'
    INSERT INTO tbl2 VALUES ('some value')  
    SET TRANSACTION ISOLATION LEVEL READ COMMITTED
    EXEC print_curr_il 'after setting il to read committed'
    ALTER TABLE tbl1 ADD val int
COMMIT

/* simple tables */
CREATE TABLE [dbo].[tbl1](
    [text] [nchar](10) NULL
)
GO

CREATE TABLE [dbo].[tbl2](
    [text] [nchar](10) NULL
)
GO

/* procedeure to print current isolation level */
CREATE PROCEDURE [dbo].[print_curr_il]
    @desc varchar(50)
AS
BEGIN
    Declare @il varchar(50);
    SELECT @il = 
        CASE transaction_isolation_level 
            WHEN 0 THEN 'Unspecified' 
            WHEN 1 THEN 'ReadUncommitted' 
            WHEN 2 THEN 'ReadCommitted' 
            WHEN 3 THEN 'Repeatable' 
            WHEN 4 THEN 'Serializable' 
            WHEN 5 THEN 'Snapshot'
        END 
    FROM sys.dm_exec_sessions where session_id = @@SPID;
    print @desc + ' Isolation Level = ' + @il;
END
GO

As a result, I get

on start Isolation Level = ReadCommitted
after setting il to snapshot Isolation Level = Snapshot
before inserting Isolation Level = Snapshot

(1 row affected)
after setting il to read committed Isolation Level = ReadCommitted
Msg 3964, Level 16, State 1, Line 11
Transaction failed because this DDL statement is not allowed inside a snapshot isolation transaction. Since metadata is not versioned, a metadata change can lead to inconsistency if mixed within snapshot isolation.

So it looks like impossible, but I can't find the strict answer that it is impossible.

I suspect that SET TRANSACTION ISOLATION LEVEL ... on an open transaction is changing the behavior of locking, but not whole transaction type.

Upvotes: 4

Views: 587

Answers (0)

Related Questions