| How to overcome record locking in SQL Server in case of concurrent access of data |
In Most of multi-user ASP.NET application problem arises when two users are trying to access same data. For an example, if User A is modifying data and at the same time user B is trying to read data. Here we can’t be not say sure that whether user B gets the old original data or newly updated data. There are two options way to overcome this problem
1. by using Record locking (Pessimistic Concurrency)
2. Compare with original data (Optimistic Concurrency)
1. Pessimistic Concurrency: In pessimistic Concurrency when user A intends has a chance to edit record, an application l will lock that record and doesn’t allow any one to use touch it until user A completes his changes and save it. So we give guarantee that no user can read/update data while user A is editing the data. In pessimistic concurrency, user has to wait till record is locked by other user. One way to achieve this is : Put is Locked field with each record. Now when user A opens the record for editing, the field is Locked is set to true mode and after saving record set it to false mode. But problem arises when 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.
2. 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. Suggestion: If you are not concerned with the performance then use pessimistic concurrency else use optimistic concurrency to overcome problems of concurrent access of data.
Author:
By Bhumit Patel
Bhumit Patel is working as a Programmer at Semaphore Infotech Pvt. Ltd, India. You can contact me on my email bhumit@semaphore-software.com.
 www.semaphore-software.com
Article Source: UnArchived Articles
|
|
|
|
