SQL Transaction Step by Step

In this blog, I will explain what is transaction, how to transaction work ,why we need transaction function and solve some basic error in Transaction.

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.