VB.NET - Implicit and Explicit Transactions

Question

What are the benefits of using implicit transactions (TransactionScope) over explicit transactions (SQLTransaction)?

I have spent some time investigating this and there are lots of websites that explain the differences between the two, which I do understand e.g. this one: http://sqlserverpedia.com/wiki/Transaction_Overview, and this one: named explicit & implicit transactions. I understand the properties of a Transaction i.e. ACID. I have also looked at the documentation on MSDN.

I believe that implicit transactions are more flexible and easier to use (because rollback is done for you and the transaction is not tied to a specific connection). I believe that explicit transactions give you more control e.g. when to rollback. I wanted to confirm: a) whether or not what I have said is true, b) Is there any criteria used to decide whether to use an implicit or explicit transaction.

On MSDN it says: "It is highly recommended that you use the easier implicit model for development"

I've used SqlTransaction the most it's been there since net 1.0. TransactionScope was introduced in 2.0. TransactionScope is kind of "automagic" using COM transactions and also support other TransactionTypes in MSDTC. TransactionScope is easy, you may have written code quickly, without transactions. Then when you decide to have transactions and just put using TransactionScope ... around you code and a call to TransactionScope.Complete(), that's all. If you want to use SqlTransaction you must pass the transaction object around in nested methods etc. — mortb
Problem is though that you introduce a dependency on MSDTC with TransactionScope which you'll not have if you use SqlTransaction and I have not used TransactionScope for the most crititcal code in my applications... — mortb

zmbq zmbq · Accepted Answer · 2013-01-25T23:15:14

Explicit transactions are a thing of the past. Don't use them unless you have a really good reason, and I can't think of any. TransactionScope is the way to go, practically always.

Don't worry about DTC, TransactionScope is smart enough to use the DTC only when two resources need to be coordinated. If you're accessing just one database, TransactionScope will be every bit as fast as explicit transactions.

VB.NET - Implicit and Explicit Transactions

2 Answers