sql-server - 在活动事务上从 SNAPSHOT 切换隔离级别并运行 DDL

Question

我想强制 MS SQL Server 使用 SNAPSHOT 隔离级别，但是在同一个事务中，我必须运行 DML 和 DDL 操作。据我所知，我们无法在具有 SNAPSHOT 隔离级别的事务中运行 DDL（大部分 DDL 操作）。

在文章https://docs.microsoft.com/en-us/sql/t-sql/statements/set-transaction-isolation-level-transact-sql?view=sql-server-2017我们可以阅读

如果事务以 SNAPSHOT 隔离级别启动，您可以将其更改为另一个隔离级别，然后再返回到 SNAPSHOT。事务在第一次访问数据时开始。

所以我的想法是使用 SNAPSHOT 隔离级别运行 DML 事务，然后将事务切换到 READ COMMITTED 隔离级别，这是一个问题。

/* 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

结果，我得到

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.

所以看起来不可能，但我找不到不可能的严格答案。

我怀疑SET TRANSACTION ISOLATION LEVEL ...在一个打开的事务上正在改变锁定的行为，但不是整个事务类型。

标签： sql-servertransactionsisolation-levelsnapshot-isolation