Preventing Multi-User Concurrency Problems

This article is the fourth in a series of articles that describe how to use Visual Basic 6 to develop scriptless, scalable, multitiered, component-based applications for the Web. In this article, Kyle Lutes enhances the sample application to make it more robust by adding code to prevent multiuser concurrency problems.

Like this article? We recommend

As usual, you should have already read my previous articles in this series;
and be familiar with using ASP, VBScript, and either Microsoft Internet Information
Server (IIS) or Microsoft Personal Web Server (PWS) to develop and debug applications
for the Web. My standard disclaimer: In order to keep the sample application
simple to understand, the user interface elements are kept to a minimum and
the Visual Basic code isn't as robust as a production application should
be. But I do make notes where it is a good idea to include additional logic.
And finally, the code for the sample application is too lengthy to be completely
included in this article. Instead, you can download a zip file that contains
all the files necessary to run the sample application here.

Multiuser Concurrency Problems

Developing applications that support multiple users requires much more
defensive coding techniques than desktop applications that support only a single
user. If the possibility exists that two or more individual users will be
working with the same piece of data at the same time, your code must be ready to
handle the unexpected. Generally, this means that your logic shouldn't make
any assumptions about the data. For example, just because your code successfully
retrieved a piece of data from a database moments ago, it shouldn't assume
it is still there the next time it needs to retrieve it. While your code was
busy processing something else, or waiting for a response from the user, another
user could have deleted it.

Concurrency problems are well-known and documented, but if you aren't
familiar with the term, here's a quick explanation of one kind of
concurrency problem. This problem, commonly referred to as the lost update
problem, is when two users attempt to update the same piece of data at the
same time. Suppose that you've developed a Web application that allows
employee information to be updated. Also, suppose that the employee information
is stored in a relational database in which one row in the table contains the
information for one employee, and that this information includes the
employee's office telephone number and his or her hourly pay rate.

The lost update scenario then goes something like this:

Employee Bob gets promoted, and he gets a new office and a pay increase
with his new promotion.

A user from the telephony department assigns Bob a telephone number for
his new office and then uses your application to inquire on Bob's employee
record so that she can change his office telephone number. After Bob's
record has been found and while his data is displayed in a form on her PC screen
(and before any changes are made), the user answers a telephone call of her own.

While the telephony user is talking on the phone, a payroll clerk uses
your application to inquire on Bob's employee record so that he changes his
hourly pay rate to give him his raise. Suppose that Bob's old pay rate was
$20.00, and the clerk types in a new pay rate of $25.00. He then clicks the
Update button to save the changes to the database. Your application sends the
new pay rate, along with all the other values on the form, to the database. At
this point, nothing bad has happened.

The telephony user finishes her phone call, and returns to the task of
changing Bob's office phone number. She changes his phone number, and
clicks the Update button to save the changes to the database. Your application
sends the new telephone number, along with the other values on the
form—including the old hourly pay rate of $20.00—to the
database.

Thus, the telephony user's second update overwrites the changes made
by the payroll clerk's first update.

You might think that this scenario is unlikely to happen, and I guess
you'd be right. But if you were Bob, you'd be pretty upset if it did
happen. More importantly, as professional software developers, we should be
writing well-behaved code that can handle abnormal situations such as this one.
We've all used software whose developer didn't bother checking for the
unexpected; when you used the software in an unexpected way, data was lost, or
the program crashed.