09
NovSQL Server Transactions Management
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?
Q2. What are the three types of transactions available 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?
Q4. How can I manage transactions in SQL Server?
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.