SqlClient.SqlTransaction vs System.Transactions

Question

We have some constant issues with MSDTC setting up, now considering replacing SystemTransactions with SqlTransactions.

I am interested in differences of the above, and possible issues we might have doing that.

Answer 1

The article quoted and referenced by Massimiliano was 7 years old when referenced and therefore refers only to .NET 2.0 and SQL Server 2000 and 2005. However, I believe the salient points are still valid for later versions. I'm having trouble finding directly applicable commentary on any changes in later versions.

It does seem that in SQL Server 2008 and later System.TransactionScope transactions against a single database are less likely to be promoted to DTC than under older versions.

related: SqlClient, System.Transactions, SQL Server 2008, and MARS

So depending on you SQL Server version you will have more or less trouble with DTC using System.Transactions against a single database.

The code for System.Transactions is lighter than for SqlTransactions, but the processing overhead is higher and you have less explicit control.

Answer 2

System.Transactions is NOT a replacement for SqlTransaction.

But as far as a single database is considered, the biggest difference between a ADO.NET 2.0 System.Transactions based transaction and a regular SqlTransaction is "Isolation level". It is a big difference however. Let me come to Isolation level in a moment, but before that you've gotta realize that, in Sql 2k, the transaction will be managed by MSDTC even if you have ONE single database involved. For Sql2k5, the transaction is managed by LTM UNTIL the second SqlConnection connected to a different database comes into picture. When that DOES happen, the transaction is bumped up from LTM to MSDTC.

http://dotnetslackers.com/SQL/re-3463_SqlTransaction_vs_System_Transactions.aspx