Practical ASP.NET
Never Lock Records in Web Applications! Never! That Means You!
Seriously, don't. Here are two reasons why record locking is simply a bad idea.
Here's a negative column for you, all about something you should never,
never
do in an ASP.NET application: Lock records when retrieving records in order
to resolve data contention.
My clients often worry about data contention. The scenario they have in mind
goes something like this:
- Sue retrieves a Customer record at 9:00 and makes a change to the CustomerName
field.
- Lou retrieves the same record at 9:01 and changes the CustomerCredit field.
- Sue clicks her submit button and updates the record at 9:02.
- Lou clicks his submit button and updates the record at 9:03.
At this point, the ASP.NET DataSources create a problem by updating every field
in the record. Because of that default behavior, Lou's update at 9:03 overwrites
Sue's change to the CustomerName field saved at 9:02 by using the original data
Lou retrieved at 9:01. This is bad. My clients usually suggest that we solve
the problem by "locking records when someone retrieves them." This
is worse.
It's not an accident that ADO.NET doesn't include any native commands to support
record locking: You don't want to lock records in your database, especially
in an ASP.NET application.
Record Locking Is Evil: Reason No. 1
The first reason that you don't want to lock records is because it generates
extra work for you. Start by thinking about the process for when data is retrieved
in an ASP.NET application: The user's request hits your site, your code retrieves
the requested record, you embed its data into the WebForm and then send the
page down to the user.
Now what happens? The user looks at the screen deciding what to do and the
record remains locked. The user may, eventually, decide to do nothing and just
hit the submit button -- your record locking was unnecessary and, in addition,
you must add code to your page to release the lock.
Or the user may select a choice from one of your menus and navigate to another
page on your site. Since the current page's code isn't called when a menu choice
(or any hyperlink) is selected, you'll have to set up some system to check for
locked records on previous pages and release them.
Alternatively, the user may shut down their browser or wander off to some other
site. In either of these cases, no code on your site will execute and the record
will stay locked forever. To handle this, you'll need some system that sweeps
through your database looking for records that have been locked for "too
long" and release them.
So, unless you're being paid by the hour, you want to avoid record locking
just because it creates more work for you.
Record Locking Is Evil: Reason No. 2
The second reason that you don't want to lock records is because it reduces
the scalability of your application.
There are two reasons for this. First, as more users access your application,
the more likely it is that two users will attempt to access the same record.
If the first user on the scene has locked the record, then the second user will
either have their code idle while waiting for the lock to be released (soaking
up resources on the server) or will simply be told that he can't have the requested
record.
Either way, your application is supporting fewer users than if you just sent
the data to the second user. And remember, a lot of the time, when you lock
a record, the user doesn't change anything and you've annoyed the other users
for no reason.
The second way that record locking reduces scalability is simpler: Record locking
is hard on the database server. Once you start locking records you'll find that
just about all of your performance measures get worse even if the number of
users doesn't increase.
When it comes to record locking, just say "No."
What Not To Do
But as I noted at the start, because the ASP.NET DataSources update every field
in a record, you do need to handle record contention. There are two solutions
which I've discussed
here
and
here
.
About the Author
Peter Vogel is a principal in PH&V Information Services, specializing in ASP.NET development with expertise in SOA, XML, database, and user interface design. His most recent book ("rtfm*") is on writing effective user manuals, and his blog on technical writing can be found at rtfmphvis.blogspot.com.