Reputation: 476
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