Beginning Microsoft® SQL Server® 2008 Programming

Chapter 14. Transactions and Locks

This is one of those chapters that, when you go back to work, makes you sound like you've had your Wheaties today. Nothing we're going to cover in this chapter is wildly difficult, yet transactions and locks tend to be two of the most misunderstood areas in the database world. As such, this beginning (or at least I think it's a basic) concept is going to make you start to look like a real pro.

In this chapter, we're going to:

• Demystify transactions

• Examine how the SQL Server log and checkpoints work

• Unlock your understanding of locks

We'll learn why these topics are so closely tied to each other and how to minimize problems with each.

Transactions

Transactions are all about atomicity. Atomicity is the concept that something should act as a unit. From our database standpoint, it's about the smallest grouping of one or more statements that should be considered to be all or nothing.

Often, when dealing with data, we want to make sure that if one thing happens, another thing happens, or that neither of them does. Indeed, this can be carried out to the degree where 20 things (or more) all have to happen together or nothing happens. Let's look at a classic example.

Imagine that you are a banker. Sally comes in and wants to transfer $1,000 from checking to savings. You are, of course, happy to oblige, so you process her request.

Behind the scenes, something like this is happening:

UPDATE checking SET Balance = Balance - 1000 WHERE Account = 'Sally' UPDATE savings ...