Concurrent data modification in web applications

I'd like to describe an overlooked problem that affects
every web application that lets more than one user edit data. In Czech you could call it souběžná modifikace dat, and in English-language material you'll find it under concurrent data modification. Overlooking it when we design a system can cause a lot of trouble.

Description

We'll use a real-world example from an online store. When you're browsing
products in an online store, it's customary to show how many units are currently
in stock. Say the quantity in stock is stored in a shared database table.

ID Description In stock 1 Fil01 automatic washing machine 1

As you can see, there's one Fil01 washing machine in stock. Suppose you're interested in that
product and are looking at its detail page in your browser, where it shows one unit in stock. While you're deciding whether to buy it, another customer comes along who has their eye on the same item. Both browsers have loaded the detail page and show one unit in stock.

You hesitate a little longer, so the other customer's order is processed first. Thinking you're ordering the last one, you submit your order. Unfortunately the stock is gone, but your order goes through and you have to wait for it to ship. Set aside the possibility of canceling an order or adding the product to a cart as an intermediate step. We're considering concurrent modification when the client isn't interacting continuously with the server. The page is already loaded in the browser, and it only communicates with the server when it submits a request.

Pessimistic vs. optimistic locking

Locking is the technique used to address this problem. There are two approaches:
pessimistic and optimistic.

Pessimistic locking

Pessimistic locking means locking the data implicitly. In our online store, once user A loads the washing machine's detail page, no one else can read or change the relevant data. Use pessimistic locking when you expect concurrent changes to occur frequently. The record is locked directly in the database by transaction isolation or by the SELECT … FOR UPDATES clause, if
the database supports it.

Optimistic locking

With optimistic locking, we expect concurrent changes to be infrequent. We therefore don't lock the data at the database level. In effect, the lock operates at the application transaction level. The options depend on what the middleware offers or how we implement optimistic locking ourselves.

Pessimistic locking assumes that data will be modified concurrently and locks it implicitly. Optimistic locking assumes all will go well and locks only at the end of the application transaction, which in our online store means submitting the order. If a concurrent change occurs under the optimistic approach, the application is informed. In our example users A and B both load the product details. A submits an order, then B submits an order. Instead of a successful submission, B is told the product's stock status changed during the request.

Both approaches have advantages and disadvantages, and each suits a different kind of application. Pessimistic locking is resource-intensive, so the lock should be held as briefly as possible. That almost rules it out for web applications, where the client doesn't interact continuously with the server. Optimistic locking suits web applications better, but you must account for it when designing the application.

Hibernate-inspired optimistic locking

To finish, I'd like to outline the optimistic locking support in Hibernate, an ORM (object-relational mapping tool). The mechanism is generic enough to implement yourself in any environment. Let's return to the online store to see how.

The only change to our example is to add a version column to the table.

ID Description In stock Version 1 Fil01 automatic washing machine 1 1

Hibernate increments the version on each modification and
checks that the version of the row we're changing matches the one in the database. If we wanted to apply this approach without Hibernate, here's how we'd do it.

• Send the version to the client along with the business data when loading the product detail page.
• Send back the version when submitting the order.
• Before saving the order, check that the submitted version matches the version in the database.
• If they match, save the order and increment the version.
• If they differ, send the user back to the product detail page and explain that its status has changed.

As you can see, the versioning mechanism is general enough for
any table. The drawback is having to pass the version from the server to the client and
back every time.