Skip to main content
transactions-in-SQL Server

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.

 

Sneha Singh

Sneha Singh

Sneha Singh works with Trigent Software as Sr. Software Engineer. She has around 5.6 yrs of experience in .Net and has been working on Web-based applications. In her leisure time, Sneha likes reading fiction and listening to music..

One thought on “Transactions in SQL Server (Part – II)

  1. Heya i’m for the first time here. I found this board and I discover It truly helpful & it helped me out significantly. I hope to present 1 thing once again and help other people like you aided me.

Comments are closed.