SQL Server
Transactions
Management: An
Overview
Lab Session
Introduction
• 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',1500
0,'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.
cont’d...
--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.
