Post
Ask Question

Transactions and Concurrency Control using ADO.NET

A Database is a software system that defines a collection of predefined operations. Mainly it includes the following operations
  1. Efficient management of a large amount of persistent data in 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
In this article, I am concentrating on transaction management that is Concurrency Control in a .NET environment.
 

Transaction

 
A transaction is an abstract unit of concurrent computation that execute automatically. The effect of the transaction does not interfere with other transactions that access the same data. Also, a transaction happens with all of its effects (In this case you will commit the changes) or it doesn't happen with any of its effects (In this case you will rollback the changes).
 
In transaction control, we generally define code in between a block where we perform the mission-critical operation. If all operations get completed successfully then that part is committed in the database otherwise whatever modification you might have done during the process is rollbacked from the database so that it never affects other user's operations.
 
In a .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
Let us discuss a simple block of transaction control. In this block, I am taking SqlClient Managed Provider
  1. string connectionString = ".........";  
  2. SqlConnection myConnection = new SqlConnection(connectionString);  
  3. myConnection.Open();  
  4. // Start transaction.  
  5. SqlTransaction myTransaction = myConnection.BeginTransaction();  
  6. // Assign command in the current transaction.  
  7. SqlCommand myCommand = new SqlCommand();  
  8. myCommand.Transaction = myTransaction;  
  9. try {  
  10.      .........................  
  11.      Database operations  
  12.      ........................  
  13.      myTransaction.Commit();  
  14.      Console.WriteLine("Records are modified in the database.");  
  15. } catch (Exception e) {  
  16.      myTransaction.Rollback();  
  17.      Console.WriteLine(e.ToString());  
  18.      Console.WriteLine("Neither record was written to database.");  
  19. } finally {  
  20.      myConnection.Close();  
  21. } 
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 whatever 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 databases 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 an action until unless the owner releases that lock. But this is not the case with the optimistic currency model. In the optimistic concurrency model, the user does not lock the row while reading it, while the 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 an 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 a distributed environment.
 
In real-time execution, DataSet maintains the versions of data that means if anyone modifies any data in the DataSet then it get maintain in the dataset as the old version and new version. While updating modified data in the database if any of the concurrency conflicts 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
  1. string connectionString = ".......................";  
  2. SqlConnection myConnection = new SqlConnection(connectionString);  
  3. SqlDataAdapter myAdaptor = new SqlDataAdapter("SELECT Name, City FROM Employee ORDER BY EmpID", myConnection);  
  4. // Add the RowUpdated event handler.  
  5. myAdaptor.RowUpdated += new SqlRowUpdatedEventHandler(OnRowUpdated);  
  6. DataSet supplierData = new DataSet();  
  7. myAdaptor.Fill(supplierData, "Supplier");  
  8. // Modify the DataSet contents.  
  9. ..........................................  
  10. .........................................  
  11. myAdaptor.Update(supplierData, "Supplier");  
  12. foreach(DataRow myRow in supplierData.Tables["Supplier"].Rows) {  
  13.      if (myRow.HasErrors)  
  14.           Console.WriteLine(myRow[0] + "\n" + myRow.RowError);  
  15. }  
  16. protected static void OnRowUpdated(object sender, SqlRowUpdatedEventArgs args) {  
  17.      if (args.RecordsAffected == 0) {  
  18.           args.Row.RowError = "Optimistic Concurrency Violation Encountered";  
  19.           args.Status = UpdateStatus.SkipCurrentRow;  
  20.      }  
  21. } 
Explanation of Code
 
In this code, you have SqlDataAdaptor, which is retrieving supplier records from a database and filling it in a DataSet supplierData. After that, you have performed certain modifications 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 an event handler on dataAdaptor's RowUpdated event. This event will be fired when the row is updated in the database and in that event handler mechanism we can define different statuses to the argument so that further action can 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.


Similar Articles