SQL Transactions

What is Transaction?

We hear this term every day when we go to bank, withdraw money from ATM, do fund transfer from Account 1 to Account 2.

So, do we know transactions are vital parts of our life?

How?

1. What if you withdraw money from ATM and you get SMS saying your account is debited with $200 and ATM machine did not dispatched the money?
   
   
2. What if you transfer $2000 to your friend’s account, money is debited from your account but not credited to your friend’s account? So, where the money gone?
   
   May be you lost internet connectivity while doing fund transfer or power cut in ATM machine. So, will you hold bank people responsible for this loss?

So, now we realize we need some mechanism to stop this process in between if anything goes wrong.

So let’s divide the steps as below:

1. Login to Net Banking
2. Debit money from Account 1
3. Credit money to Account 2
4. Send SMS/Email Alerts

Now, if anything goes wrong in between I would like to revert to step 1 so there is no loss to me.
In short if step 3 or 4 fails then I will revert my step 2 as well so that there is no loss. This terminology is nothing but Transaction.

In my previous article we talked about Error/Exception handling, let’s discuss about how to achieve transactions in SQL.

Please see the attachment for sample code as I want people to explore the syntax and transaction logic on their own.

References:

• BEGIN TRANSACTION (Transact-SQL)
• SQL - Transactions
• Introduction to Transactions

I will come up with new topics in SQL in my future articles.

Till then….stay tuned…..Keep learning……Cheers !!!