none
What is a pending transaction?

    Question

  • I have read the content of  "SqlConnection.Close() method rolls back any pending transactions." in msdn library, and msdn library also said "SqlConnection.Close() method releases the connection to the connection pool, or closes the connection if connection pooling is disabled."


    I created a stored procedure in Northwind db like this:

    create proc dbo.TestProc
    as
    begin transaction
     delete dbo.[Order Details];
    go  

    I created some code to execute the stored procedure like this:
            
            static public void Test()
            {
                string connectionString = "Integrated Security=true;Initial Catalog=Northwind;server=(local);Connection Lifetime = 60";
               
                SqlConnection sqlconnection = new SqlConnection();
                sqlconnection.ConnectionString = connectionString;
                SqlCommand sqlCommand = new SqlCommand("dbo.TestProc");
                sqlCommand.CommandType = CommandType.StoredProcedure;
                sqlCommand.Connection = sqlconnection;
                try
                {
                    sqlconnection.Open();
                    sqlCommand.ExecuteNonQuery();
                }
                catch
                {               
                }
                finally
                {
                    sqlconnection.Close();
                }
            }

    Clearly, the database connection is put into the pool after the Sqlconnection.Close() is invoked. But after the Sqlconnection.Close() is invoked, the transaction is still on the database connection, and at that time the connection is in the pool.


    I have create a query like this:

    select open_tran
    from dbo.sysprocesses as p
     inner join dbo.sysdatabases as d
      on p.dbid = d.dbid
    where d.[name] = N'Northwind';


    and the result has only one row:

    open_tran
    ---------
    1

    (1 row(s) affected)



    I think the not completed(committed or rollbacked) transaction in the example procedure is not a pending transaction, because the Sqlconnection.Close() method is not rollback the transaction.
    Thus, my question are:

    a)  What is a pending transaction?
    b)  How to check transaction count in c# code, besides execute T-SQL batch include "@@trancount".
    c)  Is the not completed transaction in T-SQL only manual committed? Are there better methods to solve the not completed transaction problem?

    • Edited by zcg Tuesday, August 25, 2009 1:14 PM @@trancount is ok
    Monday, August 24, 2009 3:21 PM

Answers

  • The statement about the transaction rollback refers to pending transactions started in client code (e.g. SqlConnection.BeginTran).  That does not apply to the BEGIN TRAN in your proc code, where the pending transaction is left open and the connection is retuned to the pool.  The rollback will later occur when the connection is either reused or removed from the pool.
     
    Consider specifying SET XACT_ABORT ON when you use explicit transactions is procs.  See http://weblogs.sqltea m.com/dang/archive/2007/10/20/Use-Caution-with-Explicit-Transactions-in-S tored-Procedures.aspx.  If you don't do that, execute something like "WHILE @@TRANCOUNT > 0 ROLLBACK" afterward.
     
    --
    Hope this helps.
     
    Dan Guzman
    SQL Server MVP
    http://weblogs.sqlteam.com/dang /

    I have read the content of  "SqlConnection.Close() method rolls back any pending transactions." in msdn library, and msdn library also said "SqlConnection.Close() method releases the connection to the connection pool, or closes the connection if connection pooling is disabled."


    I created a stored procedure in Northwind db like this:

    create proc dbo.TestProc
    as
    begin transaction
     delete dbo.[Order Details];
    go  

    I created some code to execute the stored procedure like this:
            
            static public void Test()
            {
                string connectionString = "Integrated Security=true;Initial Catalog=Northwind;server=(local);Connection Lifetime = 60";
                ;
                SqlConnection sqlconnection = new SqlConnection();
              ;   sqlconnection.ConnectionString = connectionString;
            &nbs p;   SqlCommand sqlCommand = new SqlCommand("dbo.TestProc");
                 sqlCommand.CommandType = CommandType.StoredProcedure;
            ;     sqlCommand.Connection = sqlconnection;
             & nbsp;  try
                {
               &n bsp;    sqlconnection.Open();
                     sqlCommand.ExecuteNonQuery();
          &nbs p;     }
                catch
              &nbs p; {                
                }
                finally
              &n bsp; {
               &n bsp;    sqlconnection.Close();
             ;    }
            }

    Clearly, the database connection is put into the pool after the Sqlconnection.Close() is invoked. But after the Sqlconnection.Close() is invoked, the transaction is still on the database connection, and at that time the connection is in the pool.


    I have create a query like this:

    select open_tran
    from dbo.sysprocesses as p
     inner join dbo.sysdatabases as d
      on p.dbid = d.dbid
    where d.[name] = N'Northwind';


    and the result has only one row:

    open_tran
    ---------
    1

    (1 row(s) affected)



    I think the not completed(committed or rollbacked) transaction in the example procedure is not a pending transaction, because the Sqlconnection.Close() method is not rollback the transaction.
    Thus, my question are:

    a)  What is a pending transaction?
    b)  How to check transaction count in c# code, besides execute T-SQL batch include "@@error".
    c)  Is the not completed transaction in T-SQL only manual committed? Are there better methods to solve the not completed transaction problem?

    Tuesday, August 25, 2009 1:57 AM
  • The documentation is misleading regarding the Transact-SQL transaction.  Your observation (and my remarks) about the actual behavior of a local SqlClient transaction is consistent and inline with the other APIs as well.  It looks to me like the documentation reflects planned changes that were not implemented in the final product.  I'll see if I can find out more information.
     
    --
    Hope this helps.
     
    Dan Guzman
    SQL Server MVP
    http://weblogs.sqlteam.com/dang /
    Wednesday, August 26, 2009 12:20 PM

