如何使 SET XACT_ABORT ON 回滚事务？

Question

根据Books Online documentation of SET XACT_ABORT ON，我得出的印象是，如果T-SQL语句引发运行时错误，则整个事务将被终止并回滚：

备注

当 SET XACT_ABORT 为 ON 时，如果 Transact-SQL 语句引发运行时错误，则整个事务将被终止并回滚。

在 SQL Server 2008 R2 中进行测试：

SET XACT_ABORT ON;
BEGIN TRANSACTION;
PRINT 'TranCount befor an error = '+CAST(@@Trancount AS varchar(50))

DROP TABLE QuertyAsdf

PRINT 'TranCount after an error = '+CAST(@@Trancount AS varchar(50))

输出结果为：

TranCount befor an error = 1
Msg 3701, Level 11, State 5, Line 6
Cannot drop the table 'QwertyAsdf', because it does not exist or you do not have permission.
TranCount after an error = 1

我之前也曾认为 SET XACT_ABORT ON 会在出现错误时终止批处理：

SET XACT_ABORT ON 指示SQL Server在运行期间出现错误时回滚整个事务并中止批处理。

听起来很方便。我该如何让它也执行这个操作呢？

- Ian Boyd

---

当设置XACT_ABORT为ON时，并非所有错误都会终止批处理... 编译错误（通常终止作用域的错误）并不会终止批处理。 - Martin Smith

@MartinSmith 可能这不是编译错误，因为它已经通过了编译，并开始执行批处理。 - Ian Boyd

一般来说，引用不存在的对象的语句会被延迟编译。不确定DDL语句是否也是如此。 - Martin Smith

https://dev59.com/dHNA5IYBdhLWcg3wjOlS#919279 - user1293068

Answer 1

SQL Server只有在严重级别大于或等于16时才会回滚事务。

以下是示例：

Msg 544, Level 16, State 1, Line 1
Cannot insert explicit value for identity column in table 'ORC_ORCAMENTO' whenIDENTITY_INSERT is set to OFF.

测试SQL Server 2008 R2

SET XACT_ABORT ON;
BEGIN TRANSACTION;
PRINT 'TranCount befor an error = '+CAST(@@Trancount AS varchar(50))
insert into ORC_ORCAMENTO (ORCID, ORCNOME, ORCATIVO) VALUES (1, 'TESTE_ALEXP', 0);
PRINT 'TranCount after an error = '+CAST(@@Trancount AS varchar(50))

返回结果

TranCount befor an error = 1
Msg 544, Level 16, State 1, Line 5
Cannot insert explicit value for identity column in table 'ORC_ORCAMENTO' when IDENTITY_INSERT is set to OFF.
TranCount after an error = 0

查看Microsoft错误消息级别，请访问：

https://learn.microsoft.com/en-us/sql/relational-databases/errors-events/database-engine-events-and-errors

- Roger Medeiros

Answer 2

当您在 try catch 语句中使用 xact abort on 时，您可以手动引发错误以使事务回滚。

set xact_abort on;

begin try
    ...dml statements here....

if conditions here...
    raiseerror(....);

end try
begin catch

end catch

- AgentSQL