The main objective of a transaction is to ensure that either every statement within that transaction is executed successfully or none of them. So, you would logically wrap in one transaction related statements that should be executed together. A classic example is that of transferring money from one account to another – the action of subtracting the money from account A must happen together with the action of adding that same amount of money to account B (either both or none) otherwise, if let’s say the system crashed right after subtracting the money from account A and before adding them to account B the money will disappear! So, great, when one understands this a legitimate question comes to mind – what’s the point of wrapping a single statement in a transaction?
Well, we are humans and as such we are prone to making mistakes, sometimes, big and costly mistakes so, wouldn’t it be nice to “erase” our mistakes when we realize that? That’s what a simple Begin Transaction statement provides us – the ability to rollback our mistakes. How does one realize that he made a mistake – the first indicator is the “number of rows effected” which SQL Server reports – if that number is larger than you expected then maybe you made a mistake on your statement. How else can I tell? The cool thing is that SQL Server will let you read the uncommitted rows and see what your statement did - so you can issue a select and review the rows you just updated for example. If you do realize that you made a mistake you simply issue a rollback and all is good – it is like it never happened. If you are sure your statement did what you intended it to do then you issue a commit transaction and the changes you just made become permanent.
So again, be diligent and wrap every update, delete or insert statement in a begin transaction / rollback / commit transaction. It is much better to be safe then sorry!
Applies to: SQL Server, SQL Server Mangement Studio, Query Analyzer
Subscribe to:
Post Comments (Atom)
No comments:
Post a Comment