我真的需要使用“set xact_abort on”吗?

请记住,无论有没有错误,Try-Catch都无法捕获。 XACT_ABORT .

然而, SET XACT_ABORT ON 不影响错误捕获。但它确实保证了任何交易都会被回滚/终结。当“关闭”时,您仍然可以选择提交或回滚(取决于xact_状态)。这是SQL 2005的主要行为变化 XACTH中止

如果客户机命令超时启动,客户机发送“abort”指令,它还将删除锁等。没有 SET XACT_ABORT ,如果连接保持打开,则锁可以保留。我的同事(MVP)和我在年初对此进行了彻底的测试。

我认为,在执行分布式事务时,需要设置xact_abort on。

From the books on line: 对于大多数OLE DB提供程序(包括SQL Server)的隐式或显式事务中的数据修改语句,必须设置xact_abort。唯一不需要此选项的情况是提供程序支持嵌套事务。有关详细信息,请参阅分布式查询和分布式事务。

xact_abort确实会影响错误处理:当遇到错误时,它将中止整个批处理,以及产生错误的行后面的任何代码(包括错误检查!)永远不会执行。此行为有两个异常:xact_abort被try…catch取代(catch块将始终执行,事务不会自动回滚,只呈现不可接受的状态),xact_abort将忽略raiserror。

我的理解是,即使使用了try catch,并且catch块中没有使用rollback语句,当 XACT_ABORT 开始了。

有一个警告就是要盲目使用 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

我已经改变了自己。 从未 使用 设置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 on很酷,让我们使用它

所以,代码是有效的。如果有错误,我们 期待 ,我们处理它并继续。这叫做 处理错误 . 如果一些 未知的 异常发生(我们没有预料到的),我们 rollback 可能正在进行的任何事务。

现在让我们看看我们是否盲目地遵循 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);
}

你看到会导致 道格斯 ?

道格斯 已正确写入以处理错误情况。但是介绍 XACTH中止 到连接现在将导致数据库损坏。对于那些看不到bug的人,让我们浏览一下代码:

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;
   }
}

正确编写并能正常工作的代码会损坏,导致错误,最坏情况下还会导致数据库损坏。都是因为我打开了 XACTH中止 .

当xact_abort在trigger中设置为off时,我调用trigger body中的raiseError,更改不会回滚。