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 system architect and principal in PH&V Information Services. PH&V provides full-stack consulting from UX design through object modeling to database design. Peter tweets about his VSM columns with the hashtag #vogelarticles. His blog posts on user experience design can be found at http://blog.learningtree.com/tag/ui/.

comments powered by Disqus

Featured

  • Creating Reactive Applications in .NET

    In modern applications, data is being retrieved in asynchronous, real-time streams, as traditional pull requests where the clients asks for data from the server are becoming a thing of the past.

  • AI for GitHub Collaboration? Maybe Not So Much

    No doubt GitHub Copilot has been a boon for developers, but AI might not be the best tool for collaboration, according to developers weighing in on a recent social media post from the GitHub team.

  • Visual Studio 2022 Getting VS Code 'Command Palette' Equivalent

    As any Visual Studio Code user knows, the editor's command palette is a powerful tool for getting things done quickly, without having to navigate through menus and dialogs. Now, we learn how an equivalent is coming for Microsoft's flagship Visual Studio IDE, invoked by the same familiar Ctrl+Shift+P keyboard shortcut.

  • .NET 9 Preview 3: 'I've Been Waiting 9 Years for This API!'

    Microsoft's third preview of .NET 9 sees a lot of minor tweaks and fixes with no earth-shaking new functionality, but little things can be important to individual developers.

  • Data Anomaly Detection Using a Neural Autoencoder with C#

    Dr. James McCaffrey of Microsoft Research tackles the process of examining a set of source data to find data items that are different in some way from the majority of the source items.

Subscribe on YouTube