1

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"

Community
  • 1
  • 1
w0051977
  • 15,099
  • 32
  • 152
  • 329
  • 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 Jan 10 '13 at 09:14
  • 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 Jan 10 '13 at 09:17

2 Answers2

1

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.

zmbq
  • 38,013
  • 14
  • 101
  • 171
  • Thanks +1. When you say "explicit transactions", then are you referring to the SQLTransaction class? – w0051977 Jan 25 '13 at 23:16
  • Yes. Haven't used those since .NET 2.0 came out. – zmbq Jan 25 '13 at 23:17
  • Thanks. Are there any benefits at all of using SQLTransaction and OracleTransaction. You may be able to answer my other question here: http://stackoverflow.com/questions/14346936/vb-net-transaction-rollback. I have asked several questions about transactions recently. – w0051977 Jan 25 '13 at 23:23
  • OK, OK. I think I'll just do a search of all the questions you've asked recently. – zmbq Jan 25 '13 at 23:27
  • You should really accept answers to the questions you ask. A lot of people went out of their way to help you. – zmbq Jan 25 '13 at 23:29
0

I suggest avoiding using MSDTC if at all possible, unless you have the chops troubleshoot, write exhaustive troubleshooting guides on it, and write thorough explanation to users of your software about how to configure it. For example, users will need to know how to set up its authentication, how to ensure the DTC service is running make sure remote machines can communicate, how to configure SQL with FQDN for cross domain transactions, etc. It's been nothing but a problem for me, for years. Slowly have been winning the war to get it removed from the system. In other words, yes. Go ahead and use if it you want to guarantee issues with it for the next ten years. IMHO, it is not an enterprise class tool. There is nothing automagical about it at all.

RelativitySQL
  • 356
  • 3
  • 5