Transaction In .NET

Introduction

 

Most applications deal with back-end databases, and we need the data to be atomic, which means no anomalies in the data. For instance, you're supposed to insert data into master and child tables in the SQL Server. During the above task if you inserted one row into the master table and then some error occurred then what would happen to that task? Because of the error, child data is not inserted and you must rollback the master table data also; otherwise data inconsistency will happen. Here transaction plays a vital role to make sure data operations that occur happen successfully.

 

What is transaction?

 

A transaction is a single unit of work which means either ALL or NONE. If a transaction is successful, all of the data operations are committed and become a durable part of the database. If a transaction encounters errors/exceptions and must be canceled or rolled back, then all of the data modifications/operations need to be removed.

 

Example: Transfer money from Bank Account1 to Account2. It consists of two processes: Withdraw amount from Account1 and Deposit that amount to Account2.Transferring money will only be accurate and successful if both the processes are individually successful. If that is not happening, suppose process 1 succeeds but process 2 fails, then the money will be deducted from Account 1 but not deposited to Account 2. If that happens, it will be very bad and no one will accept it.

 

Properties of Transaction

 

Transaction has the following four standard properties, usually call by the acronym ACID.

• Atomicity
  Ensures that all operations within the work unit are completed successfully; If the transaction fails then operations are rolled back to their previous state.
  
  
• Consistency
  Ensures that the database properly changes states upon a successfully committed transaction.
  
  
• Isolation
  Enables transactions to operate independently of and transparent to each other.
  
  
• Durability
  Ensures that a committed transaction persists in case of a system failure.
  
  
  Figure 1: ACID properties 

Using Code

 

We will discuss how we can implement transaction in ADO.NET, EntityFramework and SQLServer.

 

For demonstration, we create two tables (ProjectMember, Project). In ProjectMember table, column ProjectID is a foreign key to Project table's ProejctID column.

-- Table1
CREATE TABLE tblProject
(
   ProjectID int PRIMARY KEY,
   Name varchar(50) NULL
);

--Table2
CREATE TABLE tblProjectMember
(
   MemberID int,
   ProjectID int foreign Key references Project(ProjectID)
);

In ADO.NET

 

Often we use ADO.NET to perform Database opertaions. Here is the sample code to implement transaction in ADO.NET.

string strConnString = "myconnectionstring"; // get it from Web.config file
SqlTransaction objTrans = null;

using (SqlConnection objConn = new SqlConnection(strConnString))
{
   objConn.Open();
   objTrans = objConn.BeginTransaction();
   SqlCommand objCmd1 = new SqlCommand("insert into tblProject values(1, 'TestProject')", objConn);
   SqlCommand objCmd2 = new SqlCommand("insert into tblProjectMember(MemberID, ProjectID) values(2, 1)", objConn);
   try
   {
      objCmd1.ExecuteNonQuery();
      objCmd2.ExecuteNonQuery(); // Throws exception due to foreign key constraint

      objTrans.Commit();
   }
   catch (Exception)
   {
      objTrans.Rollback();
   }
   finally
   {
      objConn.Close();
   }
}

In the above code sample, it executes two SQL queries: first it inserts record to project, second it inserts record to projectMember table. First SQL statement executes properly but second sql statement throws exception because we are inserting projectId as 2 which is not present in project table. Transaction dictates either complete success or failure, there is nothing in between. So to avoid the above problem, we will use Transaction to make everything perfect. Here it uses SqlTransaction class and creates object by calling BeginTransaction() method of SqlConnection class. Transaction starts here. If everything goes fine then commit (save into database) it otherwise rollback (delete inserted record in scope in the transaction) it.

 

We can achieve Transaction implementation using TransactionScope class.

 

Using TransactionScope

 

It makes code block in transactional mode. It cannot be inherited. It is present in namespace System.Transactions.TransactionScope

 

