如果你很仔细,并且在所有操作周围使用TRY-CATCH,并在错误时回滚,你是否真的需要使用:
SET XACT_ABORT ON
换句话说,TRY-CATCH会错过哪些错误,而SET XACT_ABORT ON会处理?
如果你很仔细,并且在所有操作周围使用TRY-CATCH,并在错误时回滚,你是否真的需要使用:
SET XACT_ABORT ON
换句话说,TRY-CATCH会错过哪些错误,而SET XACT_ABORT ON会处理?
请记住,无论是否使用 XACT_ABORT
,TRY-CATCH
都无法捕获某些错误。
然而,SET XACT_ABORT ON
不会影响错误的捕获。它确保任何事务都被回滚/取消。当设置为“OFF”时,您仍然可以选择提交或回滚(取决于 xact_state)。这是 SQL 2005 中 XACT_ABORT
的主要行为变化。
它还会在客户端命令超时并且客户端发送“abort”指令时删除锁等待。如果不使用 SET XACT_ABORT
,则连接保持打开状态时锁定可能会保留。我和我的同事(一位 MVP)在今年年初对此进行了彻底的测试。
我相信在执行分布式事务时,设置SET XACT_ABORT ON是必需的。
根据官方文档: 对于大多数OLE DB提供程序(包括SQL Server),在隐式或显式事务中进行数据修改语句时,必须将XACT_ABORT设置为ON。唯一不需要此选项的情况是如果提供程序支持嵌套事务。有关更多信息,请参见分布式查询和分布式事务。
盲目地始终使用SET XACT_ABORT ON
是有一个注意事项的,最近我就中招了。
我在StackOverflow上阅读了一篇有说服力的论述,建议您应该始终使用XACT_ABORT ON
。我更改了系统以在连接期间设置该选项。然而,这却导致了数据损坏和很多痛苦。
begin transaction
try
perform insert
catch duplicate key violation and react appropriately
perform more actions
commit transaction
catch
rollback transaction
end
begin transaction
try
perform insert
catch duplicate key violation and react appropriately
transaction implicitly rolled back
perform more actions
commit transaction -> fails because not in a transaction
catch
rollback transaction -> fails because not i a transaction
end
我已经改变了我的想法。永远不要使用SET XACT_ABORT ON
。
编辑:人们似乎认为问题来自于在没有事务的情况下尝试调用ROLLBACK TRANSACTION
。他们认为如果没有正在进行的事务,则可以通过不调用ROLLBACK
来解决问题。
让我们使用一些伪代码,更改名称以保护NDA:
const
SQLNativeErrorPrimaryKeyViolation = 2627; //Primary keys. 2601 is for other unique index
void x(String sql)
{
database.Connection.ExecuteNoRecords(sql);
}
这是一种使答案更易读的学究式方法;我们使用x
来表示执行某个SQL语句:
void DoStuff()
{
x("BEGIN TRANSACTION");
try
{
try
{
x("INSERT INTO Patrons (AccountNumber, Name, Gender)"+
"VALUES (619, 'Shelby Jackson', 'W'");
}
catch (ESqlServerException e)
{
//check if the patron already exists (or some other hypothetical situation arises)
if (e.NativeError == SQLNativeErrorPrimaryKeyViolation)
{
//This patron already exists. Set their frob to grob because contoso the blingblong
x("UPDATE Patrons SET Frob='Grob' WHERE AccountNumber = 619");
//20110918: Dont forget we also need to bang the gardinker
x("EXECUTE BangTheGardinker @floof=619");
}
else
throw e;
}
//Continue with the stuff
x("EXECUTE Frob('{498BBB4D-D9F7-4438-B7A6-4AB5D57937C0}')");
//All done, commit the transaction
x("COMMIT TRANSACTION");
}
catch (Exception e)
{
//Something bad happened, rollback the transaction
//(if SQL Server didn't kill the transaction without our permission)
x("IF @@TRANCOUNT > 0 ROLLBACK TRANSACTION");
throw e;
}
}
所以,那段代码有效。如果出现我们预期的错误,我们会处理它并继续执行。这被称为处理错误。如果发生一些未知的异常(我们没有预料到的情况),我们会回滚
任何可能正在进行的事务。
现在让我们看看是否盲目地遵循XACT_ABORT
应该始终开启的建议:
DbConnection Connection()
{
if (_connection == null)
{
_connection = new SqlConnection();
//It is generally recommended that you always have xact_abort on.
//If a connection is closed with a transaction still in progress
//it still leaves locks held until that connection is finally recycled
//Also, when querying linked severs in a client-side transaction, the
//operation won't work until xact_abort is on (SQL Server will throw an saying xactabort is off
_connection.ExecuteNoRecords("SET XACT_ABORT ON");
}
return _connection;
}
void x(String sql)
{
database.Connection.ExecuteNoRecords(sql);
}
DoStuff正确地编写以处理错误情况。但是,将XACT_ABORT ON
引入连接将导致数据库损坏。对于那些没有发现漏洞的人,请跟随代码进行分析:
void DoStuff()
{
x("BEGIN TRANSACTION");
try
{
try
{
x("INSERT INTO Patrons (AccountNumber, Name, Gender)"+
"VALUES (619, 'Shelby Jackson', 'W'");
}
catch (ESqlServerException e)
{
//WARNING: WE ARE NO LONGER OPERATING IN A TRANASCTION
//Because XACT_ABORT is on, the transaction that we started has been implicitly rolled back.
//From now on, we are no longer in a transaction. If another error happens
//the changes we make cannot be rolled back
//check if the patron already exists (or some other hypothetical situation arises)
if (e.NativeError == SQLNativeErrorPrimaryKeyViolation)
{
//WARNING: This update happens outside of any transaction!
//This patron already exist. Set their frob to grob because contoso the blingblong
x("UPDATE Patrons SET Frob='Grob' WHERE AccountNumber = 619");
//WARNING: This stored procedure happens outside of any transaction!
//20110918: Dont forget we also need to bang the gardinker
x("EXECUTE BangTheGardinker @floof=619");
}
else
throw e;
}
//WARNING: This stored procedure happens outside of any transaction!
//If any error happens from
//Continue with the stuff
x("EXECUTE Frob('{498BBB4D-D9F7-4438-B7A6-4AB5D57937C0}')");
//WARNING: This stored procedure happens outside of any transaction. It will throw:
// Msg 3902, Level 16, State 1, Line 1
// The COMMIT TRANSACTION request has no corresponding BEGIN TRANSACTION.
//All done, commit the transaction
x("COMMIT TRANSACTION");
}
catch (Exception e)
{
//If there was an error during Frob, we would want to catch it and roll everything back.
//But since SQL Server ended the transaction, we have no way to rollback the changes
//And even if the call to Frob (or Updating the patron's Grob, or Banging the Gardinder)
//didn't fail, the call to COMMIT TRANSACTION will throw an error
//Either way, we have detected an error condition that cannot be rolled back in the database
//Something bad happened, rollback the transaction
//(if SQL Server didn't kill the transaction without our permission)
x("IF @@TRANCOUNT > 0 ROLLBACK TRANSACTION");
throw e;
}
}
原本正确且正常运行的代码,一旦我开启了 XACT_ABORT ON
,就会出现错误、甚至导致数据库损坏。
SET XACT_ABORT ON
添加到现有代码中可能会使其从正常工作的代码变成数据损坏。 - Ian Boyd.BeginTransaction
移到try
块中不会产生任何积极影响。事实上,这样做只会导致更多问题:如果将.BeginTransaction
移到try
块内,而在调用.BeginTransaction
时出现错误,那么异常处理程序将故意尝试回滚一个从未启动的事务。编写try-catch的正确方式是捕获需要处理错误的代码。 - undefined我的理解是,即使使用了try catch,并且在catch块中没有使用rollback语句,当XACT_ABORT为ON时,任何未提交的事务都将被回滚。
XACT_ABORT确实会影响错误处理:当遇到错误时,它将中止整个批处理,并且产生错误的行之后的任何代码(包括错误检查!)都将永远不会执行。但有两个例外情况:XACT_ABORT被TRY...CATCH覆盖(CATCH块将始终执行,并且事务不会自动回滚,只会变为不可提交状态),并且XACT_ABORT将忽略RAISERROR。
当触发器中的XACT_ABORT设置为OFF,并且我在触发器主体中调用RAISEERROR时,更改不会回滚。
RAISERROR
(注意一个E
)不会触发回滚,而应该使用Throw
。参考此链接顶部的“注意”内容:https://msdn.microsoft.com/en-us/library/ms188792.aspx - JohnLBevan