Wednesday, August 13, 2014

SQL Server Transactions Management

Transaction is a set of T-SQL statements that are executed together as a unit like as a single T-SQL statement. If all of these T-SQL statements executed successfully, then a transaction is committed and the changes made by T-SQL statements permanently saved to database. If any of these T-SQL statements within a transaction fail, then the complete transaction is cancelled/ rolled back.
We use transaction in that case, when we try to modify more than one tables/views that are related to one another. Transactions affect SQL Server performance greatly. Since When a transaction is initiated then it locks all the tables data that are used in the transaction. Hence during transaction life cycle no one can modify these tables’ data that are used by the transaction. The reason behind the locking of the data is to maintain Data Integrity.

Types of Transactions

1.               Implicit Transaction

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

 

 

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


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

-----------------------------------------------------------------------------------------------


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

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

--------------------------------------------------------------------------------

      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

-----------------------------------------------------------------------------------------------
      SELECT * FROM Employee

      SELECT * FROM Department