I've written TransactionContext class that is instantiated in application layer and is send down to business and data layer, allowing nested transactions. Now I must decide: 1. Should I use explicit transactions and let every function to call begin, commit or rollback on the transaction if needed? 2. I can start the transaction implicitly when TransactionContext is created and let nested methods only to rollback
Now, I would use second approach because it's easier to code: no worry about begin, commit or rollback in every method, just set the rollback flag on transaction and let only top most method worry about commit or rollback. Problem is that I'm not sure if wrapping all database traffic in transaction is a good idea.
What are possible negative effects with wrapping all database calls inside the transaction?
My setup is ASP.NET appliaction and MSSQL Server database. It is possible that appliaction and database will be on different servers, if that's something that influence the decision.
Single SQL statements are already wrapped in an implicit transaction. Use a transaction where it is needed, i.e. when updating multiple tables in an atomic operation. Wrapping all calls to the DB is not a good idea: it might lead to reduced throughput and blocking.
Altough SQL Server supports nested Transactions, they might not work as you expect:
Committing inner transactions is ignored by the SQL Server Database Engine. The transaction is either committed or rolled back based on the action taken at the end of the outermost transaction. If the outer transaction is committed, the inner nested transactions are also committed. If the outer transaction is rolled back, then all inner transactions are also rolled back, regardless of whether or not the inner transactions were individually committed.
Ref.: Nesting Transactions