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.
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.
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
Recent articles in this category:
- Improper Way Of Marketing Reflects Poorly On A Company.
New business, product or service everything requires visibility, awareness in order to come into the - Replacing Paper Prints With Online Versions
Nowadays saving out on resources and being additionally informative are both aspects that are in. In - Stop Smoking Effectively
If I told you of a way that you could stop smoking harmful tobacco would you believe it? Most people - What Is Runtime Error 182? And How To Fix It
Are you finding an effective way to fix runtime error 182? Do you think fixing runtime error 182 is - Do You Know How To Fix Runtime Error 87 In Minutes?
Are you finding an effective way to fix runtime error 87? Do you think fixing runtime error 87 is to - Knowledge About Avi, Avi Player, Avi Converter On Mac
Knowledge about AVI, AVI player, AVI Converter on MacWhat is an AVI?AVI, an acronym for Audio Video - Buy Your Highly Successful Email Survey Software Today
Email Survey Software- Boost Your Business and Increase ProfitsAn email survey software could be one - Xrm - The Anything Relationship Management Solution
I recently attended the Microsoft Dynamics West Region FY11 Sales Planning Retreat. This year's meet - What Are The Benefits Of Working With Electronic Medical Records
Recording medical information is a vital part of health care services. These records are necessary f - Basic Factor To Make Website Business Oriented
Internet is home for millions of websites. The online business is becoming more and more competitive
Most viewed articles in this category:
- Parental Control Software
Parental control software is software that can help parents protect their children when they are onl - Digital Asset Management Software
Managing and organizing your organization's documents is a critical component to your business's suc - AdobeRGB vs. sRGB
Understanding color spaces I'll try to explain it very simplified, but understandable for everyone - Confessions of a Prankster
I wanted to get a jump on April Fool's Day, partially because of the long, cold winter blues, and pa - Malicious Thoughts About The Spyware Ills Of My PC
Who would think I was capable of such revengeful thoughts about the parties responsible for inflicti - Recover File and Recover Deleted File Tools
Data recovery software is a very effective way of retrieving data from a worn or damaged hard disk d - Life without Windows
Ubuntu, a user-friendly version of Linux, has been running so nicely on my home PC that I decided to - What Benefit Does an Online Software Download Site Offer You?
Are you having a problem that where you find a good softeware when you consider to have a try or wan - Maintaining A Website
There was an era when people were talking about how to create a website using html coding or some ea - Benefits Of Proper Time Tracking
Have you ever written down time when you have started and finished your work? Maybe you have had mul