Transactions in .Net 2.0 application-- what to use?

Question

Hi everyone,

I'm working on a .Net 2.0 application and need to wrap some database transactions in my code. The backend is SQL Server 2008.

I've been away from .net for a few years, the last time I did any transaction processing was in .Net 1.1 with serviced components. I know about TransactionScope, and was wondering if that's a good way to go about things now.

Also, in .Net 3.5 I assume there are other ways to do this, say with WCF? Just wondering if someone could point me toward an article or two. Thanks very much.

Answer 1

TransactionScope is a good way, provided you keep it in check. All ADO.Net providers are aware of the System.Transactions objects so they will enlist properly into a back end transaction when operating under a TransactionScope.

There are two gotchas:

• Isolation level. .Net framework loves escalating every measly read to a full isolation level transaciton and 99.99% of the times this is overkill. "By default, the System.Transactions infrastructure creates Serializable transactions". Make sure you set it to something more reasonable (Snapshot and Read Commited are good choices).

. You can determine the isolation level of an existing transaction using the IsolationLevel property of a transaction.

• Distributed transactions. Whithin a transaction scope the moment you use a second ADO.Net connection the TransactionScope will enlist it, together with the first one, into a distributed transaciton, even when both transacitons point to the same database. Make sure you use one and only one connection within a transaciton scope.

Technically the last point applies to using any resource manager, but is unlikely you'll use any other RM than your db connections. IF you do, then enlisting into a distributed transaction is the proper action anyway.