Transactions: What are they?

4

January 8, 2014 by Kenneth Fisher

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.

About these ads

4 thoughts on “Transactions: What are they?

  1. Kamran says:

    Nice Article…

  2. John says:

    “There are also implicit transactions that SQL creates and ends on its own.”

    I believe you’re referring here to the auto-committed transactions “mode” in SQL Server. From what I understand, “Implicit” transactions (IMPLICIT_TRANSACTIONS) are a specific mode in SQL Server, which while implicitly begun, need to be *explicitly* committed (a rather counter-intuitive wrinkle). It might be helpful to clarify this for your readers, i.e., the 3 “modes” of transaction management – especially given the difference between our common understanding of the word “implicit” and SQL Server’s implementation of IMPLICIT_TRANSACTIONS.

    • Actually I’m talking about the transactions that SQL creates before each statement. For example if you update a row and don’t create an explicit transaction then SQL will create an implicit transaction and commit it at the end of the statements execution. Beyond that the mode you are talking is very interesting but I could be writing about transactions for weeks and not come to the end. I had to call it a day somewhere :)

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

Join 423 other followers

%d bloggers like this: