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 intendshas a chance to edit record, an application l will lock that record and does notn’t allow any one to usetouch it until user A complete 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 isLocked field with each record. Now when user A opens the record for editing, the field isLocked 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 theit 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 ofiration time limit. Alternatively, we require two fields flag AND a timestamp.The flag indicates the lock state, and the time is the expiryation time.Then create one SQL job that runs and checks the expiryation times and automatically unlocks records that will showare past time of expiryexpiration.
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 towill take your chances that the record is not modify the recordsmodified by someone else before you takehave the a 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 .Sso 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 everytime 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.
About Author
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.
Source: ArticleTrader.com