[mbowcock]

The transaction is internal to the database. It's essentially a list of operations to perform, if something goes wrong it provides a list of things to reverse. If the db transaction fails, then there would need to be some code to handle rolling back operations outside of the database. I don't think db transactions were to fault in your situation.

[VBprogrammer]

I'm not sure I really get what you are trying to say. I know what a transaction is, and I know it wasn't the fault of transactions. It was the fault of our poor use of transactions, in the same monolithic transaction pattern that the OP appears to be advocating.

[zzzeek]

transaction-per-request is by far the best default pattern to use. If some particular methods have special needs, like needing to break up the operations into multiple transactions to deal with third-party communication, those are the exceptions.

The alternatives to transaction-per-request are autocommit, or explicit transactions required at all times. Autocommit is a terrible choice because now you've lost all atomicity and isolation for dependent operations (locking is also not much of an issue. If you're doing VB, then you're on SQL server, which has some of the worst locking behavior - turn on snapshot isolation to make it bearable). Explicit transactions required at all times is a terrible choice because now your app is littered with what is 90% of the time unnecessary boilerplate.

[jeltz]

Why not the third (and in my experience most common) alternative? Autocommit but if you explicitly start a transaction then automcommit is turned off until that transaction has been committed.

[zzzeek]

If you have more than one statement, then autocommit is usually wrong, and what you usually want is transaction-per-request. If you have exactly one statement, then autocommit is exactly identical to transaction-per-request.

Also part of the transaction-per-request pattern is that the transaction is started when the first statement is invoked. So there's no overhead for zero-statement requests either.

[mbowcock]

It seemed you were arguing against using transactions by default because they didn't roll back a call to a web service. I meant to point out that the issue in your case seems less about db transactions and more about the code and error handling.