Professional SQL Server™ 2005 Programming

12.1. 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 do. 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, we have something like this happening:

UPDATE checking
   SET Balance = Balance—1000
   WHERE Account = 'Sally'
UPDATE savings
   SET Balance = Balance + 1000
   WHERE Account = 'Sally'

This is a hypersimplification of what's going on, but it captures the main thrust of things: you need to issue two different statements — one for each account.

Now, what if the first statement executes and the second one doesn't? Sally would be out of a thousand dollars! That might, for a short time, seem okay from your perspective (heck, you just made a thousand bucks!), but not for long. By that afternoon you'd have a steady stream of customers leaving your bank — it's hard to stay in the bank business with no depositors.

What you need is a way to be certain that if the first statement executes, ...