SQL Transaction Step by Step

Introduction

Transaction is most important role play in our Development line. In Transaction have two basic rules.

Process Result

Everything is OK= Success
Some error = Role back Everything.

Transaction process system

Real Time Example

Every buddy like a travlining. suppose i want go HP(Himachal Pardesh) for vinnter vocation. I Need a Train seat for comfertable travel. I Book the Train seat ON ONLINE(IRTC WEBSITE). For seat confimation some procedure follow.

    Step 1: Login on IRTC webiste.

    Setp 2: Book your seat Transaction Start here.

    Stpe 3: Select the Payment.

    Step 4: Proceed the Payment.

    Step 5: Debit ticket amount in your account //if amount not sufficant Transaction is Role Back.

    Step 6: Payment is done but IRTC server down // if some error occur on this stage Transaction is role Back.

    Step 7: payment Received IRTC account.

    Step 8: Ticket is booked after every step complete // Transaction is complete.

    Transaction END here.

    Step 9: received the confirmation mail or Message.
    information message

Transaction is both side working Front END(c#) and Back End (SQL Server).

How to declare SQL Transaction

  1. Begin tran    
  2.  --do stuff here     
  3. commit tran  
With Example:
  1. SET XACT_ABORT ON --this one    
  2. set nocount on                                      
  3.     
  4. BEGIN tran                                    -- * Transaction Start here * -- -- * Product * --    
  5.       -- do stuff here  
  6.     
  7.  if(@@ERROR<>0)    
  8.  begin    
  9. rollback tran    
  10. set @succ=0    
  11. end    
  12. else    
  13. begin    
  14. set @succ=-1    
  15. commit tran    
  16. end   
Nesting Transaction

SQL Server allows you to nest transactions. Basically, this feature means that a new transaction can start even though the previous one is not complete. Transact-SQL allows you to nest transaction operations by issuing nested BEGIN TRAN commands. The @@TRANCOUNT automatic variable can be queried to determine the level of nesting - 0 indicates no nesting , 1 indicates nesting one level deep, and so fourth.

Some error occur

You can put set xact_abort on before your transaction to make sure sql rolls back automatically in case of error.