ADO.NET in a Nutshell

Handling Concurrency Issues

It is important to handle concurrency issues that almost inevitably result when multiple users access data simultaneously. Updates from one user should not inadvertently overwrite changes made by another user.

Because ADO.NET uses optimistic concurrency, two users can request the same data and then subsequently update the same data. Without managing concurrency, the changes made by the first user will be overwritten by the second user’s changes and, to make matters worse, no one will know that this has happened.

There are several ways that concurrency can be managed in an optimistic locking environment. As discussed earlier, the CommandBuilder object attempts to match all original fields to the row data source to determine if the row has changed since it was retrieved. That approach suffers from several significant drawbacks that limit its usefulness.

The best approach to managing concurrency is to add a timestamp column to the table. The timestamp type is supported by most databases and doesn’t actually contain a time and date; it’s a binary value that is unique within the database. When updating a record in the data source, the timestamp value is used in addition to the primary key to locate a record in the data source. If a row has been modified by another user since it was last retrieved, it will not be found because the timestamp value will have changed. The second user can be notified of the failure, shown the new data in the row, and presented with ...