A Beginner’s Tutorial for Understanding Transactions and TransactionScope in ADO.NET

This article is a beginner’s tutorial for understanding what are transactions and how can transactions be implemented using .Net framework and ADO.NETfor any ASP.NET web application or any other database driven application.

Background

Transaction word, when used in normal context mean exchange of goods, products or money in return for something of equal value. What makes this exchange a transaction is the fact that the total amount of goods or money remains the same i.e. it doesn’t get increased or decreased and the reliability i.e. if one party is giving something then other party will receive that much quantity (no less, no more).

Following the same lines, when we talk about transactions in database operations, When we perform some database operations in such a way that either all the database operations are successful or all of them fail. This would result in the amount information being same once the transaction is complete or it fails.

To illustrate the above process, let say I have two account holders, one person is trying to transfer some money to other person. From the database perspective this operation consist of two sub-operations i.e.

  1. Debiting the first account by specified amount.
  2. Secondly, crediting the second account with required amount.

Now from a technical perspective, if the first operation is successful but second one fails the result would be that the first persons account will be debited but second one will not be credited i.e. we loose the amount of information. The other way round will in fact increase the amount ion second account without even debiting the first amount.

So the bottom-line here is that we need either both of them to be successful to both of them should fail. Success of any one operation will result in inconsistent results and thus even if one operation fails we need to rollback what we did in the other operation. This is precisely where transaction are useful.

Let us now look at some technical details associated with transactions and transactions in .Net framework. We will then see how we can implement transactions in .NET.

Using the code

By definition a transaction must be Atomic, Consistent, Isolated and Durable. What does we mean by all these terms

Properties of Transaction

  • Atomic: Atomic means that all the statements ( SQLstatement or operations) that are a part of the transaction should work as atomic operation i.e. either all are successful or all should fail.
  • Consistent: This means that in case the atomic transaction success, the database should be in a state that reflect changes. If the transaction fails then database should be exactly like it was when the transaction started.
  • Isolated: If more than one transactions are in process then each of these transactions should work independently and should not effect the other transactions.
  • Durable: Durability means that once the transaction is committed, the changes should be permanent i.e. these changes will get saved in database and should persist no matter what(like power failure or something).

Description of Sample code

Now to illustrate how can transactions be implemented, We will work on a small application that contains a single table database. This table contains the account id and the amount present in the account. The application will facilitate transfer of amount of one account to another. Since there are two operations involved we will see how we can use transactions to perform these operations.

The sample DB table looks like:

table

The UI will look like:

screenshot

And we will implement three versions of this page to see three different ways. We will see how can handle transaction at database level using SQLtransactions, how to implement transactions using ADO.NET transaction object and finally we will see how to implement transactions using TransactionScopeobject.

Note: The code is written to elaborate the transaction functionality only, it is not as per the coding standards i.e it is vulnerable to SQLinjection. It should not be taken as code that could go in production. It is just the sample code and has a lot of scope for improvement.

Creating and Implementing Transactions in SQL

The transactions can also be handled at SQLlevel. The SQLconstruct for handling the transactions is like follows:

So if something goes wrong, the SQL itself will take care of rolling back the transaction.

So for our sample application we can write the following code to get this code to work with SQLtransactions in place.

Note: This sample code just shows the code snippet handling the transaction and database operation, to get the full understanding please refer to the sample code.

Creating and Implementing Transactions using ADO.NET DbTransaction Object

Now having the transactions in SQLlevel is perfect solution if all the operations are being done in one place. I can create a stored procedure which will take care of all the transactions  But what if all the operations are taking place in separate classes or even separate assemblies. If that is the case need to use the ADO.NET transaction to handle transactions in code.

The other way to handle the transaction is from the code itself using ADO.NET DbTransactionobject. To do that following steps should be taken:

  1. Create the Connection.
  2. Create the Transaction.
  3. Create the commands for all the operations that are within transaction.
  4. Open the connection.
  5. Begin Transaction.
  6. Associate all the commands with the above transaction object.
  7. Execute commands.
  8. Check for the commands status separately.
  9. If any of the command fails, Roll back the transaction.
  10. If all Commands are successful, Commit the transaction.

