SQL 2012 Easy Transaction Example


Transactions are one of those things you learn about in databases last. You never think about using or needing them until a more advanced scenario exists. But we’ve all heard the ATM example of why they are so important, but how many of us write software for ATMs?

So here is a drop dead simple example. We have 2 accounts at a bank: cashbook#1 and cashbook#2.

To make things easy, all entries in cashbook#1 are $100, and all in cashbook#2 are $200-to begin with. Each account has an id, and the objective is to:

1. change account id=12 (cashbook#1) from $100.00 to $101.00

2. change account id=11 (cashbook#2) from $200.00 to $201.00


Here is how things look when things go well (no problems/errors):

The output of this little script is predictably:

ScreenHunter_24 May. 08 15.14

Nothing shocking here. Account 12 & 11 had $1 added to their accounts.

BUT! Suppose there was a problem with the line:

How could we mess this up? Well, for one thing we can call an id that doesn’t exist, changing the line above to:

Where we know that account id 123 doesn’t exist. Would cashbook#1 get updated (the unaffected/correct line)?


Well, the nature/purpose of TRANSACTIONS is really quite simple:

“If even ONE of the actions in a TRANSACTION scope fail, DO NONE OF THE ACTIONS!

So… that sounds pretty simple. Above, we are proposing to make one of the actions…

Fail. More than one can fail & still cause the same ‘ROLLBACK’, but it only takes one!

Great! Now here is the modified, WILL FAIL code we’ll run next:

And if you guessed the below would be the result, give yourself a cookie! You are now a certified Master of Transactions!



Leave a Reply

Your email address will not be published. Required fields are marked *