It has 3 main properties(IsolationLevel.

• Isolation Level
  It defines the locking mechanism to read data in another transaction. Available options are Read UnCommitted, Read Committed, Repeatable Read, Serializable. Default is Serializable.
  
  
• Timeout
  How much time transaction object will wait to be completed. SqlCommand Timeout is different than Transaction Timeout. SqlCommand Timeout defines how much time the SqlCommand object will wait for a database operation to be completed. Available options are 10 minutes. Default is 1 minute.
  
  
• TransactionScopeOption
  It is an enum. Following are the options. 

Option	Description
Disable	This component does not participate in a transaction. This is the default value.
NotSupported	This component runs outside the context of a transaction.
Required	It is the default value for TransactionScope. If any already exists then it will join with that transaction otherwise create new one.
RequiresNew	When this option is selected a new transaction is always created. This transaction is independent with its outer transaction.
Suppress	When this option is selected, no transaction will be created. Even if is already  there.

 

You can set default time out in web.config like.

<system.transactions>
  <defaultSettings timeout="30"/>
  <machineSettings maxTimeout="1200"/>
</system.transactions>

 System.Transactions is available by default; we need to add the reference. See Figure 2 for adding the reference.

 

Figure 2: Add Transaction assembly reference 

 

In the below code sample, it creates TransactionScope class object and defines SQL queries to add records to Project table, ProjectMember table. Once everything is fine, it calls Complete() to commit the data. If exception occurs it rollsback to previous state.

using (var txscope =new TransactionScope(TransactionScopeOption.RequiresNew))
{
   try
   {
      using (SqlConnection objConn = new SqlConnection(strConnString))
      {
            objConn.Open();
            SqlCommand objCmd1 = new SqlCommand("insert into tblProject values(1, 'TestProject')", objConn);
            SqlCommand objCmd2 = new SqlCommand("insert into tblProjectMember(MemberID, ProjectID) values(2, 1)", objConn);

            objCmd1.ExecuteNonQuery();
            objCmd2.ExecuteNonQuery(); // Throws exception due to foreign key constraint

            //The Transaction will be completed
            txscope.Complete();
      }
   }
   catch(Exception ex)
   {
      // Log error
      txscope.Dispose();
   }
}

What is the difference between TransactionScope and BeginTranaction()??

• TransactionScope is usually a better choice because it allows you to nest calls to other methods that might require a transaction without you having to pass the transaction state around.
• With TransactionScope, as long as the scope exists, it will handle everything that registers with the current Transaction on the thread, making your code cleaner, and more maintainable.
• TransactionScope uses MS DTC(Microsoft Distributed Transaction Coordinator) for transaction management.
• Due to its ease of use and efficiency, it is recommended that you use the TransactionScope class when developing a transaction application.  

In EntityFramework

 

In  EntityFramework(EF) we have different versions, so EF 6.0 provides two APIs which are not available in previous versions. So we will discuss how can we implement transaction in earlier versions of EF. We can achieve it through TransactionScope class. In below code sample, it creates TransactionScope class object and defines EF code for adding object to Project table, ProjectMember table. Once everything is fine, it calls Complete() to commit the data. If exception occurs it rollsback to previous state.

using (TransactionScope scope = new TransactionScope(TransactionScopeOption.RequiresNew))
{
    try
    {
        // In the Data Access Layer
        using (DBContextEntitis entities = new DBContextEntitis(entitiesConnectionstring))
        {
            Project proj = new Project() { ProjectID = 1, Name = "Temp Project" };
            context.Project.Add(proj);

            ProjectMember projMember = new ProjectMember() { ProjectID = 2, MemberID = 1 };
            context.ProjectMembers.Add(projMember);

            //The Transaction will be completed
            scope.Complete();
        }
    }
    catch(Exception ex)
    {
        scope.Dispose();
    }
}

Transaction in EntityFramework 6.0

 

By default EntityFramework Inserts, Updates or Deletes operations in a transaction, when you call SaveChanges() method of context object. EF starts a new transaction for each operation and completes the transaction when the operation finishes. EntityFramework 6.0 provides two new methods.

 

Database.BeginTransaction()

 

It facilitates starting and completing transactions themselves within an existing DbContext object. It allows several operations to be combined within the same transaction and hence either all are committed or all are rolled back as one. You can specify more easily the isolation level for the transaction. 

using (var context = new MyDBContext())
{
   using (System.Data.Entity.DbContextTransaction dbTran = context.Database.BeginTransaction( ))
   {
      try
      {
         Project proj = new Project() { ProjectID = 1, Name = "Temp Project" };
         context.Project.Add(proj);

         ProjectMember projMember = new ProjectMember() { ProjectID = 2, MemberID = 1 };
         context.ProjectMembers.Add(projMember);

         //saves all above operations within one transaction
         context.SaveChanges();

         //commit transaction
         dbTran.Commit();
      }
      catch (Exception ex)
      {
         //Rollback transaction if exception occurs
         dbTran.Rollback();
      }
   }
}

In above code, exception will occur due to wrong project ID of ProjectMember object. Transaction object dbTran will rollback all operations that  happened before. Now you wont get any record in Project table as well ProjectMember table.

 

Database.UseTransaction() 

It allows the DbContext to use a transaction which was started outside of the Entity Framework.

using (TransactionScope scope = new TransactionScope(TransactionScopeOption.RequiresNew))
{
   try
   {
      // In the Data Access Layer
      using (DBContextEntitis entities = new DBContextEntitis(entitiesConnectionstring))
      {
         Project proj = new Project() { ProjectID = 1, Name = "Temp Project" };
         context.Project.Add(proj);
         ProjectMember projMember = new ProjectMember() { ProjectID = 2, MemberID = 1 };
         context.ProjectMembers.Add(projMember);
         //The Transaction will be completed
         scope.Complete();
      }
   }
   catch(Exception ex)
   {
      scope.Dispose();
   }
}

To implement UseTransaction, you need to add constructor which will receive Database connection and closing connection indicator. The contextOwnConnection informs the Entity Framework to not close the connection when it is done.

public class DBEntitiesContext : DbContext
{
   public EntitiesContext() : base("name=Entities")
   {
      Database.Log = Console.WriteLine;
   }

   public EntitiesContext(DbConnection existingConnection, bool contextOwnConnection)
   : base(existingDBConnection, contextOwnConnection)
   {
   }
}

In below code snippet it creates transaction object using BeginTransaction() method. Inside transaction object scope, it executes some SQL queries. Next it creates EntityFramework and creates object by passing two params, SqlConnection object and closing connection indicator. It calls UseTransaction() with passing transaction object which created before. Now entityframework operations are appended to previous transaction scope.

using(SqlConnection con = new SqlConnection("connectionString"))
{
   con.Open();

   using(var transaction = con.BeginTransaction())
   {
      SqlCommand objCmd1 = new SqlCommand("insert into tblProject values(1, 'TestProject')", objConn);
      objCmd1.ExecuteNonQuery();

      //Pass this transaction to EF....
      using (DBEntitiesContext context = new DBEntitiesContext(con, false))
      {
         context.Database.UseTransaction(transaction);
         ProjectMember projMember = new ProjectMember() { ProjectID = 2, MemberID = 1 };
         context.ProjectMembers.Add(projMember);

         context.SaveChanges();
      }
   }
}

Default transaction level in EF6 has changed to READ COMMITTED SNAPSHOT

 

In SQL Server

 

We implemented transaction in front-end code(C# code) but we can also define transaction in back-end code like SQLServer database.

 

Transaction Control:

 

There are the following 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. 

In SQLServer it is READ COMMITTED. For more on isolation level, visit here.

 

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.

 

Let's call the Stored procedure from code like below.

using (SqlConnection con = new SqlConnection(connectionString))
{
   using (SqlCommand cmd = new SqlCommand("spAddProject", con))
   {
      cmd.CommandType = CommandType.StoredProcedure;
      cmd.Parameters.AddWithValue("@ProjectID", 1);
      cmd.Parameters.AddWithValue("@MemberID", 1);
      cmd.Parameters.AddWithValue("@Name", "Test Proejct");

      con.Open();
      cmd.ExecuteNonQuery();
   }
}

Distributed Transactions

 

A transaction which works with multiple data sources is called a distributed transaction. If a transaction fails then the affected data sources will be rolled back. In System.Transactions, MSDTC (Microsoft Distributed Transaction Coordinator) manages distributed transactions. It implements a two phase commit protocol. A distributed transaction is much slower than a local transaction. The transaction object automatically escalates a local transaction to a distributed transaction when it understands that a distributed transaction is needed. The developer cannot do anything here.

 

Conclusion

 

We discussed about what is transaction and how we can use in .Net, SQLServer. We also discussed transaction in ADO.NET, EntityFramework. Choose as per your requirement.

 

Hope this helps.