Transaction in Sqlserver

A Database is a software system that defines a collection of predefined operations. Mainly it includes following operations

1.Efficient management of large amount of persistent data in a persistent storage (database)
2.Transaction Management which includes Concurrency Control, Atomicity and backup recovery procedure
3.A DataModel which gives a separate level of abstraction
Transaction



In .NET environment we can define transaction boundary by Transaction object.

1.If you are using SqlClient (namespace System.Data.SqlClient) Managed Provider you can SqlTransaction object.
2.If you are using Oledb (namespace System.Data.Oledb) Managed Provider you can OledbTransaction object.
3.If you are using Odbc (namespace Microsoft.Data.Odbc) Managed Provider you can OdbcTransaction object

A transaction must follows this properties:-

In a perfect transaction world, a transaction must contain a series of properties known as ACID. These properties are:

Atomicity
A transaction is an atomic unit of work or collection of separate operations. So, a transaction succeeds and is committed to the database only when all the separate operations succeed. On the other hand, if any single operations fail during the transaction, everything will be considered as failed and must be rolled back if it is already taken place. Thus, Atomicity helps to avoid data inconsistencies in database by eliminating the chance of processing a part of operations only.

Consistency
A transaction must leave the database into a consistent state whether or not it is completed successfully. The data modified by the transaction must comply with all the constraints in order to maintain integrity.

Isolation
Every transaction has a well defined boundary. One transaction will never affect another transaction running at the same time. Data modifications made by one transaction must be isolated from the data modification made by all other transactions. A transaction sees data in the state as it was before the second transaction modification takes place or in the state as the second transaction completed, but under any circumstance a transaction can not be in any intermediate state.

Durability
If a transaction succeeds, the updates are stored in permanent media even if the database crashes immediately after the application performs a commit operation. Transaction logs are maintained so that the database can be restored to its original position before failure takes place.

Let us discuss a simple block of transaction control. In this block I am taking SqlClient Managed Provider

string connectionString = ".........";

SqlConnection myConnection = new SqlConnection(connectionString);

myConnection.Open();

// Start transaction.

SqlTransaction myTransaction = myConnection.BeginTransaction();

// Assign command in the current transaction.

SqlCommand myCommand = new SqlCommand();

myCommand.Transaction = myTransaction;



try

{

.........................Database operations........................

myTransaction.Commit();

Console.WriteLine("Records are modified in the database."); 

}



catch(Exception e)

{

myTransaction.Rollback();

Console.WriteLine(e.ToString());

Console.WriteLine("Neither record was written to database.");

}



finally

{ 

myConnection.Close();

} 

In Above Block BeginTransaction method of the Connection object to mark the start of the transaction, which returns a Transaction object.

The newly created transaction object is assigned to CommandObject so that what ever the database operation is performed by that commandObject can be managed by Transaction Object.

If anything gets wrong the Transaction object will raise an Exception otherwise it will run through a normal process. Call the Commit method of the Transaction object to complete the transaction if everything works fine otherwise call the Rollback method to cancel the transaction.

Concurrency Control


While doing certain modification in the database some time you need to lock the data so that no one can else perform modification in that data. There are two commonly known approaches for locking database they are optimistic locking and pessimistic locking.

Both these approaches are used to maintain concurrency in the database. Pessimistic concurrency locking is done at rows of the data source to prevent users from modifying data in a way that affects other users. In a pessimistic model, when a user performs an action that causes a lock to be applied, no one else can perform action until unless owner releases that lock. But this is not case with optimistic currency model. In optimistic concurrency model user does not lock row while reading it, while user only locks the row while updating changes to the database.

In .NET we use DataSet object for modifying changes in the database. The DataSet object uses optimistic concurrency model with the help of DataAdaptor. The DataSet object is designed to encourage the use of optimistic concurrency for long-running activities such as when you are working in distributed environment.

In real time execution DataSet maintains the versions of data that means if anyone modify any data in the DataSet then it get maintain in the dataset as old version and new version. While updating modified data in the database if any of the concurrency conflict occur it raises Exception, which sets DataRow's HasError Boolean value. This we can easily handle with DataAdaptor event and with our own programming logic.

Here I am giving a simple code sample, which explains you how can you manage, concurrency control in .NET environment

string connectionString = ".......................";

SqlConnection myConnection = new SqlConnection(connectionString);

SqlDataAdapter myAdaptor = new SqlDataAdapter("SELECT Name, City FROM Employee ORDER BY EmpID", myConnection);

// Add the RowUpdated event handler.

myAdaptor.RowUpdated += new SqlRowUpdatedEventHandler(OnRowUpdated);

DataSet supplierData = new DataSet();

myAdaptor.Fill(supplierData, "Supplier");

// Modify the DataSet contents.

..........................................

.........................................

myAdaptor.Update(supplierData, "Supplier");

foreach (DataRow myRow in supplierData.Tables["Supplier"].Rows)

{

if (myRow.HasErrors)

Console.WriteLine(myRow[0] + "\n" + myRow.RowError);

}

protected static void OnRowUpdated(object sender, SqlRowUpdatedEventArgs args)

{

if (args.RecordsAffected == 0) 

{

args.Row.RowError = "Optimistic Concurrency Violation Encountered";

args.Status = UpdateStatus.SkipCurrentRow;

}

} 

In this code you have SqlDataAdaptor, which is retrieving supplier record from a database and filling it in a DataSet supplierData. After that you have performed certain modification in that data via DataSet. After modifying data we have used dataAdaptor to update that changes in the database.

So what is new in this code? You might have noticed that in this code we have defined a event handler on dataAdaptor's RowUpdated event. This event will be fired when row is updated in the database and in that event handler mechanism we can define different status to argument so that further action can be take place.

In the main code I have specified code to write all those rows in which error has occurred during modification.

There are different type of status is available for SqlRowUpdatedEventArgs by which you can direct the updating process. Those status are as follows:

Status - Description
•Continue - Continue the update operation.
•ErrorsOccurred - Abort the update operation and throw an exception.
•SkipCurrentRow - Ignore the current row and continue the update operation.
•SkipAllRemainingRows - Abort the update operation but do not throw an exception.