SQL Server Transactions Management

SQL Server Transactions Management

18 Mar 2024
Advanced
9.48K Views
9 min read

SQL Server Transactions Management: An Overview

SQL Server transactions ensure the atomicity, consistency, isolation, and durability (ACID) of database operations. They enable the compilation of numerous SQL statements into a single unit of work, assuring data integrity and recoverability in the event of failure. Understanding transaction management is essential for database developers and administrators, whether through self-study materials like a SQL Server Tutorial or organized training like a SQL Server Certification Course.

What is SQL Server Transactions?

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

To ensure data integrity, SQL Server uses transactions when updating several connected tables/views. They have a substantial influence on performance since they lock the data used in the transaction and prohibit anyone from modifying it during its existence.

Types of Transactions

1. Implicit Transaction

Implicit transactions are maintained by SQL Server for every DDL (CREATE, ALTER, DROP, TRUNCATE), and DML (INSERT, UPDATE, DELETE) statements. All these T-SQL statements run 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 transactions, we include the DML statements that need to be executed as a unit. Since SELECT statements don’t modify data. Hence generally we don’t include a 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)
) 

This SQL script generates two tables: "Department" (primary key), DeptName, and Location, and "Employee" (primary key), Name, Salary, Address, and DeptID. These tables establish the relationship between an employee and a department.

--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 

This code inserts data into the "Department" and "Employee" tables, containing a record for an IT department in Delhi and an employee named Mohan with a salary of 15000 who lives in Delhi and works for the IT department. It then retrieves the data of the two tables.

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 

This code starts a transaction called 'trans' and tries to insert data into the "Department" and "Employee" databases. If successful, the transaction is committed; otherwise, it reverses the operation and publishes an error message.

--Now Select data to see transaction affects
SELECT * FROM Employee
SELECT * FROM Department 

This code retrieves data from the "Employee" table to assess transaction consequences, then selects data from the "Department" table to investigate any relevant changes.

--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 

This code starts a transaction called 'trans', inserts data into the "Department" table, saves a savepoint within the transaction, proceeds with more insertions, and commits the transaction if successful. If an error occurs, it returns to the last savepoint in the transaction and publishes an error message.

--Now Select data to see transaction affects
SELECT * FROM Employee
SELECT * FROM Department 

These lines fetch data from the "Employee" and "Department" databases to see the transaction's consequences, including any modifications made by previous SQL operations.

Read More:

Summary

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

FAQs

Q1. How can a transaction be maintained in SQL Server?

Autocommit mode is SQL Server's default transaction management mode. When a T-SQL statement is completed, it is either committed or rolled back. If a statement is completed successfully, it is committed; if any mistakes occur, it is forced to roll back.

Q2. What are the three types of transactions available in SQL Server?

There are three major types of transactions in SQL Server: 
  • SQL Server uses auto-commit transaction mode. 
  • SQL Server supports implicit transaction mode. 
  • SQL Server supports explicit transaction mode.

Q3. What are the ACID attributes of a transaction?

The acronym ACID stands for Atomicity, Consistency, Isolation, and Durability. These attributes ensure the dependability and integrity of SQL Server transactions.

Q4. How can I manage transactions in SQL Server?

The BEGIN TRANSACTION, COMMIT, & ROLLBACK statements allow you to set transaction boundaries and manage the end of it. COMMIT applies changes permanently, whereas ROLLBACK reverses them.

Take our Sqlserver skill challenge to evaluate yourself!

In less than 5 minutes, with our skill challenge, you can identify your knowledge gaps and strengths in a given skill.

GET FREE CHALLENGE

Share Article

Live Classes Schedule

Our learn-by-building-project method enables you to build practical/coding experience that sticks. 95% of our learners say they have confidence and remember more when they learn by building real world projects.
Azure DevOps Certification Training Course Jun 01 SAT, SUN
Filling Fast
10:00AM to 12:00PM (IST)
Get Details
Data Structures and Algorithms Training Jun 04 TUE, FRI
Filling Fast
08:30PM to 10:00PM (IST)
Get Details
ASP.NET Core Certification Training Jun 14 MON, WED, FRI
Filling Fast
07:00AM to 08:30AM (IST)
Get Details
ASP.NET Core (Project) Jun 22 SAT, SUN
Filling Fast
08:30PM to 10:30PM (IST)
Get Details

Can't find convenient schedule? Let us know

About Author
Shailendra Chauhan (Microsoft MVP, Founder & CEO at Scholarhat by DotNetTricks)

Shailendra Chauhan is the Founder and CEO at ScholarHat by DotNetTricks which is a brand when it comes to e-Learning. He provides training and consultation over an array of technologies like Cloud, .NET, Angular, React, Node, Microservices, Containers and Mobile Apps development. He has been awarded Microsoft MVP 8th time in a row (2016-2023). He has changed many lives with his writings and unique training programs. He has a number of most sought-after books to his name which has helped job aspirants in cracking tough interviews with ease.
Accept cookies & close this