I’ve done a couple of posts now talking about how rolling back a transaction works. I thought this time I would back up a bit and talk about what exactly a transaction is and why we have them. A transaction is simply a unit of work. A unit of work is a series of inserts/updates/deletes that go together. So why do we care? Well one of my favorite examples is paying a bill.

Bob is paying $50 to his internet provider “ImaPain”. This is going to require two commands.

UPDATE Balances SET CurrentBalance = CurrentBalance-50 
WHERE name = 'Bob'
UPDATE Balances SET CurrentBalance = CurrentBalance+50 
WHERE name = 'ImaPain'

So what happens if we cancel the transfer in the middle and only the first command has occurred? Bob now has $50 less and his provider still hasn’t been paid. No one is happy at this point. But what if instead we wrap this “unit of work” in a transaction?

BEGIN TRANSACTION
UPDATE Balances SET CurrentBalance = CurrentBalance-50 
WHERE name = 'Bob'
UPDATE Balances SET CurrentBalance = CurrentBalance+50 
WHERE name = 'ImaPain'
COMMIT

Now if we cancel the transfer in the middle (deliberately or through a crash) the whole process rolls back at once and Bob isn’t out any money. His provider hasn’t been paid yet but at least Bob still has the money to do so.

That was an explicit transaction. An explicit transaction is defined by BOL as “one in which you explicitly define both the start and end of the transaction.”. There are also implicit transactions that SQL creates and ends on its own. Again a unit of work but this time we don’t have to deliberately start and commit a transaction. Here we are giving everyone a raise!

UPDATE PayTable SET HourlyPay = HourlyPay + 1

Oh no! Our connection was lost about half way through the command! We have updated 20 employees of our total roster of 50. SQL uses an implicit transaction to make sure that any changes before the end of the command are rolled back. It wouldn’t do for a random half of the employees to get a raise and not the other.

If you stop there it sounds like the best thing to do is to wrap everything in transactions to prevent possible problems. Unfortunately this has its own set of problems. As a rule transactions should be as small as possible. Among other things this is to avoid blocking. Discussing transactions and blocking in detail is way beyond the scope of this post as you have to get into the various transaction isolation levels and how each handles blocking. In general though all locks held by a statement in a transaction are held until the end of the transaction. If this happens to block a statement in another transaction then that block will be held until the end of the first transaction. Another good reason to keep your transactions as small as possible is to avoid losing information during a crash. Using the example above let’s say we both Bob and James are paying their internet provider and it’s all put into a single transaction.

BEGIN TRANSACTION
UPDATE Balances SET CurrentBalance = CurrentBalance-50 
WHERE name = 'Bob'
UPDATE Balances SET CurrentBalance = CurrentBalance+50 
WHERE name = 'ImaPain'
UPDATE Balances SET CurrentBalance = CurrentBalance-50 
WHERE name = 'James'
UPDATE Balances SET CurrentBalance = CurrentBalance+50 
WHERE name = 'ImaPain'
COMMIT

What happens if the connection fails or the server goes down in the middle of the transaction, say after the 3rd statement. Even though both statements required for Bob’s payment have completed his payment is rolled back along with James’. If however we had used 2 transactions then we would only have lost the one pair of updates instead of both.

BEGIN TRANSACTION
UPDATE Balances SET CurrentBalance = CurrentBalance-50 
WHERE name = 'Bob'
UPDATE Balances SET CurrentBalance = CurrentBalance+50 
WHERE name = 'ImaPain'
COMMIT
BEGIN TRANSACTION
UPDATE Balances SET CurrentBalance = CurrentBalance-50 
WHERE name = 'James'
UPDATE Balances SET CurrentBalance = CurrentBalance+50 
WHERE name = 'ImaPain'
COMMIT

Now when the same crash happens only the incomplete payment fails and is rolled back. Bob’s payment has completed successfully.

To sum it up transactions are an extremely important tool used to make sure that a unit of work is either completed together or rolled back together. For additional reading you should look at ACID (Atomic, Consistent, Isolated, Durable) compliance.

Transactions are a big subject which I’m going to explore over several posts. I am by no means going to cover the subject exhaustively but if you have any subjects you would like me to cover or think I’ve missed something feel free to comment or email me.

• Transactions: Rolling back a nested transaction.
• Transactions: Rolling back part of a transaction.
• Transactions: Rolling back a transaction inside a stored procedure.
• Transactions: Who, What and Where
• Transactions: Creating a single restore point across multiple databases.
• Transactions: What commands aren’t allowed.

Filed under: Microsoft SQL Server, SQLServerPedia Syndication, T-SQL, Transactions Tagged: code language, language sql, microsoft sql server, T-SQL, transactions