none
Connection Pooling reuses transaction settings RRS feed

  • Question

  • Hi All,

    I encountered the following problem and would like to have your opinion.

    1. I create a connection to read a record using ADO.Net, i use a transaction with isolation level read uncommited. Lets call this connection Pool A. After reading the record the connection is closed; (freed in the connection pool)
    2. I update the same record i read in step 1 using a different connection string (for example change the Application name). This update also uses a transaction. Lets call this connection Pool B
    3. Before commiting the update in step 2, I read the same record again reusing the connection from pool A, only this time without setting the transaction.

    I would expect I cannot read the record in step 3 before the update is comitted. At least that is what happens when I skip step 1.

    Actualy it seems that the connection that is retrieved in step 3 from connection pool A, reuses the transaction isolation level that is set in step 1.

    This behavior seems like a bug to me, what do you think?

    Below is the code I used:

    using System;
    using System.Data.SqlClient;
    using System.Diagnostics;
    using Framework.Database.Sql;
    
    namespace test
    {
        static class Program
        {
            /// <summary>
    
            /// The main entry point for the application.
    
            /// </summary>
    
            [STAThread]
            static void Main()
            {
    
                //connection pool A
                using (SqlConnection connection = new SqlConnection("Server=(local);Database=testdb;User Id=testuser;Password=testpassword;Application Name=connectionA;"))
                {
                    connection.Open();
                    using (SqlTransaction transaction = connection.BeginTransaction(System.Data.IsolationLevel.ReadUncommitted))
                    using (SqlCommand command = new SqlCommand("GetProjectById", connection, transaction))
                    {
                        command.CommandType = System.Data.CommandType.StoredProcedure;
                        command.Parameters.AddWithValue("@id", 1);
    
                        using (SqlDataReader reader = command.ExecuteReader())
                        {
                            if (reader.Read())
                            {
                                Debug.WriteLine(reader.GetString(1));
                            }
                        }
                    }
                }
    
                //connection pool B
                using (SqlConnection connection = new SqlConnection("Server=(local);Database=testdb;User Id=testuser;Password=testpassword;Application Name=connectionB;"))
                {
                    connection.Open();
                    using (SqlTransaction transaction = connection.BeginTransaction(System.Data.IsolationLevel.ReadCommitted))
                    using (SqlCommand command = new SqlCommand("SaveProject", connection, transaction))
                    {
                        command.CommandType = System.Data.CommandType.StoredProcedure;
    
                        SqlParameter id = SqlCommandHelper.IntParameter("@Id", 1, System.Data.ParameterDirection.InputOutput);
    
                        command.Parameters.Add(id);
                        command.Parameters.AddWithValue("@Name", "testname");
                        command.ExecuteNonQuery();
    
                        int idint = (int)id.Value;
    
                        //reuse connection pool A without a transaction
                        using (SqlConnection reuseA = new SqlConnection("Server=(local);Database=testdb;User Id=testuser;Password=testpassword;Application Name=connectionA;"))
                        {
                            reuseA.Open();
                            using (SqlCommand reuseCommand = new SqlCommand("GetProjectById", reuseA))
                            {
                                reuseCommand.CommandType = System.Data.CommandType.StoredProcedure;
                                reuseCommand.Parameters.AddWithValue("@id", 1);
    
                                using (SqlDataReader reader = reuseCommand.ExecuteReader())
                                {
                                    if (reader.Read())
                                    {
                                        Debug.WriteLine(reader.GetString(1));
                                    }
                                }
                            }
                        }
                    }
                }
            }
        }
    }
    


    Thursday, July 30, 2009 7:55 AM

Answers

