ADO.NET 2.0 The connection pool and distributed transactions

  • Article

I am a big fan of pop quiz style blogs like the ones you can find here: https://blogs.msdn.com/brada/ .

I am not really sure that there are enough people looking at this blog to make this fun but I am going to give it a try. I will incorporate the answer in the blog at a latter time for reference.

Pop quiz,

Connection pooling can get pretty confusing when distributed transactions are in use, in the following code I am setting the following connection string keywords:

Max Pool Size=1

Enlist =false

using System.Transactions.Ltm;

using System.Transactions;

using System;

using System.Data.SqlClient;

namespace DataViewer.Repro {

    public class Repro {

        public static int Main(string[] args) {

           try{

            TransactionScope transactionscope1 = new TransactionScope();

            ITransaction itransaction1 = Transaction.Current; // LightweightTransaction

            SqlConnection sqlconnection1 = new SqlConnection(ConnectionString+";Max Pool Size=1;Enlist=false");

            sqlconnection1.Open();

            sqlconnection1.EnlistTransaction((ITransaction)itransaction1);

            sqlconnection1.Close();

//System.Threading.Thread.Sleep(61*1000); //ß Line 1

            sqlconnection1.Open(); //ß What is the behavior here?

           }catch(Exception ex){Console.WriteLine(ex.Message.ToString());}

           return 1;

        }

    }

}

 

Well, time to aknowledge that this is not going to work, let me just explain how this works:

“What is the behavior here?” sqlconnection1.Open will work or throw an exception given the information specified below:

1) With the code as shown. (no waiting 61 seconds)

      The connection will fail to open. On connection close we will realize that the connection is currently participating in a distributed transaction and we will not place it back into the regular pool. This transacted connection will be placed in a sub-pool and will only be taken out when the transaction completes (we get a notification) or if you open another connection with the same connection string and enlisted to the same distributed transaction (impossible in this case due to Enlist=false). When you call Open again we compare the size of the pool (including the subpool) with the Max Pool Size of 1 and fail to Open with a "timeout exception" The timeout period elapsed prior to obtaining a connection from the pool.

 

 2)  If you uncomment “Line 1” so that you wait 61 seconds before calling Open.

      The connection will open. As above we will place the connection on the transacted subpool, the difference here is that the default timeout of

      a distributed transaction is 1 minute. When the minute is up the transaction will roll back and we will get a notification saying that it is completed.

      We will then take the connection out of the subpool and place it back in the regular pool. When we call Open a second time there will be a free

      connection waiting in the pool.