Transaction in SQL Server

Dharm
Posted by in Sql Server category on for Intermediate level | Points: 250 | Views : 681 red flag
Rating: 4 out of 5  
 2 vote(s)

In this article we will explain transaction in SQL Server.

Transaction is a set of activity or operation that should be considered as a unit work.

In transaction either every operation will execute or none will execute. This means if every operation in the transaction executes then only the whole transaction will execute otherwise it will rollback if any operation of the transaction fails to execute.

  • Transaction can be anything such as,
  • It can be a set of logical sql query,
  • It can be copying of a set of files,
  • Etc.

Let’s take an example to understand transaction in more better way and the best example for this is transferring of some amount from one account to other.

The operation involved in transferring of some amount from one account to other is as follows,

  • Deducting money from account A and updating account A.
  • Adding money in account B and updating account B.

Both operation must succeed together and changes must be committed to the accounts or both must fail together and rolled back so that both the accounts maintained their consistency state. There shouldn’t be any condition where money is deducted from account A but it is not added to account B or vice versa.

By using transaction we can take care whether the transaction executes completely or fails completely.

Let’s understand the transaction using small code in sql server,

In this example, there is table know as bank which as account, name and balance.then I have inserted some values in the bank table as follows.

Use master 
CREATE TABLE BANK (ACCOUNT INT, NAME VARCHAR(MAX), BALANCE MONEY)
Insert into Bank values(1,'Rahul',20000)
Insert into Bank values(2,'Raj',35000)
Insert into Bank values(3,'Dharm',20000)


Let’s update both account using transaction

1. In this we are putting account =5(which is not in the table) so whole transaction should rollback as it is a wrong input so the operation will fail.

Use master 
BEGIN TRANSACTION 
DECLARE @COUNT1 int, @Count2 int
UPDATE BANK SET BALANCE = BALANCE-5000 WHERE ACCOUNT=5  SET @COUNT1=@@ROWCOUNT
UPDATE BANK SET BALANCE = BALANCE+5000 WHERE ACCOUNT=3 SET @Count2=@@ROWCOUNT
IF @COUNT1=@Count2
begin
  COMMIT TRANSACTION
  Print 'Amount is transferred'
  end
ELSE
begin
  ROLLBACK TRANSACTION
  Print 'Amount is not transferred'
  end


So it will not affect the table and table value will remain same.


2. In this we are putting every input properly so that whole transaction should execute and the table should be updated.

Use master 
BEGIN TRANSACTION 
DECLARE @COUNT1 int, @Count2 int
UPDATE BANK SET BALANCE = BALANCE-5000 WHERE ACCOUNT=2  SET @COUNT1=@@ROWCOUNT
UPDATE BANK SET BALANCE = BALANCE+5000 WHERE ACCOUNT=3 SET @Count2=@@ROWCOUNT
IF @COUNT1=@Count2
begin
  COMMIT TRANSACTION
  Print 'Amount is transferred'
  end
ELSE
begin
  ROLLBACK TRANSACTION
  Print 'Amount is not transferred'
  end


So it will affect the table and the updated value can be seen.


Page copy protected against web site content infringement by Copyscape

About the Author

Dharm
Full Name: dharm yadav
Member Level: Starter
Member Status: Member
Member Since: 8/2/2016 7:12:32 AM
Country: Other



Login to vote for this post.

Comments or Responses

Posted by: Amatya on: 9/30/2016 | Points: 25
Nice Explanation.. Keep it up. :)

Login to post response

Comment using Facebook(Author doesn't get notification)