What’s difference between “Optimistic” and “Pessimistic” locking?

In pessimistic locking when user wants to update data it locks the record and till then no one can update data. Other user’s can only view the data when there is pessimistic locking. In optimistic locking multiple users can open the same record for updating, thus increase maximum concurrency. Record is only locked when updating the record. This is the most preferred way of locking practically. Now a days browser based application is very common and having pessimistic locking is not a practical solution.

Transactions and Concurrency Control using ADO.NET:

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

In this article I am concentrating of transaction management that is Concurrency Control in .NET environment.

Transaction

A transaction is an abstract unit of concurrent computation that execute automatically. The effect of 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 none of its effects (In this case you will rollback the changes).

In the transaction control we generally define code in between a block where we perform mission critical operation. If all operations get completed successfully then that part is committed in the database otherwise what ever modification you might have done during the process is roll backed from the database so that it never affect other user’s operations.

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

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;
}
}

Explanation of Code:

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

ref:http://www.c-sharpcorner.com/UploadFile/shrijeetnair/TransactionsNConcurr09052005002744AM/TransactionsNConcurr.aspx

Pessimistic locking in six easy steps
The basics steps for pessimistic locking are as follows:

1. Create a transaction with an IsolationLevel of RepeatableRead.
2. Set the DataAdapter’s SelectCommand property to use the transaction you created.
3. Make the changes to the data.
4. Set DataAdapter’s Insert, Update, and Delete command properties to use the transaction you created.
5. Call the DataAdapter’s Update method.
6. Commit the transaction.

Let’s explore these steps with a sample application. First, create a new Visual Basic. NET Windows application project in Visual Studio .NET. Add two button controls—one named cmdFill and another named cmdUpdate—and a DataGrid to the form. Set the cmdFill.Text property to Fill and cmdUpdate.Text property to Update so you can identify these buttons later when you test the sample application.

Next, drag a SqlDataAdapter onto the form, and when the wizard starts, connect to the Northwind database and use SELECT * FROM Customers as your SQL select statement. Generate a DataSet, name it dsCustomers, and add it to the designer window. Finally, bind the DataGrid’s DataSource property to DsCustomers1.Customers.

---

No SQL Server?
If you don’t have access to SQL Server, you should be able to duplicate this sample app with any transaction-aware OLE-DB database using OLE-DB-specific ADO.NET components.

---

Add the event handlers
Supporting pessimistic locking requires surprisingly little code. In this sample, all the work is done inside the event handlers for the two buttons, cmdFill and cmdUpdate. First, add the following code for cmdFill’s Click event handler:

Dim txn As SqlTransaction

Private Sub cmdFill_Click(ByVal sender As System.Object, _
ByVal e As System.EventArgs) Handles cmdFill.Click
SqlConnection1.Open()
txn = SqlConnection1.BeginTransaction(IsolationLevel.RepeatableRead)
SqlDataAdapter1.SelectCommand.Transaction = txn
SqlDataAdapter1.Fill(DsCustomers1)
End Sub

Make sure that the txn transaction variable is defined outside of any procedure, preferably at the class level of the form. You’ll need to use it again when you commit the update, so you don’t want it going out of scope when the cmdFill_Click procedure ends.

Inside the cmdFill_Click procedure, create a Transaction object by calling the SqlConnection object’s BeginTransaction method. Note that here you’ll use the BeginTransaction overload that accepts an IsolationLevel parameter, so you can specify RepeatableRead. You then set the SelectCommand’s Transaction property to the Transaction object you just created and call the Fill method to fill the DsCustomers1 DataSet.

The Fill method will run the SelectCommand query you specified when you built the DataAdapter and will start the transaction on the server. Because you have set an isolation level for the transaction of RepeatableRead, which locks all records returned by the query, no other application will be able to update the data you retrieve.

The next step is to create the update code, which commits the transaction and removes the record locks. Add the following code for the Update button’s Click event handler:

Private Sub cmdUpdate_Click(ByVal sender As System.Object, _
ByVal e As System.EventArgs) Handles cmdUpdate.Click
SqlDataAdapter1.InsertCommand.Transaction = txn
SqlDataAdapter1.UpdateCommand.Transaction = txn
SqlDataAdapter1.DeleteCommand.Transaction = txn
SqlDataAdapter1.Update(DsCustomers1)
txn.Commit()
SqlConnection1.Close()
End Sub

Here, you take the Transaction object you created in cmdFill_Click, txn, and assign it to the InsertCommand, UpdateCommand, and DeleteCommand properties of the DataAdapter. This way, any changes made to the data will play back in the same transaction you started when you retrieved the records. Without setting these values, the updates performed in cmdUpdate_Click wouldn’t be able to complete because the records would be locked.

Having set the updates as part of the original transaction, you can simply call the DataAdapter’s Update method and let the updates occur. Finally, call the Commit method of the transaction to free up the locks.

Testing the application
To test the application, compile it and then run two instances of it. Click the Fill button on both instances. On the second instance of the application, change a value and click the Update button. Return to the first instance of the application, change the same record by editing a different field, and click the Update button.

The update made in the first instance of the application will succeed because it was holding a lock on the records. The second instance will simply display an error message, indicating it was chosen as the victim of a deadlock situation. You can also launch the application, click the Fill button, and then attempt to make a change to a record using another method, such as the SQL Server Query Analyzer. As long as you click the Fill button before you try to run a SQL query in Query Analyzer, Query Analyzer will be forced to wait for the locks to be removed before executing the query.

A few words of warning
As I mentioned, you’d normally try to avoid pessimistic locking if at all possible, for performance reasons. However, in certain cases, you may need to use it. And as I’ve shown, the code to perform pessimistic locking in ADO.NET is relatively straightforward.

If you are forced to use pessimistic locking, you should attempt to update the records and complete the open transaction quickly to keep the record locks in place for as brief a time as possible. Under no circumstances should you hold the transaction open while awaiting some action from the user, like I did for reasons of clarity in the sample application I presented. A user could easily walk away from the computer in the middle of an update, possibly leaving the records locked for hours or even days. Instead, you’ll want to programmatically make the updates remove the locks from the records in a single step.