System.Transaction performance with non SQL 2005

[Peter Meinl]

Our customer will use transactions against single SQL Server 2000, 2005 and
AS400 databases (no distributed transactions).
I am evaluating using TransactionScope vs. SqlTransaction.

Some internet posts say that with non SQL Server 2005 databases
TransactionScope will always use the slower distributed model using MSDTC.
To my understanding the online help only says non SQL Server 2005 databases
don't support Promotable Enlistments and not that they always use MSDTC.
My tests seem to show that using TransactionScope against SQL Server 2000
works without MSDTC (I stopped MSDTC and the test app still seems to work).
What is the truth?

Some say SQL Server 2000 will alway use IsolationLevel=Serializable.
I assume one can override this by exlicitely setting
TransactionOptions.Serializable=ReadCommitted.
Or am I missing something here?

 

[Shawn Wildermuth (C# MVP)]

Hello Peter,

I can't attest to the AS400, but for SQL Server 2000 you can add "enlist=false;"
to the connection string to prevent auto-enlistment to the DTS unless a distrubuted
transaction is started. Though if you're going to just use a single transaction,
SqlTransaction will be a bit more lightweight.


Thanks,
Shawn Wildermuth
Speaker, Author and C# MVP
http://adoguy.com