Optimistic Concurrency

In a multiuser environment, there are two models for updating data in a database: optimistic concurrency, and pessimistic concurrency. The DataSet object is designed to encourage the use of optimistic concurrency for long-running activities such as when you are remoting data and when users are interacting with data.

Pessimistic concurrency involves locking rows at 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, other users cannot perform actions that would conflict with the lock until the lock owner releases it. This model is primarily used in environments where there is heavy contention for data, where the cost of protecting data with locks is less than the cost of rolling back transactions if concurrency conflicts occur.

Therefore, in a pessimistic currency model, a user who reads a row with the intention of changing it establishes a lock. Until the user has finished the update and released the lock, no one else can change that row. For this reason, pessimistic concurrency is best implemented when lock times will be short, as in programmatic processing of records. Pessimistic concurrency is not a scalable option when users are interacting with data, causing records to be locked for relatively large periods of time.

By contrast, users who use optimistic concurrency do not lock a row when reading it. When a user wants to update a row, the application must determine whether another user has changed the row since it was read. Optimistic concurrency is generally used in environments with a low contention for data. This improves performance as no locking of records is required, and locking of records requires additional server resources. Also, in order to maintain record locks, a persistent connection to the database server is required. Because this is not the case in an optimistic concurrency model, connections to the server are free to serve a larger number of clients in less time.

In an optimistic concurrency model, a violation is considered to have occurred if, after a user receives a value from the database, another user modifies the value before the first user has attempted to modify it.

The following tables follow an example of optimistic concurrency.

At 1:00 p.m., User1 reads a row from the database with the following values:

CustID     LastName     FirstName

101          Smith             Bob

Column name	Original value	Current value	Value in database
CustID	101	101	101
LastName	Smith	Smith	Smith
FirstName	Bob	Bob	Bob

At 1:01 p.m., User2 reads the same row.

At 1:03 p.m., User2 changes FirstName from "Bob" to "Robert" and updates the database.

Column name	Original value	Current value	Value in database
CustID	101	101	101
LastName	Smith	Smith	Smith
FirstName	Bob	Robert	Bob

The update succeeds because the values in the database at the time of update match the original values that User2 has.

At 1:05 p.m., User1 changes Bob's first name to "James" and tries to update the row.

Column name	Original value	Current value	Value in database
CustID	101	101	101
LastName	Smith	Smith	Smith
FirstName	Bob	James	Robert

At this point, User1 encounters an optimistic concurrency violation because the values in the database no longer match the original values that User1 was expecting. The decision now needs to be made whether to overwrite the changes supplied by User2 with the changes supplied by User1, or to cancel the changes by User1.

Testing for Optimistic Concurrency Violations

There are several techniques for testing for an optimistic concurrency violation. One involves including a timestamp column in the table. Databases commonly provide timestamp functionality that can be used to identify the date and time when the record was last updated. Using this technique, a timestamp column is included in the table definition. Whenever the record is updated, the timestamp is updated to reflect the current date and time. In a test for optimistic concurrency violations, the timestamp column is returned with any query of the contents of the table. When an update is attempted, the timestamp value in the database is compared to the original timestamp value contained in the modified row. If they match, the update is performed and the timestamp column is updated with the current time to reflect the update. If they do not match, an optimistic concurrency violation has occurred.

Another technique for testing for an optimistic concurrency violation is to verify that all the original column values in a row still match those found in the database. For example, consider the following query:

SELECT Col1, Col2, Col3 FROM Table1

To test for an optimistic concurrency violation when updating a row in Table1, you would issue the following UPDATE statement:

UPDATE Table1 Set Col1 = @NewCol1Value,
              Set Col2 = @NewCol2Value,
              Set Col3 = @NewCol3Value
WHERE Col1 = @OldCol1Value AND
      Col2 = @OldCol2Value AND
      Col3 = @OldCol3Value

As long as the original values match the values in the database, the update is performed. If a value has been modified, the update will not modify the row because the WHERE clause will not find a match.

Note that it is recommended to always return a unique primary key value in your query. Otherwise, the preceding UPDATE statement may update more than one row, which might not be your intent.

If a column at your data source allows nulls, you may need to extend your WHERE clause to check for a matching null reference in your local table and at the data source. For example, the following UPDATE statement verifies that a null reference in the local row still matches a null reference at the data source, or that the value in the local row still matches the value at the data source.

UPDATE Table1 Set Col1 = @NewVal1
  WHERE (@OldVal1 IS NULL AND Col1 IS NULL) OR Col1 = @OldVal1

You may also choose to apply less restrictive criteria when using an optimistic concurrency model. For example, using only the primary key columns in the WHERE clause results in the data being overwritten regardless of whether the other columns have been updated since the last query. You can also apply a WHERE clause only to specific columns, resulting in data being overwritten unless particular fields have been updated since they were last queried.

The DataAdapter.RowUpdated Event

