sql-server - 使用 SP_ExecuteSQL 时事务中断

Question

我使用的是 SQLServer 2014，我有一个简单的数据库，其中有一个表，该表有一个 ID 和一个名为 data 的 varchar 列。当我运行以下语句时出现一些奇怪的行为:

SET XACT_ABORT ON
BEGIN TRANSACTION
    exec sp_executesql N'some nonsense'
    insert into testTable values ('b')

COMMIT  

SSMS 显示出现错误，因为我尝试在 sp_executesql 调用中运行不正确的查询。但是，它还显示 1 行受影响。如果我在 testTable 上运行选择查询，我可以看到值“b”已插入。

如果我将语句包装在 TRY/CATCH block 中，一切都会按预期工作，并且整个事务操作将回滚:

BEGIN TRANSACTION
BEGIN TRY
    exec sp_executesql N'some nonsense'
    insert into testTable values ('b')

    COMMIT
END TRY
BEGIN CATCH
    SELECT ERROR_MESSAGE()
    ROLLBACK
END CATCH

SET XACT_ABORT ON 不应该确保在出现问题时回滚整个事务吗？我缺少什么设置吗？

谢谢

Answer 1

发生这种情况是因为运行时语法错误未包含在 TRY 中/CATCH不会中止事件事务，即使使用 XACT_ABORT设置为ON 。关于什么会中止、什么不会中止以及在什么情况下中止的确切规则一点也不简单或明显。 Erland Sommarskog 有 an excellent write-up on error handling in general和 the rules of what does and doesn't abort in particular .

我不会在这里重现所有内容，但这里的问题归结为其要点:

SET XACT_ABORT ON   -- or OFF, it makes no difference
BEGIN TRANSACTION
EXEC ('SELECT')     -- Incorrect syntax near 'SELECT'
PRINT @@TRANCOUNT   -- Prints 1, transaction is still going
COMMIT
PRINT @@TRANCOUNT   -- Prints 0, transaction succeeded

尽管XACT_ABORT ON ，执行不仅不会停止，事务甚至不会中止。添加TRY/CATCH更改规则:

SET XACT_ABORT ON
BEGIN TRANSACTION
BEGIN TRY
    EXEC ('SELECT')              -- Incorrect syntax near 'SELECT'
    PRINT 'After bad statement.' -- Does not print
    COMMIT
END TRY
BEGIN CATCH
    PRINT @@TRANCOUNT            -- Prints 1, transaction is still going, but it's doomed
END CATCH
-- Error here: 
-- 'Uncommittable transaction is detected at the end of the batch. 
--  The transaction is rolled back.'

现在事务注定要失败，如果我们不自己回滚它，SQL Server 会为我们执行此操作(但会出现错误)。这种厄运完全归功于 XACT_ABORT ，因为关闭它会再次产生不同的结果:

SET XACT_ABORT OFF
BEGIN TRANSACTION
BEGIN TRY
    EXEC ('SELECT')               -- Incorrect syntax near 'SELECT'
    PRINT 'After bad statement.'  -- Does not print
    COMMIT
END TRY
BEGIN CATCH
    PRINT @@TRANCOUNT             -- Prints 1, transaction is still going
END CATCH
PRINT @@TRANCOUNT                 -- Prints 1, transaction is still going!
ROLLBACK

这个故事的寓意是:T-SQL 中正确的错误处理非常棘手。通常对我有用的是SET XACT_ABORT ON对于任何重要的批量语句，以及完全在 SQL Server 外部启动、提交或回滚事务(通过客户端代码)。这避免了理解什么会停止或破坏事务、什么不会停止或毁灭事务的许多困难，因为 SQL Server 传回客户端的任何错误最终都会导致回滚。但是，当然，即使这样也不是 Elixir 。