Monday, January 30, 2006

System.Transactions LTM - promoting to DTC

In an earlier post I wrote about how the ADO.NET 2.0 System.Transactions LTM mechanism for SQL Server 2005 gets promoted from LTM into a full DTC transaction when more than one connections are opened against the same SS2K5 database (the resource). This is bound to happen when using TableAdapaters, as they open and close their own SQL connections using the configured common connection string.

One possible solution is to open a single, common SQL connection and pass it around to all methods in the biz-logic and data access logic (a data session object) to ensure that all SQL and TableAdapters use a single connection to stay within the LTM limitation (i.e. avoid getting DTC). The drawback of this is that it is contrary to "aquire late, release early" use of SQL connections, and also introduces explicit code to open/close a connection. Thus, it re-introduces the negative aspects of explicit transactions, making it hard to reuse the components e.g. in higher level processes and services.

An alternative, simpler solution is available when you use the TableAdapter only for reading data: use the TransactionScopeOption.Suppress option when setting up the ambient transaction context of your method. This ensures that the current LTM transaction will not be affected by the TableAdapter read/fill/get operation. It does not matter that the TableAdapater opens a second SQL connection automatically, promoting to DTC will not happen as the code is using two different transaction contexts:

Using transxScope As TransactionScope = New TransactionScope(TransactionScopeOption.Suppress)

'code that utilize TableAdapter for reading data here

transxScope.Complete()
End Using


You must review your usage of TableAdapaters and ensure that they can be used outside transactions. If you use them for updating the database, or if you depend on reads to be inside a transaction (e.g. for row/table locking purposes, transaction isolation level), then you are out of luck with LTM. These scenarios requires the use of DTC or, when you require LTM, the passing-an-open-connection-around solution. The latter can be caused by DTC not being allowed by the customer's operations department.

3 comments:

Anonymous said...

I am in the same boat. Did you come up with any alternative solution for using table adapters and transaction without escalating to MSDTC? It's hard to believe tha MS ignored this issue and it's killing use of typed datasets.

Kjell-Sverre Jerijærvi said...

I have to admit that we use a DataSession object with a SqlConnection property that we assign an open connection. This session object is passed around, and then assigned to the Connection property as applicable.

This is a temporary solution while we are crossing our fingers and hoping/waiting for Microsoft to allow LTM for multiple, identical connections to a single SS2K5 database.

Kjell-Sverre Jerijærvi said...

The ADO.NET team has provided an unsupported workaround for the LTM limitation. See more info and link at http://kjellsj.blogspot.com/2006/04/systemtransactions-nesting-scopes.html