System.Transactions and connection pooling

Update: I had put this post up yesterday and then accidentally deleted it while trying to do an update due to a technical inaccuracy. Sorry if you get a duplicate post, if so use this one. The technical inaccuracy had to do with the section describing when the transaction is promoted to the DTC.

—————

One of the reviewers of my Data Binding in Windows Forms 2.0 book raised a question regarding System.Transactions and connection pooling that is important to understand.I didn’t go into detail on this issue in the book (because it is not a data access book, it is a data binding book… you know completely different layers, hopefully separated by a business layer…).



The discussion centered around the following code snippet:

public void ExecuteTransaction()

{

CustomersTableAdapter adapter = new CustomersTableAdapter();

// Start the transaction

using (TransactionScope scope = new TransactionScope())

{

CustomersDataSet.CustomersDataTable customers;

customers = adapter.GetData();// First query

customers[0].Phone = “030-0074321”;

adapter.Update(customers);// Second query

scope.Complete();// Commits the transaction

}

}



The code above opens a transaction scope and performs two queries within that scope. The call to Complete on the scope votes to commit the transaction. If there is no ambient transaction when this call is executed, a new transaction will be created when the scope is constructed. If there is an ambient transaction, this scope will be enlisted in the ambient transaction, and will not be committed until its enclosing transaction is committed.



The misunderstanding was that the reviewer thought that in order to scope the transaction across two queries (GetData and Update on the table adapter), Microsoft had implemented it by keeping the connection open, which would be a bad thing from a connection pooling perspective.





The truth is that transactions in System.Transactions have scope greater than a single connection. If the queries run against a single connection to a SQL Server 2005 database, then a lightweight transaction will scope the queries through that connection. If the queries run against more than one connection instance (as is likely with the code above or most multi-query scenarios) or any other resource manager other than 2005 at this point (i.e. SQL 2000, Oracle, MSMQ, etc.), then the transaction will auto-promote to a DTC transaction, which can obviously scope multiple connections.





In the code above, the connection is opened and closed automatically by the data adapter that is encapsulated in the table adapter for each of the calls (GetData and Update) in the same way as in .NET 1.1, regardless of the target database. The close doesn’t really close the connection, it puts it back on the connection pool. The transaction scope is greater than each connection and can span both connections, after promotion to the DTC.



In case you want to see it in action, you can download a simple sample here that demonstrates this fact. This sample makes similar calls to the above, but adds a partial class extenstion and method to the table adapter allowing you to check the connection state in between calls:

namespace SystemTransactionsSample.CustomersDataSetTableAdapters

{

publicpartialclass CustomersTableAdapter

{

publicbool IsConnectionOpen()

{

return Connection.State == System.Data.ConnectionState.Open;

}

}

}



If you insert a call to IsConnectionOpen on the adapter between GetData and Update, you will see that the connection is in fact in a closed state between calls, even though the transaction is alive and uncommitted.