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 a 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 called 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 transactions in ADO.NET, EntityFramework, and SQLServer.

For demonstration, we create two tables (ProjectMember, Project). In the ProjectMember table, column ProjectID is a foreign key to the 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)    
);

Implement transactions In ADO.NET

Often we use ADO.NET to perform Database operations. Here is the sample code to implement transactions 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 a record to project, second, it inserts a record to projectMember table. First SQL statement executes properly but the second SQL statement throws an exception because we are inserting projectId as 2 which is not present in the 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 an object by calling the BeginTransaction() method of SqlConnection class. The 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 the TransactionScope class.

Using TransactionScope

It makes code block in transactional mode. It cannot be inherited. It is present in the 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 a 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. The 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 a new one.
RequiresNew	When this option is selected a new transaction is always created. This transaction is independent of 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 are 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 a TransactionScope class object and defines SQL queries to add records to the Project table, ProjectMember table. Once everything is fine, it calls Complete() to commit the data. If an exception occurs it rollsback to the 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.  

Implement transactions 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 transactions in earlier versions of EF. We can achieve it through TransactionScope class. In the below code sample, it creates a TransactionScope class object and defines the EF code for adding an object to the Project table, ProjectMember table. Once everything is fine, it calls Complete() to commit the data. If an exception occurs it rollsback to the 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 the 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 the above code, an exception will occur due to the wrong project ID of the ProjectMember object. Transaction object dbTran will rollback all operations that happened before. Now you won't get any record in the Project table as well ProjectMember table.

Database.UseTransaction() 

It allows the DbContext to use a transaction that 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 a constructor that will receive a 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 the below code snippet it creates a transaction object using the BeginTransaction() method. Inside the transaction object scope, it executes some SQL queries. Next, it creates EntityFramework and creates an object by passing two params, SqlConnection object and closing connection indicator. It calls UseTransaction() with passing transaction object which created before. Now entity-framework operations are appended to the 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

Implement transactions In SQL Server

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

Transaction Control

There are the following commands used to control transactions.

• COMMIT To save 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 the select query of "Read Uncommitted" isolation transaction sessions.
• READ COMMITTED
  In a select query, it takes only the committed values of the table. If any transaction is opened and incomplete on the table in other sessions then the select query will wait until no transactions are pending on the same table. 
• REPEATABLE READ
  Select query data of the table that is used under the transaction of isolation level. "Repeatable Read" cannot be modified from any other sessions until the transaction is completed. 
• SERIALIZABLE
  Serializable Isolation is similar to Repeatable Read Isolation but the difference is it prevents Phantom Read. This works based on the range lock.
• SNAPSHOT
  Snapshot isolation is similar to Serializable isolation. The difference is Snapshot does not hold a lock on the table during the transaction so the 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 the highest level of Isolation but it affects performance. 

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

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

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();    
      }    
   }    
} 

@@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 that 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 what is transaction and how we can use it in .Net, SQLServer. We also discussed transactions in ADO.NET, Entity Framework. Choose as per your requirement.

Hope this helps.