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.



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.

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

How to declare SQL Transaction

Begin tran
 --do stuff here
commit tran

With Example:

SET XACT_ABORT ON --this one
set nocount on

BEGIN tran                                    -- * Transaction Start here * -- -- * Product * --
      -- do stuff here

 if(@@ERROR<>0)
 begin
rollback tran
set @succ=0
end
else
begin
set @succ=-1
commit tran
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.