When multiple users attempt to modify the same data at the same time, a process needs to be established that prevents one user's modifications from adversely affecting other user's modifications. Concurrency Control is the process used to identify and resolve database updates that are made by simultaneous users.
In general, there are three ways to handle concurrency in a database system:
Pessimistic concurrency control is generally used in two situation:
However, pessimistic concurrency control is not possible in a disconnected architecture (DataSets use a disconnected architecture). In a disconnected architecture, connections are open only long enough to read data, after which the connection is dropped and results sent back to the client. The client is then free to process data for as long as he/she wants. When the user is ready to commit data back to the database, a connection is re-opened only during the update process. Because locks are not maintained during the entire process of reading and updating data, pessimistic concurrency control is not possible in a disconnected architecture.
In optimistic concurrency control, locks are set and held only when the database is accessed. These locks prevent users from updating records at the same time. In an optimistic concurrency control, the data is always available except for the exact moment when it is updated.
When an update is attempted using optimistic concurrency, the original version of a changed row is compared against the corresponding row in the database. If the two versions match, the update succeeds. Otherwise, the update process fails because some other user has already updated the same data. It is then up to the user to reconcile the two rows using his/her own business logic. One possible approach for this business logic is to present the end-user with the current version (user's changes) and updated version (other user's changes) of the record that caused the violation and leave it up to the user to determine what to do next. See Handling Concurrency Errors for more details
With this approach, the update is written to the database, regardless of whether the row(s) in question were already updated by somebody else. Updates are simply written to the database, overwriting any existing changes. You need to ensure that overwriting other user's changes is acceptable.
ADO.NET uses optimistic concurrency control because the data architecture uses a disconnected architecture. Therefore, business logic must always be added to resolve issues with optimistic concurrency. If you decide to use optimistic concurrency, there are two general ways to determine if a change has occurred: version/date-time stamps approach or saving-all-values approach.
In this approach, the column to be updated must contain a version or date-time column. This version or date-time value must be kept accurate perhaps by having a trigger that updates it whenever any other value in the row changes. The version/date-time value is read for each row and saved on the client. When the client attempts to update the database, it must compare its own version/date-time value against that of the database for each row that the client wants to update . If the version numbers match, then that row has not changed since the client last read it and the update can proceed. If on the other hand, the version numbers do not match, the update process fails and error is returned to the client.
One way to handle the version number approach in SQL is:
UPDATE SomeTable SET Column1 = @newValue1, Column2 = @newValue WHERE RowVersion = @originalRowVersionValue
This approach uses features from the DataSet to handle concurrency. Recall that a DataSet maintains multiple versions of each DataRow in each DataTable: an original version that was read from the database and a modified version that represents user updates. When attempting to write user changes back to the database, the original values present in the DataRow (taken from its Original version) are compared against the corresponding record in the data source. If they match, then that row in the data source has not changed since the client last read, and the update process can proceed.
Saving-all-values approach can be accomplished using the DataSet's UpdateCommand property along with a parameter's SourceVersion property to indicate whether a parameter value should come form the Current version or from the Original version of each DataRow.
// Create a new data adpater and set
its UpdateCommand property
DataAdapter da = new DataAdapter();
da.UpdateCommand = new SqlCommand("update Categories set CategoryName =
@CatName where CategoryID =
@CatID");
da.UpdateCommand.Connection = conn;
// Add parameters for the update command (it has two parameters identified by '@').
SqlParameter param1 = da.UpdateCommand.Parameters.Add("@CatName",
SqlDbType.NVarChar, 12, "CategoryName" );
param1.SourceVersion = DataRowVersion.Current;
SqlParameter param2 = da.UpdateCommand.Parameters.Add("@CatID",
SqlDbType.Int, 4, "CategoryID" );
param2.SourceVersion = DataRowVersion.Original;
// ...
// Make a change to some row then update it
dsData.Tables[0].Rows[0]["CategoryName"] = "Some new category";
da.Update( dsData );
// ...
In the code above note that the @CatName parameter will get its value from the Current version of each DataRow and it is this value that will be written to the database, while the @CatID parameter will get its value from the Original version of each DataRow and it is this value that will be used to locate the original record in the database.
Handling concurrency errors is based on using exceptions. The .NET Framework provides the DBConcurrencyException class to assist in resolving issues from concurrency violations. The DBConcurrencyException exception is thrown by a DataAdapter during an Update operation as a result of a concurrency violation. This object returns the data row that caused the error as well as the message associated with the failed update.
try
{
// Create a new data adpater and set
its UpdateCommand property
DataAdapter da = new DataAdapter();
da.UpdateCommand = new SqlCommand("update Categories set CategoryName =
@CatName where CategoryID = @CatID");
da.UpdateCommand.Connection = conn;
// Add parameters for the update command (it has two parameters identified by '@').
SqlParameter param1 = da.UpdateCommand.Parameters.Add("@CatName",
SqlDbType.NVarChar, 12, "CategoryName" );
param1.SourceVersion = DataRowVersion.Current;
SqlParameter param2 = da.UpdateCommand.Parameters.Add("@CatID",
SqlDbType.Int, 4, "CategoryID" );
param2.SourceVersion = DataRowVersion.Original;
// ...
// Make a change to some row then update it
dsData.Tables[0].Rows[0]["CategoryName"] = "Some new category";
da.Update( dsData );
}
catch( DBConcurrencyException e)
{
string strErrMsg = e.Message;
string strRowErrMsg = e.Row[0].ToString();
// Process the concurrency violation
according to business rules. See sub-sections below
}
The specific rules that govern your application should determine how concurrency errors should be handled. One such strategy would be to present the user with three versions of each record that caused a concurrency violation:
The user would then have to decide whether or not to overwrite the changed record in the database with his/her changes.
Implementing optimistic concurrency with dynamic SQL depends on having a SQL statement with a WHERE clause with that contains all the original data source values. A example was of handling concurrency errors with dynamic SQL was shown in the Saving All Values Approach section.
This approach is very similar to Implementing optimistic concurrency with dynamic SQL except that the WHERE clause parameters are sent to a stored procedure that handles the update rather than having a dynamic SQL statement constructed by the client.