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.
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
Execute following UPDATE statements which will ROLLBACK transaction when error come across.
--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
Here is the error I have received while executing UPDATE code block.
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)
Reference: Ritesh Shah
Note: Microsoft Books online is a default reference of all articles.