To illustrate the above mentioned process, lets write some code to perform the transaction in our sample application.

This code block makes sure that either all the operations associated with the transactions succeed or none of them will.

Note: This sample code just shows the code snippet handling the transaction and database operation, to get the full understanding please refer to the sample code.

Creating and Implementing Transactions using TransactionScope Object

Creating and using transactions using ADO.NET transaction is fine as long as we do either a commit or rollback on the transaction object. If we forget to so do this and leave the code then it will cause some problems.

To solve such problems there is one more way of handling the transaction i.e. using TransactionScopeobject. TransactionScopeif more of a syntactic sugar when dealing with transactions. Also it makes sure that if the transaction is not committed and the code goes out of scope, the transaction will get rolled back.

To use the TransactionScopeobject to handle the transactions following needs to be done.

  1. Create a TransactionScopeobject within a using block.
  2. Create the connection inside this TransactionScopeusing block.
  3. Create all the commands inside this.
  4. Perform all the operations using Commands.
  5. If all the operations are successful call the Complete function on TransactionScopeobject.
  6. If any of the command fail, let the control go out of scope and the transaction will be rolled back.

To illustrate this process let us try to re-implement the functionality in our sample application using TransactionScopeobject.

This code block makes sure that either all the operations within the scope of TransactionScopewill succeed or none of them will.

Note: This sample code just shows the code snippet handling the transaction and database operation, to get the full understanding please refer to the sample code.

To give more control to the developer, along with the scoped control of TrasanctionScope, there are few options that can be associated with the TransactionScope

  • Required: If within current scope another TransactionScopeis already instantiated, this TransactionScopeobject will join that one.
  • RequiresNew: Even if within current scope another TransactionScope is already instantiated, this TransactionScopeobject will create a new transaction that will work within its scope.
  • Supress: Even if within current scope another TransactionScopeis already instantiated, this TransactionScopeobject now keep all the operations within its scope out of the existing transaction.

These options can be passed in the Constructor of the TransactionScopeas:

A Note on Distributed Transactions

If a transaction span across multiple processes then it is a Distributed transaction i.e. if I need to perform an operation in SqlServerand other in Oracle ans I have a transaction associated with it then it will be a distributed transaction.

The reason this is important is because of two things. The Isolation property of transaction will be ensured by the LTM(Lightweight Transaction Manager) if it is not a distributed transaction. But of it is a distributed transaction then Distributed Transaction Controller( DTC) will take over the control.

When we use TransactionScopeclass, if the transaction starts with one process LTMwill keep it going but if another operation in some other process is done the DTCtakes over and the transaction will then be automatically promoted to distributed transaction. So TransactionScopeclass can be used to create promotable transactions with ease.

But even for non distributed transactions, transactionScope object should only be used with SqlServer 2005or later products because the earlier SqlServerproducts were not aware of this LTMand DTC. so for the earlier products ADO.NET DbTransactionobject is the perfect way to handle the transactions from code.

Point of interest

In this article we tried to see what are transactions and how can transactions be handled in .NET. We looked at these concepts from a very beginner’s perspective and from the topics perspective, it is just the tip of the iceberg. We have not discussed anything about the isolation levels and the note on distributed transactions was very basic(since it itself is a vast topic). But we did discuss about various ways of handling transactions in .NET.

Download sample code for this article: TransactionScopeDemo

One thought on “A Beginner’s Tutorial for Understanding Transactions and TransactionScope in ADO.NET

  1. […] Now having transactions from a simple class library’s perspective ot from a simple .Net applications, it is just a matter of calling the operations within a transaction, checking whether all the operations are successful or not and deciding whether to commit or rollback the transaction. Refer this article to know about transactions in normal application: A Beginner’s Tutorial for Understanding Transactions and TransactionScope in ADO.NET[^] […]