Concurrent Database Access

In many organizations, concurrent database access is common, so it is important to prevent data from being modified so that it will adversely affect other users. To prevent external data modification, two record-level locking methods can be applied to individual records: optimistic and pessimistic concurrency.

Pessimistic concurrency uses the method of applying a lock directly to the row being modified. This requires a "lock" column to exist in the table, which is set when a user fetches data for modification. If another user tries to modify the same row, the lock denies the request until the initial user clears the lock. This method of locking rows is typically used in environments where there is a high potential of data retrieval conflicts. The use of row-level locking outweighs the cost of rolling back transactions if a data conflict does occur.

With optimistic concurrency, no row level locks are applied. The row checking is done when the user submits an update to the database. There are three ways to determine whether a record can be updated using optimistic concurrency. First, when data is submitted to be updated, an original copy of the row data is sent along with the updated information. Each original field value is compared to the record being updated and if any of the fields differ , the update does not occur. Second, a date-time stamp is used to indicate the last update to the row. If the row being updated has a newer date stamp than the data being sent, the update fails. Finally, an incremental sequence number is also used. This is similar to the date-time stamp, and gets incremented each time the row changes.

ADO.NET now provides a third method for handling concurrency. With the DataAdapter Configuration Wizard (DCW), a stored procedure is automatically created that accomplishes the primary method of optimistic concurrency. It contains parameters for the modified data and the original data. When the procedure is called, the updated data is passed as the first group of parameters and the original data as the second. To illustrate this, the following stored procedure declaration is shown with the two sets of parameters.

 Create Stored Procedure UpdatePublishers  @pub_id    char(4),  @pub_name  varchar(40),  @city    varchar(20),  @state    char(2),  @country  varchar(30),  @param1    char(4),  @param2    varchar(40),  @param3    varchar(20),  @param4    char(2),  @param5    varchar(30)  AS  Update Publishers set pub_id=@pub_id,pub_name=@pub_name,    city=@city,state=@state,country=@country where (pub_id=@param1)    AND (pub_name=@param2) AND (city=@param3) AND (state=@param4) AND (country=@param5) 

From the example, the parameters @param1 through @param5 are just copies of the original five parameters. To verify the update was successful, the number of rows affected can be checked and handled appropriately.