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.

• By Peter Vogel
• 07/23/2008

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.