The DataAdapter.RowUpdated event can be used in conjunction with the techniques described earlier, to provide notification to your application of optimistic concurrency violations. RowUpdated occurs after each attempt to update a Modified row from a DataSet. This enables you to add special handling code, including processing when an exception occurs, adding custom error information, adding retry logic, and so on. The RowUpdatedEventArgs object returns a RecordsAffected property with the number of rows affected by a particular update command for a modified row in a table. By setting the update command to test for optimistic concurrency, the RecordsAffected property will, as a result, return a value of 0 when an optimistic concurrency violation has occurred, because no records were updated. If this is the case, an exception is thrown. The RowUpdated event enables you to handle this occurrence and avoid the exception by setting an appropriate RowUpdatedEventArgs.Status value, such as UpdateStatus.SkipCurrentRow. For more information about the RowUpdated event, see Working with DataAdapter EventsWorking with DataAdapter Events.

Optionally, you can set DataAdapter.ContinueUpdateOnError to true, before calling Update, and respond to the error information stored in the RowError property of a particular row when the Update is completed. For more information, see Adding and Reading Row Error Information.

Optimistic Concurrency Example

The following is a simple example that sets the UpdateCommand of a DataAdapter to test for optimistic concurrency, and then uses the RowUpdated event to test for optimistic concurrency violations. When an optimistic concurrency violation is encountered, the application sets the RowError of the row that the update was issued for to reflect an optimistic concurrency violation.

Note that the parameter values passed to the WHERE clause of the UPDATE command are mapped to the Original values of their respective columns.

[Visual Basic]

Dim nwindConn As SqlConnection = New SqlConnection("Data Source=localhost;Integrated Security=SSPI;Initial Catalog=northwind")

  Dim custDA As SqlDataAdapter = New SqlDataAdapter("SELECT CustomerID, CompanyName FROM Customers ORDER BY CustomerID", nwindConn)

  ' The Update command checks for optimistic concurrency violations in the WHERE clause.
  custDA.UpdateCommand = New SqlCommand("UPDATE Customers (CustomerID, CompanyName) VALUES(@CustomerID, @CompanyName) " & _
                                        "WHERE CustomerID = @oldCustomerID AND CompanyName = @oldCompanyName", nwindConn)
  custDA.UpdateCommand.Parameters.Add("@CustomerID", SqlDbType.NChar, 5, "CustomerID")
  custDA.UpdateCommand.Parameters.Add("@CompanyName", SqlDbType.NVarChar, 30, "CompanyName")

  ' Pass the original values to the WHERE clause parameters.
  Dim myParm As SqlParameter
  myParm = custDA.UpdateCommand.Parameters.Add("@oldCustomerID", SqlDbType.NChar, 5, "CustomerID")
  myParm.SourceVersion = DataRowVersion.Original
  myParm = custDA.UpdateCommand.Parameters.Add("@oldCompanyName", SqlDbType.NVarChar, 30, "CompanyName")
  myParm.SourceVersion = DataRowVersion.Original

  ' Add the RowUpdated event handler.
  AddHandler custDA.RowUpdated, New SqlRowUpdatedEventHandler(AddressOf OnRowUpdated)

  Dim custDS As DataSet = New DataSet()
  custDA.Fill(custDS, "Customers")

  ' Modify the DataSet contents.

  custDA.Update(custDS, "Customers")

  Dim myRow As DataRow

  For Each myRow In custDS.Tables("Customers").Rows
    If myRow.HasErrors Then Console.WriteLine(myRow(0) & vbCrLf & myRow.RowError)
  Next


Private Shared Sub OnRowUpdated(sender As object, args As SqlRowUpdatedEventArgs)
  If args.RecordsAffected = 0
    args.Row.RowError = "Optimistic Concurrency Violation Encountered"
    args.Status = UpdateStatus.SkipCurrentRow
  End If
End Sub
[C#]
  SqlConnection nwindConn = new SqlConnection("Data Source=localhost;Integrated Security=SSPI;Initial Catalog=northwind");

  SqlDataAdapter custDA = new SqlDataAdapter("SELECT CustomerID, CompanyName FROM Customers ORDER BY CustomerID", nwindConn);

  // The Update command checks for optimistic concurrency violations in the WHERE clause.
  custDA.UpdateCommand = new SqlCommand("UPDATE Customers (CustomerID, CompanyName) VALUES(@CustomerID, @CompanyName) " +
                                        "WHERE CustomerID = @oldCustomerID AND CompanyName = @oldCompanyName", nwindConn);
  custDA.UpdateCommand.Parameters.Add("@CustomerID", SqlDbType.NChar, 5, "CustomerID");
  custDA.UpdateCommand.Parameters.Add("@CompanyName", SqlDbType.NVarChar, 30, "CompanyName");

  // Pass the original values to the WHERE clause parameters.
  SqlParameter myParm;
  myParm = custDA.UpdateCommand.Parameters.Add("@oldCustomerID", SqlDbType.NChar, 5, "CustomerID");
  myParm.SourceVersion = DataRowVersion.Original;
  myParm = custDA.UpdateCommand.Parameters.Add("@oldCompanyName", SqlDbType.NVarChar, 30, "CompanyName");
  myParm.SourceVersion = DataRowVersion.Original;

  // Add the RowUpdated event handler.
  custDA.RowUpdated += new SqlRowUpdatedEventHandler(OnRowUpdated);

  DataSet custDS = new DataSet();
  custDA.Fill(custDS, "Customers");

  // Modify the DataSet contents.

  custDA.Update(custDS, "Customers");

  foreach (DataRow myRow in custDS.Tables["Customers"].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;
  }
}