C# SQLTransaction 已完成错误

SQLTransaction has completed error

提问by LCJ

I got following error once in my application.

我在我的应用程序中遇到以下错误。

This SQLTransaction has completed; it is no longer usable

此 SQLTransaction 已完成；它不再可用

Stack Trace is attached below – It says about Zombie Checkand Rollback.

Stack Trace 附在下面——它说的是Zombie Check和Rollback。

What is the mistake in the code?

代码中有什么错误？

Note: This error came only once.

注意：此错误仅出现一次。

UPDATE

更新

From MSDN - SqlTransaction.Rollback Method

来自MSDN - SqlTransaction.Rollback 方法

A Rollback generates an InvalidOperationException if the connection is terminated or if the transaction has already been rolled back on the server.

如果连接终止或事务已在服务器上回滚，则回滚会生成 InvalidOperationException。

From Zombie check on Transaction - Error

从Zombie 检查交易 - 错误

One of the most frequent reasons I have seen this error showing up in various applications is, sharingSqlConnection across our application.

我在各种应用程序中看到此错误的最常见原因之一是，在我们的应用程序中共享SqlConnection。

CODE

代码

public int SaveUserLogOnInfo(int empID)
{
        int? sessionID = null;
        using (SqlConnection connection = new SqlConnection(connectionString))
        {
            connection.Open();
            SqlTransaction transaction = null;
            try
            {
                transaction = connection.BeginTransaction();
                sessionID = GetSessionIDForAssociate(connection, empID, transaction);

                    //Other Code

                //Commit
                transaction.Commit();
            }
            catch
            {
                //Rollback
                if (transaction != null)
                {
                    transaction.Rollback();
                    transaction.Dispose();
                    transaction = null;
                }

                //Throw exception
                throw;
            }
            finally
            {
                if (transaction != null)
                {
                    transaction.Dispose();
                }
            }
        }

        return Convert.ToInt32(sessionID,CultureInfo.InvariantCulture);

   }

Stack Trace

堆栈跟踪

---

---

REFERENCE:

参考：

1. What is zombie transaction?
2. Zombie check on Transaction - Error
3. SqlTransaction has completed
4. http://forums.asp.net/t/1579684.aspx/1
5. "This SqlTransaction has completed; it is no longer usable."... configuration error?
6. dotnet.sys-con.com - SqlClient Connection Pooling Exposed
7. Thread abort leaves zombie transactions and broken SqlConnection

1. 什么是僵尸交易？
2. 交易僵尸检查 - 错误
3. SqlTransaction 已完成
4. http://forums.asp.net/t/1579684.aspx/1
5. “此 SqlTransaction 已完成；它不再可用。”...配置错误？
6. dotnet.sys-con.com - SqlClient 连接池暴露
7. 线程中止留下僵尸事务和损坏的 SqlConnection

---

---

回答by Marc Gravell

Note: This error came only once.

注意：此错误仅出现一次。

then it is very hard to say much; it could be simply that the // Other Codeetc simply took to long, and the entire thing got killed. Maybe your connection died, or an admin deliberately killed it because you were blocking.

那么很难说太多；这可能只是因为// Other Code等只是花了很长时间，整个事情都被杀死了。也许您的连接已断开，或者管理员因您的阻塞而故意杀死它。

What is the mistake in the code?

代码中有什么错误？

over-complicating it; it can be much simpler:

过度复杂化；它可以简单得多：

using (var connection = new SqlConnection(connectionString))
{
    connection.Open();
    using(var transaction = connection.BeginTransaction())
    {
        try
        {
            sessionID = GetSessionIDForAssociate(connection, empID, transaction);
            //Other Code
            transaction.Commit();
         }
         catch
         {
            transaction.Rollback();
            throw;
         }
    }
}

much less code to get wrong.

出错的代码要少得多。

回答by Nikola Radosavljevi?

You should leave some of the work to compiler, to wrap that in a try/catch/finallyfor you.

你应该留下一些工作来编译，包装，在一个try/ catch/finally你。

Also, you should expect that Rollbackcan occasionally throw an exception, if a problem occurs in Commitstage, or if a connection to server breaks. For that reason you should wrap it in a try/catch.

此外，Rollback如果Commit阶段中出现问题，或者与服务器的连接中断，您应该期望偶尔会抛出异常。出于这个原因，您应该将其包装在try/ 中catch。

try
{
    transaction.Rollback();
}
catch (Exception ex2)
{
    // This catch block will handle any errors that may have occurred 
    // on the server that would cause the rollback to fail, such as 
    // a closed connection.
    Console.WriteLine("Rollback Exception Type: {0}", ex2.GetType());
    Console.WriteLine("  Message: {0}", ex2.Message);
}

This is copied exactly from MSDN documentation page for Rollback method.

这是从回滚方法的 MSDN 文档页面完全复制的。

I see that you're worried that you have a zombie transaction. In case you pasted, it doesn't sound like you have a problem. You're transaction has been completed, and you should no longer have anything to do with it. Remove references to it if you hold them, and forget about it.

我看到你担心你有一个僵尸交易。如果你粘贴了，听起来你没有问题。您的交易已完成，您不应再与它有任何关系。如果您持有它们，请删除对它的引用，然后忘记它。

---

---

From MSDN - SqlTransaction.Rollback Method

来自MSDN - SqlTransaction.Rollback 方法

