Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question
2  
Actually I'd be willing to say Approach 1 is not atomic. In fact, Approach 2 is atomic. They either all commit together or rollback as one. All pass or all fail. –  Thomas Stringer Mar 21 '12 at 17:41
    
@Shark, I have updated question and made it clearer what I am looking for. –  dublintech Mar 21 '12 at 21:01

1 Answer 1

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

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.