Professional SQL Server™ 2005 Programming

12.6. Summary

Transactions and locks are both cornerstone items to how SQL Server works and, therefore, to maximizing your development of solutions in SQL Server.

By using transactions, you can make sure that everything you need to have happen as a unit happens, or none of it does. SQL Server's use of locks ensures that we avoid the pitfalls of concurrency to the maximum extent possible (you'll never avoid them entirely, but it's amazing how close you can come with a little — OK a lot — of planning). By using the two together, you are able to pass what the database industry calls the ACID test. If a transaction is ACID, then it has:

• Atomicity — The transaction is all or nothing.

• Consistency — All constraints and other data integrity rules have been adhered to, and all related objects (data pages, index pages) have been updated completely.

• Isolation — Each transaction is completely isolated from any other transaction. The actions of one transaction cannot be interfered with by the actions of a separate transaction.

• Durability — After a transaction is completed, its effects are permanently in place in the system. The data is "safe," in the sense that things such as a power outage or other non-disk system failure will not lead to data that is only half-written.

In short, by using transactions and locks, you can minimize deadlocks, ensure data integrity, and improve the overall efficiency of your system.

In our next chapter, we'll be looking at triggers. Indeed, we'll see that, for many ...