Consider two approaches to update data:
Approach 1:
Update Table 1, commit
Update Table 2, commit
Update Table 3, commit
Approach 2:
Update Table 1, Table 2, Table 3, commit.
Now Approach 1 is not atomic and uses three transactions. However, it could be argued Approach 2 will hang onto locks longer so will not scale as well. In a scenario where you don't need true atomicity, can an argument be made that Approach 1 will perform better because it won't hang onto locks for so long.
The thing, is you need statistics and evidence to make good architecture decisions and not just speculation. I'd like to set up a trace for SqlServer so I can get an idea of how much performance different there ie between both approaches? I want to know if it is 1ms or 500ms?
I am guessing it is not that much, but wish to confirm my suspicions.
Many thanks.
Best Answer
All write locks are held until the end of the transaction. Otherwise rollback would be impossible. When considering the span of a transaction the first and foremost question to ask is 'Does each operation leave the database in a consistent state from a business rules point of view?. If this is true, then the operations can be done in individual transactions. If is not true then an encompassing transaction must span the all operations, otherwise you risk leaving the database in an inconsistent state (think typical debit account/credit account transaction).