Transactions in SQL Server (Part – II)

Transaction can be implemented in SQL Server (DB Level) as well as in Dot Net. In Part 1 of the blog series on Transactions, we  implemented transactions in front-end code(C# code).  However, we can also define transactions in the back-end code like SQLServer database.

Transaction Control

These are the commands used to control transactions:

• COMMIT  – To save the changes.
• ROLLBACK – To rollback the changes.
• SAVEPOINT – Creates points within groups of transactions in which to ROLLBACK
• SET TRANSACTION –  Places a name on a transaction.

Types of Transactions

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 run under the implicit transaction. If an error occurs within these statements individually, SQL Server will roll back the complete statement.

Explicit transactions are defined by programmers. In Explicit transaction 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 Select statement in a transaction.

Isolation level

It controls the locking and row versioning behavior of Transact-SQL statements issued by a connection to SQL Server. There are five types of Isolation levels.

• READ UNCOMMITTED
  If any table is updated(insert or update or delete) under a transaction and the same transaction is not completed that is not committed or rolled back then uncommitted values will display (Dirty Read) in select query of “Read Uncommitted” isolation transaction sessions.
• READ COMMITTED
  In select query it takes only committed values of table. If any transaction is opened and incomplete on table in other sessions then select query will wait until no transactions are pending on same table.
• REPEATABLE READ
  Select query data of table that is used under transaction of isolation level. “Repeatable Read” cannot be modified from any other sessions until transaction is completed.
• SERIALIZABLE
  Serializable Isolation is similar to Repeatable Read Isolation but the difference is it prevents Phantom Read. This works based on range lock.
• SNAPSHOT
  Snapshot isolation is similar to Serializable isolation. The difference is Snapshot does not hold lock on table during the transaction so table can be modified in other sessions. Snapshot isolation maintains versioning in Tempdb for old data in case any data modification occurs in other sessions, then existing transaction displays the old data from Tempdb. It is highest level of Isolation but it affects performance.

Now we create StoredProcedure(SP) which implements transaction. The SP executes two inserted SQL statements: one for tblProject table another is tblProjectMember table. It keeps all SQL statements inside BEGIN TRANSACTION block and then commits. If any SQL fails then it goes to CATCH block and rollsback to previous state of database.

CREATE PROCEDURE spAddProject
 @ProjectID INT,
 @MemberID INT,
 @Name VARCHAR(10)
 AS
 BEGIN
 BEGIN TRY
 BEGIN TRANSACTION;
 -- Insert record into Project table
 INSERT INTO tblProject(ProjectID, Name) VALUES(1, 'TestProject');
 -- Insert record into ProjectMember table
 INSERT INTO tblProjectMember(MemberID, ProjectID) VALUES(2, 1);
 COMMIT TRANSACTION;
 END TRY
 BEGIN CATCH
 IF @@TRANCOUNT > 0
 ROLLBACK TRANSACTION;
 DECLARE @ErrorNumber INT = ERROR_NUMBER();
 DECLARE @ErrorLine INT = ERROR_LINE();
 DECLARE @ErrorMessage NVARCHAR(4000) = ERROR_MESSAGE();
 PRINT 'Actual error number: ' + CAST(@ErrorNumber AS VARCHAR(10));
 PRINT 'Actual line number: ' + CAST(@ErrorLine AS VARCHAR(10));
 RAISERROR(@ErrorMessage);
 END CATCH
 END;

@@TRANCOUNT:

Returns the number of BEGIN TRANSACTION statements that have occurred on the current transaction. The BEGIN TRANSACTION statement adds @@TRANCOUNT by 1. ROLLBACK TRANSACTION l @@TRANCOUNT to 0. COMMIT TRANSACTION or COMMIT WORK subtracts @@TRANCOUNT by 1.

call the Stored procedure from code like below.

@@TRANCOUNT:
 Returns the number of BEGIN TRANSACTION statements that have occurred on the current transaction. The BEGIN TRANSACTION statement adds @@TRANCOUNT by 1. ROLLBACK TRANSACTION l @@TRANCOUNT to 0. COMMIT TRANSACTION or COMMIT WORK subtracts @@TRANCOUNT by 1.

call the Stored procedure from code like below.