All replies

  • The statement about the transaction rollback refers to pending transactions started in client code (e.g. SqlConnection.BeginTran).  That does not apply to the BEGIN TRAN in your proc code, where the pending transaction is left open and the connection is retuned to the pool.  The rollback will later occur when the connection is either reused or removed from the pool.
     
    Consider specifying SET XACT_ABORT ON when you use explicit transactions is procs.  See http://weblogs.sqltea m.com/dang/archive/2007/10/20/Use-Caution-with-Explicit-Transactions-in-S tored-Procedures.aspx.  If you don't do that, execute something like "WHILE @@TRANCOUNT > 0 ROLLBACK" afterward.
     
    --
    Hope this helps.
     
    Dan Guzman
    SQL Server MVP
    http://weblogs.sqlteam.com/dang /

    I have read the content of  "SqlConnection.Close() method rolls back any pending transactions." in msdn library, and msdn library also said "SqlConnection.Close() method releases the connection to the connection pool, or closes the connection if connection pooling is disabled."


    I created a stored procedure in Northwind db like this:

    create proc dbo.TestProc
    as
    begin transaction
     delete dbo.[Order Details];
    go  

    I created some code to execute the stored procedure like this:
            
            static public void Test()
            {
                string connectionString = "Integrated Security=true;Initial Catalog=Northwind;server=(local);Connection Lifetime = 60";
                ;
                SqlConnection sqlconnection = new SqlConnection();
              ;   sqlconnection.ConnectionString = connectionString;
            &nbs p;   SqlCommand sqlCommand = new SqlCommand("dbo.TestProc");
                 sqlCommand.CommandType = CommandType.StoredProcedure;
            ;     sqlCommand.Connection = sqlconnection;
             & nbsp;  try
                {
               &n bsp;    sqlconnection.Open();
                     sqlCommand.ExecuteNonQuery();
          &nbs p;     }
                catch
              &nbs p; {                
                }
                finally
              &n bsp; {
               &n bsp;    sqlconnection.Close();
             ;    }
            }

    Clearly, the database connection is put into the pool after the Sqlconnection.Close() is invoked. But after the Sqlconnection.Close() is invoked, the transaction is still on the database connection, and at that time the connection is in the pool.


    I have create a query like this:

    select open_tran
    from dbo.sysprocesses as p
     inner join dbo.sysdatabases as d
      on p.dbid = d.dbid
    where d.[name] = N'Northwind';


    and the result has only one row:

    open_tran
    ---------
    1

    (1 row(s) affected)



    I think the not completed(committed or rollbacked) transaction in the example procedure is not a pending transaction, because the Sqlconnection.Close() method is not rollback the transaction.
    Thus, my question are:

    a)  What is a pending transaction?
    b)  How to check transaction count in c# code, besides execute T-SQL batch include "@@error".
    c)  Is the not completed transaction in T-SQL only manual committed? Are there better methods to solve the not completed transaction problem?

    Tuesday, August 25, 2009 1:57 AM
  • Oh, thanks!
    But, msdn library also said: "Pending transactions started using Transact-SQL or BeginTransaction are automatically rolled back. ". msdn explained the SqlConnection.Close() method including this clause. What does it mean?

     
    I also have known the fact that many kind of api, such as ado, odbc, etc. cannot detect the BEGIN TRAN in T-SQL batch. Is ado.net(SqlClient) same to ado?  If this is true, I am really confused by the words in msdn library. Who can explain this question.

    Tuesday, August 25, 2009 1:11 PM
  • The documentation is misleading regarding the Transact-SQL transaction.  Your observation (and my remarks) about the actual behavior of a local SqlClient transaction is consistent and inline with the other APIs as well.  It looks to me like the documentation reflects planned changes that were not implemented in the final product.  I'll see if I can find out more information.
     
    --
    Hope this helps.
     
    Dan Guzman
    SQL Server MVP
    http://weblogs.sqlteam.com/dang /
    Wednesday, August 26, 2009 12:20 PM
  • As a followup, SQL MVP Erland Sommarskog and I added community content to the 2.0 and 3.5 framework topics to note that transactions strarted using a Transact-SQL BEGIN TRANSACTION statement are not affected by Close.

    By the way, a "pending transaction" is an outstanding transaction that has not yet been committed or rolled back.  Such a transaction can be started using client APIs (SqlConnection.BeginTransaction or System.Transactions) or with a BEGIN TRANSACTION T-SQL statement.  The SqlClient API currently has no awareness of transactions started using T-SQL so no interaction with the SQL Server occurs when a pooled connection is closed.  The transaction is rolled back later when the connection is reused or physically closed.  The physical connection is closed immediately when no pooling is used.
    Dan Guzman, SQL Server MVP, http://weblogs.sqlteam.com/dang/
    Saturday, September 05, 2009 12:49 PM