Please enable Javascript to correctly display the contents on Dot Net Tricks!

SQL Server Transactions Management

  Author : Shailendra Chauhan
Updated On : 24 Sep 2016
Total Views : 147,259   
Support : SQL Server 2005,2008,2012
 

A transaction is a set of T-SQL statements that are executed together as a unit like as a single T-SQL statement. If all of these T-SQL statements executed successfully, then a transaction is committed and the changes made by T-SQL statements permanently saved to database. If any of these T-SQL statements within a transaction fail, then the complete transaction is cancelled/ rolled back.

We use transaction in that case, when we try to modify more than one tables/views that are related to one another. Transactions affect SQL Server performance greatly. Since When a transaction is initiated then it locks all the tables data that are used in the transaction. Hence during transaction life cycle no one can modify these tables’ data that are used by the transaction. The reason behind the locking of the data is to maintain Data Integrity.

Types of Transactions

  1. Implicit Transaction

    Implicit transactions are maintained by SQL Server for each and every DDL (CREATE, ALTER, DROP, TRUNCATE), DML (INSERT, UPDATE, DELETE) statements. All these T-SQL statements runs under the implicit transaction. If there is an error occurs within these statements individually, SQL Server will roll back the complete statement.

  2. Explicit Transaction

    Explicit transactions are defined by programmers. In Explicit transaction we include the DML statements that need to be execute as a unit. Since SELECT statements doesn’t modify data. Hence generally we don’t include Select statement in a transaction.

Transactions Example

 CREATE TABLE Department
(
 DeptID int PRIMARY KEY, 
 DeptName varchar(50) NULL,
 Location varchar(100) NULL,
 )
GO
 CREATE TABLE Employee
(
 EmpID int PRIMARY KEY, 
 Name varchar(50) NULL,
 Salary int NULL, 
 Address varchar(100) NULL,
 DeptID int foreign Key references Department(DeptID)
) 
 --Now Insert data
INSERT INTO Department(DeptID,DeptName,Location)VALUES(1,'IT','Delhi')
GO
INSERT INTO Employee(EmpID,Name,Salary,Address,DeptID)VALUES(1,'Mohan',15000,'Delhi',1)
SELECT * FROM Department
SELECT * FROM Employee 
 BEGIN TRANSACTION trans
BEGIN TRY
INSERT INTO Department(DeptID,DeptName,Location)VALUES(2,'HR','Delhi')
INSERT INTO Employee(EmpID,Name,Salary,Address,DeptID)VALUES(1,'Mohan',18000,'Delhi',1)
 IF @@TRANCOUNT > 0
 BEGIN COMMIT TRANSACTION trans
 END
END TRY
BEGIN CATCH
 print 'Error Occured'
 IF @@TRANCOUNT > 0
 BEGIN ROLLBACK TRANSACTION trans 
 END
END CATCH 
 --Now Select data to see transaction affects
SELECT * FROM Employee
SELECT * FROM Department 
 --Transaction with Save Point BEGIN TRANSACTION trans
BEGIN TRY
INSERT INTO Department(DeptID,DeptName,Location)VALUES(2,'HR','Delhi')
IF @@TRANCOUNT > 0
 BEGIN SAVE TRANSACTION trans;
 END
INSERT INTO Department(DeptID,DeptName,Location)VALUES(3,'Admin','Delhi') 
INSERT INTO Employee(EmpID,Name,Salary,Address,DeptID)VALUES(1,'Mohan',18000,'Delhi',1)
IF @@TRANCOUNT > 0
 BEGIN COMMIT TRANSACTION trans
 END
END TRY
BEGIN CATCH
 print 'Error Occured'
IF @@TRANCOUNT > 0
 BEGIN ROLLBACK TRANSACTION trans
 END
END CATCH 
 --Now Select data to see transaction affects
SELECT * FROM Employee
SELECT * FROM Department 
Summary

In this article I try to explain transaction management in Sql Server with example. I hope after reading this article you will be know how to manage transaction in Sql Server. I would like to have feedback from my blog readers. Please post your feedback, question, or comments about this article.


YOU MIGHT LIKE
Free Interview Books
 
COMMENTS (0)
13 FEB
ASP.NET Core Development (online)

07:00 AM-09:00 AM IST( MON, WED, FRI)

Know More
25 JAN
Angular2 and Angular1 Development (online)

09:00 PM-10:30 PM IST(MON-FRI)

Know More
24 JAN
ASP.NET MVC with Angular2 Development (online)

07:00 AM-09:00 AM IST(Tuesday & Thursday)

Know More
21 JAN
Xamarin Forms : Build Cross-platform Apps (offline)

05:00 PM-06:30 PM IST

Know More
20 JAN
NODEJS & MEAN Stack 2 Development (online)

09:00 PM-11:00 PM IST( MON, WED, FRI)

15 JAN
PPC Marketing (offline)

04:00 PM-05:300 PM

10 JAN
ASP.NET MVC with AngularJS Development (online)

09:00 PM-11:00 PM IST on (TUE, THRU,SAT)

3 JAN
ASP.NET MVC with AngularJS Development (online)

07:00 AM-09:00 AM IST

31 DEC
ASP.NET MVC with AngularJS Development (offline)

09:30 AM-11:00 AM

31 DEC
Angular2 and Angular1 Development (offline)

11:00 AM-12:30 AM

26 NOV
ASP.NET MVC with AngularJS Development (offline)

03:00 PM-04:30 PM

5 NOV
ASP.NET MVC with AngularJS Development (offline)

08:00 AM-09:30 AM

4 SEP
MEAN Stack 2 Development (offline)

11:00 AM-12:30 PM.

BROWSE BY CATEGORY
 
RECENT ARTICLES
SUBSCRIBE TO LATEST NEWS
 
LIKE US ON FACEBOOK
 

Professional Speaks

+