Best Practices

Regardless of how you implement transactions, you must get in and out of your transactions quickly because transactions lock valuable resources. Use the "just-in-time" approach to create a transaction just before you need it, open your connections, execute your commands, and complete the transaction. Avoid running lots of non-database code inside the transaction so that other resources aren't locked any longer than absolutely necessary.

Transactions also affect connection pooling. When a connection is enlisted in an active transaction scope, even if the connection is closed, it is not fully released to the connection pool to be reused right away. The closed connection is still enlisted in the active transaction scope, so it remains in a special subpool until the active transaction is completed. Once the transaction has completed, all connections are released back to their appropriate connection pools.