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

• Download demo - 281.7 KB

Introduction

This article is a beginner's tutorial for understanding what are transactions and how can transactions be implemented using .Net framework and ADO.NET for 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 (SQL statement 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:

The UI will look like: 

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 SQL transactions, how to implement transactions using ADO.NET transaction object and finally we will see how to implement transactions using TransactionScope object.

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 SQL injection. 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 SQL level. The SQL construct for handling the transactions is like follows:

BEGIN TRY
    BEGIN TRANSACTION
        -- update first account and debit from it
        -- update second account and credit in it
    COMMIT TRANSACTION
END TRY
BEGIN CATCH
    ROLLBACK TRANSACTION
END CATCH

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 SQL transactions in place.

private void PerformTransaction(string creditAccountID, string debitAccountID, double amount)
{        
    SqlConnection con = null;

    // they will be used to decide whether to commit or rollback the transaction
    bool result = false;
   
    string updateCommand = @"BEGIN TRY
                                BEGIN TRANSACTION
                                    update Account set Amount = Amount - {0} where ID = {1}
                                    update Account set Amount = Amount + {2} where ID = {3}
                                COMMIT TRANSACTION
                            END TRY
                            BEGIN CATCH
                                ROLLBACK TRANSACTION
                            END CATCH";

    try
    {
        con = new SqlConnection(CONNECTION_STRING);
        con.Open();            

        // Let us do a debit first
        using (SqlCommand cmd = con.CreateCommand())
        {
            cmd.CommandType = CommandType.Text;
            cmd.CommandText = string.Format(updateCommand,                    
                amount, debitAccountID,
                amount, creditAccountID);

            // check if 2 records are effected or not
            result = cmd.ExecuteNonQuery() == 2;
        }
    }
    catch
    {
        // do application specific cleanup or show message to user about the problem
    }
    finally
    {
        con.Close();
    }
}

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 SQL level 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 DbTransaction object. 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.

private void PerformTransaction(string creditAccountID, string debitAccountID, double amount)
{
    SqlTransaction transaction = null;
    SqlConnection con = null;

    // they will be used to decide whether to commit or rollback the transaction
    bool debitResult = false;
    bool creditResult = false;

    try
    {
        con = new SqlConnection(CONNECTION_STRING);
        con.Open();

        // lets begin a transaction here
        transaction = con.BeginTransaction();

        // Let us do a debit first
        using (SqlCommand cmd = con.CreateCommand())
        {
            cmd.CommandType = CommandType.Text;
            cmd.CommandText = string.Format(
                "update Account set Amount = Amount - {0} where ID = {1}",
                amount, debitAccountID);

            // assosiate this command with transaction
            cmd.Transaction = transaction;

            debitResult = cmd.ExecuteNonQuery() == 1;
        }

        // A dummy throw just to check whether the transaction are working or not
        //throw new Exception("Let see..."); // uncomment this line to see the transaction in action

        // And now do a credit
        using (SqlCommand cmd = con.CreateCommand())
        {
            cmd.CommandType = CommandType.Text;
            cmd.CommandText = string.Format(
                "update Account set Amount = Amount + {0} where ID = {1}",
                amount, creditAccountID);

            // assosiate this command with transaction
            cmd.Transaction = transaction;

            creditResult = cmd.ExecuteNonQuery() == 1;
        }

        if (debitResult && creditResult)
        {
            transaction.Commit();
        }
    }
    catch
    {
        transaction.Rollback();            
    }
    finally
    {
        con.Close();
    }
}

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 TransactionScope object. TransactionScope if 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 TransactionScope object to handle the transactions following needs to be done.

1. Create a TransactionScope object within a using block.
2. Create the connection inside this TransactionScope using 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 TransactionScope object.
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 TransactionScope object.

private void PerformTransaction(string creditAccountID, string debitAccountID, double amount)
{
    // they will be used to decide whether to commit or rollback the transaction
    bool debitResult = false;
    bool creditResult = false;

    try
    {
        using (TransactionScope ts = new TransactionScope())
        {
            using (SqlConnection con = new SqlConnection(CONNECTION_STRING))
            {
                con.Open();

                // Let us do a debit first
                using (SqlCommand cmd = con.CreateCommand())
                {
                    cmd.CommandType = CommandType.Text;
                    cmd.CommandText = string.Format(
                        "update Account set Amount = Amount - {0} where ID = {1}",
                        amount, debitAccountID);

                    debitResult = cmd.ExecuteNonQuery() == 1;
                }

                // A dummy throw just to check whether the transaction are working or not
                throw new Exception("Let see..."); // uncomment this line to see the transaction in action

                // And now do a credit
                using (SqlCommand cmd = con.CreateCommand())
                {
                    cmd.CommandType = CommandType.Text;
                    cmd.CommandText = string.Format(
                        "update Account set Amount = Amount + {0} where ID = {1}",
                        amount, creditAccountID);

                    creditResult = cmd.ExecuteNonQuery() == 1;
                }                                       

                if (debitResult && creditResult)
                {
                    // To commit the transaction 
                    ts.Complete();
                }
            }
        }
    }
    catch
    {
        // the transaction scope will take care of rolling back
    }  
}

This code block makes sure that either all the operations within the scope of

TransactionScope

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 TransactionScope is already instantiated, this

  TransactionScope

  object will join that one.
• RequiresNew: Even if within current scope another TransactionScope is already instantiated, this

  TransactionScope

  object will create a new transaction that will work within its scope.
• Supress: Even if within current scope another TransactionScope is already instantiated, this

  TransactionScope

  object now keep all the operations within its scope out of the existing transaction.

These options can be passed in the Constructor of the TransactionScope as: 

using (TransactionScope ts = new TransactionScope(TransactionScopeOption.Required))
{
    // Code goes here
}

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 SqlServer and 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 TransactionScope class, if the transaction starts with one process LTM will keep it going but if another operation in some other process is done the DTC takes over and the transaction will then be automatically promoted to distributed transaction. So TransactionScope class can be used to create promotable transactions with ease.

But even for non distributed transactions, transactionScope object should only be used with SqlServer 2005 or later products because the earlier SqlServer products were not aware of this LTM and DTC. so for the earlier products ADO.NET DbTransaction object 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. 

History

• 05 January 2013: First version.