What happens to a closed connection in a transactionscope? be it reusable by other threads?

Question

_{Wednesday, September 30, 2009 6:14 AM}

What will happen to the inner part of a closed connection in a transaction ( suppose it is a pooled connection, and the transaction  can be either an explicit one or a context one of a transactionscope, the server, 2005 or 2008)?  will it just sleep there waiting for a final commit or rollback? or can already be used by other threads? or it depends on whether the transaction is promoted later to a dtc one? i.e.,  if it's promoted, then it is no longer needed by the transaction, can be used by other threads, if not promoted to dtc transaction, then it will stay waiting and be not usalbe for other threads/transactions.

The answer to question 2 will have an effect on coding pattern, if the inner part of a closed connection in a transaction is not needed any more, then closing it at the earliest possible time will release it for others to use, and will be a better coding pattern, if otherwise, then closing it later or after transaction commit or rollback is perhaps a better choice.

The question was redirected from transaction programming to here.

All replies (4)

_{Saturday, October 3, 2009 6:48 PM ✅Answered | 2 votes}

SqlConnection is naturally closed before TransactionScope finishes due to the ordinary way to nest the code with "using" blocks:

           using (TransactionScope scope = new TransactionScope())
            {
                using (SqlConnection conn = new SqlConnection())
                {
                }
                scope.Complete();
            }

However, the transaction can still be rolled back or committed after the SqlConnection using block finishes.  Thus, the SqlConnection must be tied up (not returned to the pool).  It would generally get returned to the pool when the TransactionScope commits during the call to Dispose that follows its Complete method.

> if the inner part of a closed connection in a transaction is not needed any more, then closing it at the earliest possible time will release it for others to use

I agree that you should close as soon as possible as this is good coding practice.  In return, the framework will return it to the pool as quickly as possible.  However, this will not be until the transaction is actually committed or rolled back as described above.

_{Wednesday, September 30, 2009 10:59 AM}

If it is explicit transaction the as soon as you close connection, the current transaction opened against that connection will be rolled back automatically and connection will be returned to the pool and will not have transaction support unless new transaction explicitly opened against that connection. I do not remember details of the promoted transaction in terms of rolling back current transaction, but I know that if you close connection inside of the context transaction, it will not carry transactional context to the next consumer of that connection.Val Mazur (MVP) http://www.xporttools.net

_{Thursday, October 1, 2009 9:33 AM}

Thanks.

For "If it is explicit transaction the as soon as you close connection, the current transaction opened against that connection will be rolled back automatically and connection will be returned to the pool and will not have transaction support unless new transaction explicitly opened against that connection",  I guess here you mean by "explicit transaction" is something like connection.opentransaction (an ado transacdtion),  but what I have in mind is a system.transactions.transcation and the connection join it by explicit enlisting in it. The two "explicit transaction" are quite differenct, the system.transaction deal with dtc, so your statement (roll back) will probably not be true for this type of explicit transaction.

"but I know that if you close connection inside of the context transaction, it will not carry transactional context to the next consumer of that connection.", does this mean, if you close a connection in a transaction context, the inner part of the connection will be released to the pool and will be immediately availible for use by the next consumer? and regardless if the context transaction is promoted to dtc or not?

I guess this is a complex question, bestly answered by someone from the system.transaction or system.data group

_{Sunday, October 4, 2009 8:42 AM}

Thank you for taking time to answer the question, you answer is helpful.

So basically, you mean the inner part is not free to use by next consumer until the transaction ends. Then, no benifit is gained by releasing SqlConnection before the transaction ends, regardless it's a ado transaction or system.transaction transaction, in the first case, closing connection will rollback the transaction, in the later case, the inner connection is tied and not free to use by others.

BTW, I just wonder why the inner connection has to be tied with the transaction if it's a dtc transaction, does Ado use it to send commit or rollback command? In my thinking, the commit or rollback command has to be sent to dtc which in turn notify the db server, so once sql command is finished using the connection, no need to keep it waiting for the transaction to end.