Implementing Transactions

A transaction can be defined as a sequence of operations. If a transaction is successful, all the data modifications performed in the database will be committed and saved. If it is not complete transaction will be rolled back to undo the data modifications done in the database.

Properties of a transaction

The following are the ACID properties that a transaction must possess:

· Atomicity

This property states that either all the modifications are performed or none of them are performed.

· Consistency

After the transaction completed data should be in consistent state so that integrity of database is maintained.

· Isolation

Any data modification made by one transaction must be isolated from the modifications made by the other transaction.

· Durability

Any change in data by a completed transaction remains permanently in effect in the database.

Implementing Transactions local Transactions

A local transaction is performed on a single data source. Local transaction is created and managed within the System.Transactions namespace. These are easy efficient to operate and easy to manage.

Performing local transactions

Transactions are implemented in .NET applications when multiple tasks are combined to execute a single unit of work. A transaction in ADO.NET is considered to be a local transaction when it is managed by a single data source. ADO.NET has an interface, IDbTransaction that contains methods for creating and performing local transactions against a single data source.

Transaction classes in .NET Framework

Transaction Class	Description
System.Data.SqlClient.SqlTransaction	For SQL Server
System.Data.OleDbClient.OleDbTransaction	For OLE DB
System.Data.Odbc.OdbcTransaction	For ODBC
System.Data.OracleClient.OracleTransaction	For Oracle

To perform a local transaction BeginTransaction() method on an instance of a transaction class that implements the IDbTransaction interface is called.

After calling the BeginTransaction() method, we need to execute a command within a transaction, set the connection property of the command to a connection running the appropriate transaction, and then execute the command. If all commands are succeed, the commit() method is called on the transaction object to commit the transaction. If any command fails, the Rollback() method is called on the transaction object to rollback the transaction.

Managing Distributed Transaction

When a connection to a database is opened and a transaction has been performed within that connection and another transaction on the same connection has to be performed it will throw an exception. This problem can be resolved using distributed transactions strategy in ADO.NET.

Distributed transactions are created in the System.Transaction namespace. System.Transaction namespace has a TransactionScope class, which enables a developer to create and manage distributed transactions.

To create a distributed transaction, a TransactionScope object is created in a using block. The TransactionScope object is used to open multiple data source connections within the same transaction. The TransactionScope object decides whether to create a local transaction or a distributed transaction. This is known as Transaction Promotion.

In transaction promotion, when the transaction scope is created, by default it creates a local transaction within a single connection. When more than one connection is established within the same transaction scope, the local transaction is automatically promoted to a distributed transaction.

In a distributed transaction, when a connection to the database is created, the TransactionScope object assigns a transaction to this connection. If an exception is thrown within the TransactionScope object, the transaction is rolled back. If no exception is found, the complete() method is called that commits the transaction.

Operations in Distributed Transactions

· Perform bulk copy operations

· Specify the Isolation levels

Performing bulk copy operations

Bulk copy operations can be performed as an isolated operation or as a part of a transaction. It has its own transaction. To perform bulk copy operation, we need to create a new instance of BulkCopy class with a connection string. The bulk copy operation creates, and then commits or rolls back the transaction.

Specify Isolation levels of a transaction

An isolation level determines the locking strategy used by the connection running the transaction. It is important to choose an appropriate isolation level to prevent concurrency problems when multiple transactions access the same data.

Concurrency errors

Concurrency error	Description
Dirty read	A transaction reads the data that has not been committed by the other transaction. This can create problem if a transaction that has added the data is rolled back.
Nonrepeatable read	A transaction reads the same row more than once and a different transaction modifies the row between the reads
Phantom read	A transaction reads a rowset more than once and a different transaction inserts or deletes rows between the first transaction’s reads.

The System.Transaction namespace defines TransactionOptions class, which specifies the transaction behaviour, such as an isolation level. The IDbTransaction interface defines an IsolationLevel property that specifies the isolation level for a transaction. The System.Transactions namespace also defines an enumeration type named IsolationLevel, which selects an isolation level when the TransactionScope object is initialized to manage the transaction.

Isolation levels

· Read Uncommitted

In this type of isolation level queries running inside one transaction are affected by the committed changes in another transaction. No locks are required, and no locks are considered when data is read. Dirty read, phantom read and non-repeatable reads can occur within this isolation level.

· Read Committed with Locks

In this type of isolation level committed updates in one transaction are visible within another transaction. This isolation level prevents dirty read only. Phantom and non-repeatable read problems are still there.

· Read Committed with snapshots

In this type of isolation level committed updates are visible within other transactions. No locks are required and any modifications to the rows are tracked by maintaining versions of the row updates.

· Repeatable Read

In this isolation level a transaction locks all rows that it reads in a query, preventing other transactions from updating data until the first transaction terminates. This isolation level prevents dirty read and non repeatable read, phantom reads can occur.

· Snapshot

This isolation level is used when accuracy is required on long running queries and multi statement transactions but no updates are performed on the data. No read locks are required to prevent modifications by other transactions because the changes will not be visible until the snapshot completes and the transaction commits data modification.

· Serializable

In this isolation level transactions are completely isolated from each other. This isolation level is frequently implemented by locking large numbers of rows or entire tables. This isolation level prevents dirty read, non-repeatable reads and phantom reads.

The following table shows various isolation levels and the corresponding concurrency errors for a transaction.

Isolation level	Dirty read	Nonrepeatable read	Phantom Read
Read Uncommitted	Yes	Yes	Yes
Read Committed with locks	No	Yes	Yes
Read committed with snapshots	No	Yes	Yes
Repeatable read	No	No	Yes
Snapshot	No	No	No
Serializable	No	No	No

Code to explain Isolation levels:

using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.data.SqlClient;
using System.Transactions;
namespace Isolation_Levels
{
    Class Program
    {
    static void Main(string[] args)
    {
    TransactionOptions options=new TransactionOptions();
    Options.IsolationLevel=System.Transactions.IsolationLevel.ReadCommitted;
    using (TransactionScope ts=new TransactionScope())
    {
using(SqlConnection cn=new SqlConnection(“Initial Catalog=HR;Data Source=SQLSERVER01;User id=sa;Password=password#1234;”))
{
cn.Open();
using(SqlCommand cmd=new SqlConnection(“INSERT INTO Product(product_code,product_name,price,quantity) VALUES (“P-0123”,”Toys”,250,50)”,cn))
{
    int rowsUpdated=cmd.ExecuteNonQuery();
    if(rowsUpdated>0)
    {
using(SqlConnection cn1=new SqlConnection(“Initial Catalog=HR;Data Source=SQLSERVER01;User id=sa;Password=password#1234”))
{
cn1.Open();
using (SqlCommand cmd1=new SqlCommand(“INSERT INTO Order(OrderID,OrderDate,ClientID) VALUES(‘O-909’,’12-09-2013’,’C-787’)”,cn1))
{
    int rowUpdated1=cmd1.ExecuteNonQuery();
    if(rowsUpdated>0)
    {
    Ts.Complete();
    Console.WriteLine(“Transaction Committed\n”);
    cn1.Close();
    }
}
}        
    }
}
cn.Close();
}
    }
    Console.ReadLine();
    }
    }
}