A connection using invist = false does not close after being manually entered into a distributed transaction

Question

I have a distributed transaction context using ServiceDomain . Inside, I open an SQL connection with a connection string with Enlist=false so that it will not be automatically credited to the transaction. Then, if I manually connect the connection in a distributed transaction using EnlistDistributedTransaction , the connection will not be closed, which may end in an InvalidOperationException with:

Timed out. The wait period expires before a connection is received from the pool. Perhaps this was due to the fact that all joined connections were used and the maximum pool size was reached.

Try the following:

 try { var configuration = new ServiceConfig { Transaction = TransactionOption.Required, TransactionTimeout = 1000 }; ServiceDomain.Enter(configuration); for (var i = 0; i < 500; ++i) { Console.WriteLine(i); using (var conn = new SqlConnection("Data Source=localhost;Initial Catalog=dotest;Integrated Security=SSPI;Enlist=False;")) { conn.Open(); if (i % 2 == 0) conn.EnlistDistributedTransaction((ITransaction) ContextUtil.Transaction); using (var cmd = conn.CreateCommand()) { cmd.CommandText = "INSERT INTO [Test]([ID]) VALUES(@num)"; cmd.Parameters.AddWithValue("@num", i); cmd.ExecuteNonQuery(); } } } ContextUtil.SetAbort(); } finally { ServiceDomain.Leave(); } 

This gets stuck (and dies after a timeout) in 200 connections, since all 100 recruited connections are not explicitly closed (and the default pool size is 100). (Note that you can completely remove the command if you want to test it without creating a table.)

What am I missing or is something wrong?

Answer 1

Try setting conn.EnlistDistributedTransaction(null); at the end of the query.

 using (var conn = new SqlConnection("Data Source=localhost;Initial Catalog=dotest;Integrated Security=SSPI;Enlist=False;")) { conn.Open(); if (i % 2 == 0) conn.EnlistDistributedTransaction((ITransaction) ContextUtil.Transaction); using (var cmd = conn.CreateCommand()) { cmd.CommandText = "INSERT INTO [Test]([ID]) VALUES(@num)"; cmd.Parameters.AddWithValue("@num", i); cmd.ExecuteNonQuery(); } conn.EnlistDistributedTransaction(null); } 

also:

Where is your SQL Server? Is there a local machine? Do you have security settings? This article may not be entirely suitable for you, but it should give you guidance and direction in your search for things in your environment.

Hope this helps.