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 : 144,885   
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)
14 DEC
ASP.NET MVC with AngularJS Development (online)

MON-FRI 07:30 AM- 09:00 AM IST

Know More
11 DEC
ASP.NET MVC with AngularJS Development (offline)

Sat-Sun 09:30 AM-11:00 AM IST

Know More
5 DEC
AngularJS Development (online)

Mon - Fri     6:30 AM-7:30 AM IST

3 DEC
AngularJS Development (offline)

SAT,SUN     11:00 AM-12:30 PM IST

3 DEC
MEAN Stack Development (offline)

Sat, Sun     (11:00 AM-12:30 PM IST)

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

(SAT,SUN)     03:30 PM-05:00 PM IST

24 NOV
ASP.NET MVC with AngularJS Development (online)

MON-FRI     09:30 PM-11:00 PM IST

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

SAT,SUN     08:00 AM-09:30 AM

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

MON-FRI     07:30 AM-09:00 AM IST

25 OCT
.NET Development (offline)

Mon-Fri     9:00 AM-11:00 AM IST

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

Professional Speaks

+