One way to work around optimistic concurrency issues in ADO is to lock the records your DataSet retrieves as soon as the edit operation begins. This strategy is known as pessimistic locking. Long-duration locks generally lead to poor database performance and contention problems, but in situations where an application cannot tolerate having records changed while it is updating them, pessimistic locking may be necessary.

With ADO, it’s relatively easy to use a pessimistic locking scheme. In ADO.NET, it’s somewhat more difficult to set up, but you can still use pessimistic locking in your applications. However, just because you can do something doesn’t mean you should do it all the time—what I’m about to explain should be used only when absolutely necessary.

For more information

For a little background on optimistic concurrency and the troubles it can cause for ADO.NET developers, check out these articles:

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
   txn = SqlConnection1.BeginTransaction(IsolationLevel.RepeatableRead)
   SqlDataAdapter1.SelectCommand.Transaction = txn
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
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.