All replies

  • What makes you believe that at step 3 your application reuses pooled connection from Step 1? I believe when you create connection B, pool for the connection A is destroyed, since you have completely different connection string for the connection B. ADO.NET does not maintain multiple connection pools (one per each connection string), but single pool in this case and the connection pool in your example will be recreated from scratch each time you create connection.
    Val Mazur (MVP) http://www.xporttools.net
    Friday, July 31, 2009 9:58 AM
    Moderator
  • What makes you believe that at step 3 your application reuses pooled connection from Step 1? I believe when you create connection B, pool for the connection A is destroyed, since you have completely different connection string for the connection B. ADO.NET does not maintain multiple connection pools (one per each connection string), but single pool in this case and the connection pool in your example will be recreated from scratch each time you create connection.
    Val Mazur (MVP) http://www.xporttools.net

    As you can see, in step 1 and step 3 exactly the same connection strings are used. I think that in step 1 the connection pool is created and used. After step 1 the connection pool stays alive and the used connection is available back in the pool. Step 2 uses another connection string and therefore another pool. This one is not part of the problem, only used to create the lock on the record. Then in step 3 the same connection from the connection pool created in step 1 is used. As I see it with the same transaction parameters as set in step 1. Without setting them!

    Have you tried running my sample? step 3 acts differently when step 1 is skipped, that can't be right.


    Monday, August 3, 2009 12:04 PM
  • What happens if you explicitly call CommitTransaction for the transaction and Close method for the connection instead of relying on "using" statement.
    Val Mazur (MVP) http://www.xporttools.net
    Tuesday, August 4, 2009 10:35 AM
    Moderator
  • What happens if you explicitly call CommitTransaction for the transaction and Close method for the connection instead of relying on "using" statement.
    Val Mazur (MVP) http://www.xporttools.net

    no difference.

    I disable connection pooling for step A now, that solves the my problem.

    It must have something to do with the note that can be found on the Connection.BeginTransaction page (http://msdn.microsoft.com/en-us/library/5ha4240h(VS.80).aspx)

    Note

    After a transaction is committed or rolled back, the isolation level of the transaction persists for all subsequent commands that are in autocommit mode (the SQL Server default). This can produce unexpected results, such as an isolation level of REPEATABLE READ persisting and locking other users out of a row. To reset the isolation level to the default (READ COMMITTED), execute the Transact-SQL SET TRANSACTION ISOLATION LEVEL READ COMMITTED statement, or call SqlConnection.BeginTransaction followed immediately by SqlTransaction.Commit. For more information on SQL Server isolation levels, see "Isolation Levels in the Database Engine" in SQL Server 2005 Books Online.



    But i still think this is a bug. Transaction isolation level should not be persisted for the connections that are returned to the pool.
    Tuesday, August 4, 2009 3:55 PM
  • You may want to explicitly use a transaction with "reuseA", to avoid having your commands execute in auto-commit mode.  This way you have better control over the isolation level used by the transaction associated with the connection.  This would also allow to use connection pooling with the first connection, which would most likely improve the performance of this code.


    This posting is provided "AS IS" with no warranties, and confers no rights.
    • Edited by Jimmy HutsonModerator Wednesday, August 5, 2009 4:48 AM rewording
    • Marked as answer by Yichun_Feng Thursday, August 6, 2009 1:59 AM
    • Unmarked as answer by RikMoed Thursday, August 6, 2009 10:08 AM
    Wednesday, August 5, 2009 4:46 AM
    Moderator
  • Late answer, but this seem to be by design. Do not have any further details.

    "The transaction isolation level is not reset when you reuse a connection from the connection pool"
    http://support.microsoft.com/?id=972915

    HTH
    //Michael
    This posting is provided "AS IS" with no warranties.
    Tuesday, August 11, 2009 11:49 AM
  • Late answer, but this seem to be by design. Do not have any further details.

    "The transaction isolation level is not reset when you reuse a connection from the connection pool"
    http://support.microsoft.com/?id=972915

    HTH
    //Michael
    This posting is provided "AS IS" with no warranties.

    Oke thanks for the answer(s).

    I understand this behavior is by design, however I can not think of a reason why it is designed this way. I would expect a Connection.Open() always has the same result, wether the connection is from a pool or not.
    Thursday, August 13, 2009 12:11 PM