I am currently stuck by record locking in ADO.NET. I'm using dataset and want to lock an individual record (such as a warning message) if a record is being updated by another user. Since the dataset is in disconnected model, and I do not have a consistent connection, how can the record lock be implemented? Is it better to use pessimistic or optimistic concurrency control?
Optimistic is always better; it scales. You can use the timestamp approach, by having such a field on the underlying table. Before updating a record, you compare your timestamp (the one in the disconnected dataset) with the one in the database table for that record. If they are not equal, it means somebody else changed if before. Then, you can either throw an exception, warn the user he's about to overwrite some other's work, or reload the data and show a conflict resolution UI, or whatever.

This was first published in November 2003

Back to top