[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.