How to overcome record locking in SQL Server in case of concurrent access of data

by Semaphore - Date: 2007-02-15 - Word Count: 604 Share This!

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.


By Bhumit Patel

Bhumit Patel is working as a Programmer at Semaphore Infotech Pvt. Ltd, India. You can contact me on my email

Related Tags: india, windows mobile, business process outsourcing, software testing, offshore software development, consulting services, offshore outsourcing, data entry india, offshore outsourcing india, outsourcing software development, application development, offshore software development company india, it outsourcing company, website design and development, java development, net application development, vb .net application development, asp .net development, database application, system integration, search engine optimization and promotion

Your Article Search Directory : Find in Articles

© The article above is copyrighted by it's author. You're allowed to distribute this work according to the Creative Commons Attribution-NoDerivs license.

Recent articles in this category:

Most viewed articles in this category: