sql-server - 为什么 CATCH block 允许在 SQL Server 2012 中提交事务

Question

最近，由于遗留代码中的以下语句，我遇到了 SQL 错误。它尝试删除临时表，如果尚未定义则继续。显然，这是检查临时表是否存在的糟糕方法，但这不是我的问题。

BEGIN TRY 
    DROP TABLE #my_temp_table
END TRY 
BEGIN CATCH 

END CATCH 

该语句按原样运行良好(没有任何错误)，但是一旦将其放入 Begin Tran/Commit Tran block (如下所示)，该行为就会变得有趣。

BEGIN TRAN
    BEGIN TRY 
        DROP TABLE #my_temp_table
    END TRY 
    BEGIN CATCH 

    END CATCH 
COMMIT TRAN

我的理解是Try..Catch block 不会影响事务——一旦进入Catch block ，事务将处于不可提交状态，并且事务将被回滚，这就是我在SQL上看到的服务器 2008 R2 (SP1) - 10.50.2550.0。当它在 Begin Tran/Commit Tran block 内执行时，我们会得到一个错误:

Msg 3930, Level 16, State 1, Line 8
The current transaction cannot be committed and cannot support operations that write to the log file. Roll back the transaction.
Msg 3998, Level 16, State 1, Line 1
Uncommittable transaction is detected at the end of the batch. The transaction is rolled back.

但是，它在 SQL Server 2012 - 11.0.5058.0 上运行时没有任何错误。 XACT_STATE() 在 END CATCH 行后返回 1。事务将被提交，如果 DROP TABLE 语句前后有其他数据更改，则更改将保留。

BEGIN TRAN
    BEGIN TRY 
        DROP TABLE #my_temp_table
    END TRY 
    BEGIN CATCH 

    END CATCH 
    PRINT XACT_STATE()
COMMIT TRAN

在所有这些测试中，我已确保 XACT_ABORT 处于关闭状态。所以我的问题是什么会导致这种行为差异。 2008 R2 和 2012 之间确实有不同吗？或者是某些服务器/数据库设置控制了 Try...Catch block 和事务的工作方式。

<小时/>

编辑 1:我尝试在 2008 R2 和 2012 实例上运行以下脚本。我还尝试将 INSERT dbo.UserOptionsLog 行放在不同的位置，在 Begin Tran 之前、Begin Try 之后、Begin Catch 之后、commit tran 之后，但它不会改变结果。

IF NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'dbo' AND TABLE_NAME = 'UserOptionsLog')
    CREATE TABLE dbo.UserOptionsLog([Set Option] SYSNAME, [Value] VARCHAR(100), ID INT IDENTITY NOT NULL)


BEGIN TRAN
    DELETE FROM dbo.UserOptionsLog
    INSERT dbo.UserOptionsLog EXEC('DBCC USEROPTIONS')
    SELECT * FROM dbo.UserOptionsLog

    BEGIN TRY 

        DROP TABLE #my_temp_table
    END TRY 
    BEGIN CATCH 

    END CATCH 
COMMIT TRAN

2008 R2 实例的结果。

Set Option  Value   ID
textsize    2147483647  40
language    us_english  41
dateformat  mdy 42
datefirst   7   43
lock_timeout    -1  44
quoted_identifier   SET 45
arithabort  SET 46
ansi_null_dflt_on   SET 47
ansi_warnings   SET 48
ansi_padding    SET 49
ansi_nulls  SET 50
concat_null_yields_null SET 51
isolation level read committed  52

来自 2008 R2 实例的消息

(0 row(s) affected)
DBCC execution completed. If DBCC printed error messages, contact your system administrator.

(13 row(s) affected)

(13 row(s) affected)
Msg 3930, Level 16, State 1, Line 18
The current transaction cannot be committed and cannot support operations that write to the log file. Roll back the transaction.
Msg 3998, Level 16, State 1, Line 1
Uncommittable transaction is detected at the end of the batch. The transaction is rolled back.

2012 年实例的结果。

Set Option  Value   ID
textsize    2147483647  53
language    us_english  54
dateformat  mdy 55
datefirst   7   56
lock_timeout    -1  57
quoted_identifier   SET 58
arithabort  SET 59
ansi_null_dflt_on   SET 60
ansi_warnings   SET 61
ansi_padding    SET 62
ansi_nulls  SET 63
concat_null_yields_null SET 64
isolation level read committed  65

来自 2012 年实例的消息。

(13 row(s) affected)
DBCC execution completed. If DBCC printed error messages, contact your system administrator.

(13 row(s) affected)

(13 row(s) affected)

Answer 1

[...]and that's what I see on SQL Server 2008 R2 (SP1) - 10.50.2550.0. When it's executed inside a Begin Tran/Commit Tran block, we will get an error:

Msg 3930, Level 16, State 1, Line 8 The current transaction cannot be committed and cannot support operations that write to the log file. Roll back the transaction. Msg 3998, Level 16, State 1, Line 1 Uncommittable transaction is detected at the end of the batch. The transaction is rolled back. However, it runs without any error on SQL Server 2012 - 11.0.5058.0. [...]

我相信造成这种差异的原因。行为是 XACT_ABORT 设置的值。

当 OFF 后 XACT_STATE() 返回 1 时，TX 可提交，并且 COMMIT TRAN 正在执行且没有错误:

SET XACT_ABORT OFF
BEGIN TRAN
    BEGIN TRY 
        DROP TABLE #my_temp_table
    END TRY 
    BEGIN CATCH 

    END CATCH 
    PRINT XACT_STATE()
COMMIT TRAN

但是什么时候ON

SET XACT_ABORT ON
BEGIN TRAN
    BEGIN TRY 
        DROP TABLE #my_temp_table
    END TRY 
    BEGIN CATCH 

    END CATCH 
    PRINT XACT_STATE()
COMMIT TRAN

由于 CATCH block 拦截错误/异常，TX 变为圆顶/不可提交 (-1) 且 COMMIT TRAN 引发另一个错误/异常(exception):

-1
Msg 3930, Level 16, State 1, Line 10
The current transaction cannot be committed and cannot support operations that write to the log file. Roll back the transaction.

更新:

我在 SQL2008R2 上重现了报告的行为。在2008R2上，似乎无论XACT_ABORT(ON/OFF)的值是什么，TX都变得不可提交。 SQL2012 改变了这种行为:只有当 XACT_ABORT 为 ON 时，TX 才变得不可提交。