使用“SET XACT_ABORT ON"有什么好处?在存储过程中? [英] What is the benefit of using "SET XACT_ABORT ON" in a stored procedure?

问题描述

在存储过程中使用 SET XACT_ABORT ON 有什么好处?

What is the benefit of using SET XACT_ABORT ON in a stored procedure?

推荐答案

SET XACT_ABORT ON 指示 SQL Server 在发生运行时错误时回滚整个事务并中止批处理.它涵盖了客户端应用程序上而不是 SQL Server 本身内发生的命令超时等情况(默认 XACT_ABORT OFF 设置未涵盖这种情况.)

SET XACT_ABORT ON instructs SQL Server to rollback the entire transaction and abort the batch when a run-time error occurs. It covers you in cases like a command timeout occurring on the client application rather than within SQL Server itself (which isn't covered by the default XACT_ABORT OFF setting.)

由于查询超时将使事务保持打开状态，因此在具有显式事务的所有存储过程中建议使用 SET XACT_ABORT ON(除非您有特定的理由不这样做)作为应用程序执行的结果处理具有开放事务的连接是灾难性的.

Since a query timeout will leave the transaction open, SET XACT_ABORT ON is recommended in all stored procedures with explicit transactions (unless you have a specific reason to do otherwise) as the consequences of an application performing work on a connection with an open transaction are disastrous.

Dan Guzman 的博客、

这篇关于使用“SET XACT_ABORT ON"有什么好处?在存储过程中?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！