A Rollback generates an InvalidOperationException if the connection is terminated or if the transaction has already been rolled back on the server.

如果连接终止或事务已在服务器上回滚，则回滚会生成 InvalidOperationException。

Rethrow a new exception to tell user that data may not have been saved, and ask her to refresh and review

重新抛出一个新的异常告诉用户数据可能没有保存，并要求她刷新和查看

回答by Ahmed Elbatt

This message is simply because that you wrote code that throws an exception after the transaction has been already committed successfully.Please try to check the code you wrote after the Commit method or you can handle it by using Try..Catch and finally Blocks:) .

这个消息只是因为你写的代码在事务已经成功提交后抛出异常。请尝试检查你在 Commit 方法之后编写的代码，或者你可以使用 Try..Catch 和 finally Blocks 来处理它:) .

回答by ARC

I have experienced this error once and i was stuck and unable to know what is going wrong. Actually i was deleting a record and in the Stored procedure i was not deleting its child and specially the delete statement in Stored Procedurewas inside the Transactionboundary. I removed that transaction code from stored procedure and got rid of getting this Error of “This SqlTransaction has completed; it is no longer usable.”

我曾经遇到过这个错误，我被卡住了，无法知道出了什么问题。其实我删除记录，并在存储过程中我并没有删除其子和专门的delete语句Stored Procedure是内部Transaction边界。我从存储过程中删除了那个事务代码并摆脱了这个错误“This SqlTransaction has completed; it is no longer usable.”

回答by yu yang Jian

I use code below can reproduce this error, I use 1000 tasks to execute Sql, after about 300 tasks Successfully Completed, lots of exception about timeout errorstart to occur on ExecuteNonQuery(),

我使用下面的代码可以重现这个错误，我使用 1000 个任务来执行 Sql，在大约 300 个任务成功完成后，很多关于timeout error开始发生的异常ExecuteNonQuery()，

then next error This SqlTransaction has completedwill occur on transaction.RollBack();and its call stack also contains ZombieCheck().

那么下一个错误This SqlTransaction has completed将发生在transaction.RollBack();它的调用堆栈中也包含ZombieCheck().

(If single program with 1000 tasks pressure not enough, you can execute multiple compiled exe file at the same time, or even use multi computers execute to one DataBase.)

（如果单个程序1000个任务压力不够，可以同时执行多个编译好的exe文件，甚至可以用多台电脑执行到一个DataBase。）

So I guess one of the reason cause this error can be something wrong in Connection, then cause the transactionerror happens as well.

所以我想导致这个错误的原因之一可能是 Connection 中的错误，然后导致事务错误也发生了。

Task[] tasks = new Task[1000];
for (int i = 0; i < 1000; i++)
{
    int j = i;
    tasks[i] = new Task(() =>
         ExecuteSqlTransaction("YourConnectionString", j)
        );
}

foreach (Task task in tasks)
{
    task.Start();
}       

/////////////    

public void ExecuteSqlTransaction(string connectionString, int exeSqlCou)
{

    using (SqlConnection connection = new SqlConnection(connectionString))
    {
        connection.Open();

        SqlCommand command = connection.CreateCommand();
        SqlTransaction transaction;

        // Start a local transaction.
        transaction = connection.BeginTransaction();

        // Must assign both transaction object and connection
        // to Command object for a pending local transaction
        command.Connection = connection;
        command.Transaction = transaction;

        try
        {
            command.CommandText =
                "select * from Employee";
            command.ExecuteNonQuery();

            // Attempt to commit the transaction.
            transaction.Commit();

            Console.WriteLine("Execute Sql to database."
                + exeSqlCou);
        }
        catch (Exception ex)
        {
            Console.WriteLine("Commit Exception Type: {0}", ex.GetType());
            Console.WriteLine("  Message: {0}", ex.Message);


            // Attempt to roll back the transaction.
            try
            {
                transaction.Rollback();
            }
            catch (Exception ex2)
            {
                // This catch block will handle any errors that may have occurred
                // on the server that would cause the rollback to fail, such as
                // a closed connection.
                Console.WriteLine("Rollback Exception Type: {0}", ex2.GetType());
                Console.WriteLine("  Message: {0}", ex2.Message);

            }
        }
    }
}

Besides I find if I commit twice sequentailly will invoke this exception as well.

此外我发现如果我连续提交两次也会调用这个异常。

       transaction.Commit();
       transaction.Commit();

Or if Connection Closed before commit also invoke this error.

或者，如果在提交之前关闭连接也会调用此错误。

       connection.Close();
       transaction.Commit();

Update:

更新：

I find it strange that I create another new table and insert 500 thousand data to it,

我觉得奇怪的是我创建了另一个新表并向其中插入了 50 万条数据，

then use 100000 tasks with select * from newtablesql, running 5 programs at the same time, this time the Timeout Error occur, but when transaction.Rollback()it didn't invoke the SQLTransaction has completed error.

然后用select * from newtablesql使用100000个任务，同时运行5个程序，这次出现超时错误，但是当transaction.Rollback()它没有调用SQLTransaction has completed error.

but if the Timeout Error occur, jump into the catch block, and in the catch block do transaction.Commit()again, the SQLTransaction has completed errorwill happen.

但是如果发生超时错误，则跳转到catch块，在catch块中再做transaction.Commit()一次，SQLTransaction has completed error就会发生。