ado.net 2.0 Transactionscope only for distributed transactions?

[Sam Jost]

Hi!

I just wanted to use some simple transactions, and remembered the new
ADO.NET 2.0 transactions using TransactionScope.

But these seem only to work for distributed transactions, or am I
mistaken? At least I found no way to use them without installing MSDTC,
but I only need simple transactions so I am a bit at a loss now.

Sam

 

[Sahil Malik [MVP]]

Sam,

As far as a single database is considered, the only difference between a
ADO.NET 2.0 System.Transactions based transaction and a regular
SqlTransaction is "Isolation level". It is a big difference however.

Sql2000 will default the isolation level to serializable (performance pig),
even if there is one single RM (One database) involved in the transaction.

Sql2k5 however will PROMOTE the transaction from LTM (Lightweight
transaction manager) to MSDTC --- AUTOMATICALLY for you .. thus changing
the isolation level ONLY IF it needs to .

Now, to answer your specific question. System.Transactions is NOT a
replacement for SqlTransaction. For single database transactions in MOST
cases you still want to use SqlTransaction. But can System.Transactions BE
used in a single database scenario??? YES SURE they can be. The differences
are -

a) You donot explicitly enroll in the transaction - so you don;'t know you
are in a transaction or not - atleast you don't control it explicitly.
b) You don't control when your isolation level got escalated - bad bad.
c) It's an overkill

To make the long story short -
Yes you can use System.Transactions for single db transactions.
And NO you shouldn't (in most cases) use System.Transactions for single
database transactions.

Hope this helps?

BTW here is a relevant blog post I wrote long back that you might wanna
read -
http://www.codebetter.com/blogs/sahil.malik/archive/2004/12/09/35816.aspx

- Sahil Malik [MVP]
http://codebetter.com/blogs/sahil.malik/

 

[Sam Jost]

Hi Sahil,


Jay, this did clarify things a bit for me, corrected my wrongly
remembered information about System.Transactions replacing
SqlTransaction.

Now I just gotta check if there is some provider-independent way for
transactions.

thanks a lot!
Sam

 

[Sahil Malik [MVP]]

You are welcome

- Sahil Malik [MVP]
http://codebetter.com/blogs/sahil.malik/

 

[Pablo Castro [MS]]

If you are using SQL Server 2005 and only have one connection
per-transaction scope, then their are almost equivalent (only differ in
configuration details such as the isolation level, which can be set in both
cases explicitly anyway).

If you're using other resource-managers such as SQL 2000 or other databases,
then System.Transactions will always involve MSDTC, so you'll be using more
resources than you need. In these cases classic transactions are better
(faster). Also, when running inside SQL Server 2005 CLR integration,
TransactionScope may cause the transaction to promote (become a DTC
transaction) prematurely, causing a performance degradation as well.

--
Pablo Castro
Program Manager - ADO.NET Team
Microsoft Corp.

This posting is provided "AS IS" with no warranties, and confers no rights.