Importance of transaction in SQL Server

Importance of transaction in SQL Server

Recently I have written few articles on error handling (list given in the last paragraph of this article) topic whereas I have used Transaction in my TSQL script there and have promised blog reader that I will come up with more details and explanation of transaction. Finally today I thought to write about transaction.

Transaction is one of the most important part for developer to be master in development. We might have cases where we are doing more then one combination of INSERT / UPATE / DELETE (DML statements) in TSQL code block. It is quite possible that any of the DML operation failed in-between execution and we want to revert back any DML changes made prior from that failure to keep consistency and integrity. Transaction comes to your help for this need and makes your database ACID (Atomicity, Consistency, Isolation and Durability) compliant (Click here to know more about ACID.).

According to me, there should be Begin Transaction, Commit Transaction and ROLLBACK Transaction with each TSQL block if it is using DML statements, no matter whether code block has one or more DML operation going.

Transaction creates locking so it is also advisable to keep your transaction as short as possible to avoid locking and blocking issue.

Let us see how we can use transaction in TSQL code block. We will need to table with some data for demonstration. Let us create two table and define foreign key between them.

[sourcecode language=”sql”]
CREATE TABLE tblEmployeeDetail
(
ID INT IDENTITY(1,1) CONSTRAINT pk_tblEmployeeDetail_Id PRIMARY KEY
,EmployeeName Varchar(100)
,Designation VARCHAR(50)
,City Varchar(20)
)
GO

CREATE TABLE tblEmployeeSalary
(
ID INT IDENTITY(1,1)
,EmployeeID INT CONSTRAINT fk_tblEmployeeDetail_id FOREIGN KEY REFERENCES tblEmployeeDetail(ID)
,Salary INT NOT NULL
)
GO

INSERT INTO tblEmployeeDetail
SELECT ‘Ritesh Shah’,’DBA’,’Ahmedabad’ UNION ALL
SELECT ‘Teerth Shah’,’Developer’,’Ahmedabad’
GO

INSERT INTO tblEmployeeSalary
SELECT 1,50000 UNION ALL
SELECT 2,45000
GO[/sourcecode]

Execute following UPDATE statements which will ROLLBACK transaction when error come across.

[sourcecode language=”sql”]
–We are now going to update designation in EmployeeDetail table
–and update the salary. if there is an issue in any of the UPDATE statement
–both table should be in the previous state
BEGIN TRY
BEGIN TRANSACTION
UPDATE tblEmployeeDetail SET Designation=’DBA Manager’ WHERE ID=1

–giving NULL value though it is not allowed in SALARY field (as it is NOT NULL field)
–it will generate an error and the Designation in tblEmployeeDetail table will also revert back
UPDATE tblEmployeeSalary SET Salary=NULL WHERE EmployeeID=1
COMMIT TRANSACTION
END TRY
BEGIN CATCH
declare @trancount int;
set @trancount = @@trancount;
IF @trancount>0
BEGIN
ROLLBACK;
THROW
END
END CATCH
GO

–Confirm whether any record is changed or not
SELECT * FROM tblEmployeeDetail
SELECT * FROM tblEmployeeSalary
GO
[/sourcecode]

Here is the error I have received while executing UPDATE code block.

1Error

Please note that This code should work in older version of SQL Server eg: SQL Server 2008 or 2008 R2 as well. You have to use RAISERROR instead of THROW statement.

If you are interested to read error handling article I have written previously, have a look at list below:

Error handling with “THROW” command in SQL Server 2012 (Click Here)
List of Errors and severity level in SQL Server with catalog view sysmessages (Click Here)
Create custom error message by sys.sp_addmessage in SQL Server 2012 (Click Here)

If you like this article, do like “Extreme-Advice” page in Facebook.

Reference: Ritesh Shah

http://Extreme-Advice.com

http://www.sqlhub.com

Note: Microsoft Books online is a default reference of all articles.

Author: Riteshshah

Ritesh Shah is a database professional with having 16+ years of experience in various domain of IT field. He is a author of many technical articles on Microsoft Technology and authored a book of SQL Server 2012 Performance Tuning cookbook. He loves work with different Microsoft Products.