SQL Server 2005 - concurrency & locking with ASP.NET applications
One of my Developer asked how to handle the concurrency and locking on one application where ASP.NET is used, by default the SQL Server 2005 behavior that allows Pessimistic & Optimistic locking for concurrency.
To avoid the access of records by multi-users at the same time that causes locking, two options available to overcome this problem:
- Pessimistic Concurrency that locks a record which is under update or insert
- Optimistic Concurrency to compare with original data
In pessimistic Concurrency the user intendes to edit record to raise a chance of lock that record.until that process completes his changes and save it. In this case other proceses has to wait till record is locked by other user. Use locking hints during the SELECT statement queries. But problem arises when say if user A opens record to view their contents and closes the browser before saving it e record it to database. Now this record will consider as locked. To solve this problem we have to add one more field Timestamp with each record. Timestamp field define expiration time of record and user must edit the record within expiry of ration time limit. Alternatively, we require two fields flag AND a timestamp.
The flag indicates the lock state, and the time is the expirations time. Then create one SQL job that runs and checks the expirations times and automatically unlocks records that will show past time of expiry expiration.
Optimistic Concurrency: Here no locking concept is used. Anyone can read and modify the record at anytime and you don’t give chance to anyone to will take your chances that the record is not modify the records modified by someone else before you take have the chance to modify and save it. The one solution is not to lock the record but keep a snapshot of the value that is editable and when the user submits it will compare the old snapshot with what is in the existing record and if it does not match, the new record would not be saved and send an alert to the user that the data is old and needs to be refreshed for them to submit their work Here the problem arises when there are more fields exist in record .So we need to compare all fields with their original value. This is very time consuming process. So to overcome this problem we add one Timestamp field with each record. The value of timestamp field will be modified every time a change is made to a record that contains such a field. Now compare timestamp value with original timestamp value if both are same then we can say that now changes made with the record else send and alert message to the user that the data is old and needs to be refreshed for them to submit their work In Optimistic concurrency user doesn’t require to wait to do their changes so it provide better performance compared to with pessimistic concurrency.
For the sake of performance it is better to pessimistic concurrency, otherwise optimistic is bette to handle for data consistency.