这个交易对我来说是回滚吗? [英] Is this TRANSACTION being ROLLBACK(ed) for me?

问题描述

如果我尝试通过创建现有表而导致错误，则现有事务似乎已经回滚:

If I cause an error by trying to create an existing table, the existing transaction appears to have already rolled back itself:

private void CreateSomeThings()
{
    SqlConnection SqlConn = new SqlConnection(ConnectionString);
    SqlConn.Open();

    (new SqlCommand("BEGIN TRANSACTION", SqlConn)).ExecuteNonQuery();

    try
    {
        (new SqlCommand("CREATE TABLE sometable ([some_id] [int] IDENTITY(1,1) NOT NULL)", SqlConn)).ExecuteNonQuery();

        // Create the table again, but carry on by catching the exception
        try
        {
            (new SqlCommand("CREATE TABLE sometable ([some_id] [int] IDENTITY(1,1) NOT NULL)", SqlConn)).ExecuteNonQuery();
        }
        catch (Exception)
        {
        }

        // If another exception is thrown
        (new SqlCommand("bingy bongy boo", SqlConn)).ExecuteNonQuery();

        (new SqlCommand("COMMIT TRANSACTION", SqlConn)).ExecuteNonQuery();
    }
    catch (Exception Ex)
    {
        try
        {
            // ... then this command will fail with "no corresponding BEGIN TRANSACTION"
            (new SqlCommand("ROLLBACK TRANSACTION", SqlConn)).ExecuteNonQuery();
        }
        catch (Exception Ex2)
        {
            throw;
        }
    }
}

我想了解发生了什么以及为什么.我希望事务回滚是我的责任-发生其他错误时，它不会那样做:例如，如果我只调用"bingy bongy"，则只有该调用会引发异常，然后我在异常中的ROLLBACK不会出现任何问题.

I'd like to understand what's going on and why. I would expect that transaction rollback is my responsibility - with other errors it doesn't do that: for example, if I just call "bingy bongy" only the call throws an exception and I then ROLLBACK in the exception without any issues.

推荐答案

SQL Server可以单方面决定回滚您的事务.这是SQL Server中的严重设计缺陷，因为您的应用程序永远无法知道事务是否仍处于活动状态.没有很好地记录什么类型的错误会回滚，哪些类型的错误不会回滚.例如，我想我记得唯一的键冲突和其他数据错误不会回滚.但是其他人确实如此.某些错误甚至会终止连接(这是罕见的，不是设计缺陷).

SQL Server can unilaterally decide to rollback your transaction. This is a severe design flaw in SQL Server because your app can never know whether the transaction is still active or not. It is not well documented what kinds of errors roll back and what kinds of errors don't. For example, I think I remember that unique key violations and other data errors do not roll back. But others do. Some errors even terminate the connection (that is rare and not a design flaw).

我建议您以这样的方式进行编码:在第一个错误时中止事务，然后失败或重试所有操作.这样可以省去很多麻烦.最好每批执行一条语句，否则您有冒险在事务外运行第二条语句的风险.

I recommend that you code in such a way that you abort the transaction at the first error and then either fail or retry everything. That saves you a lot of headaches. Prefer to execute one statement per batch, or you risk running the 2nd statement outside of a transaction.

如果您真的想继续追寻错误，则必须做两件事:

If you really want to keep going after errors you must do two things:

1. 建立一个不会回滚的错误白名单.在这种情况下，您可以继续前进.
2. 使用SELECT @@TRANCOUNT检查交易是否仍在进行中.

1. Build a whitelist of errors that do not roll back. In that case you can keep going.
2. Check with SELECT @@TRANCOUNT whether the transaction is still live.

这篇关于这个交易对我来说是回滚吗?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！