Most Web applications access data from a database, and then present that data in some user interface widget, such as an HTML page or a rich-client UI. As long as data is inside the database, a database management system's data access control mechanisms, such as transactions and data locking, can guarantee that concurrent transactions don't inadvertently overwrite the same data item. That guarantee, however, no longer holds after a data is accessed outside the confines of a database, writes Felipe Gaucho in a recent blog post, Don't break the optimistic locking.

In his blog post, Gaucho notes that the Java Persistence API (JPA) provides several tools to deal with situations where data detached from a persistence context needs to be re-attached, while also ensuring that other updates to the same data item don't inadvertently overwrite changes:

When exposing domain models through web-services you should serialize your entities between the client and the service, and every time you do that you have a detached JPA entity. In order to persist the detached objects in the database you need to re-attach them in to a new persistence context - and that's where the problem begins. Concurrent threads can access the same write method, reading a same entity, modifying it and then writing back the detached entity in the database...

[A] side effect of this trap is that Client A believes the current data persisted in the database is the ones from version 2, but actually it is wrong since the version 3 is currently stored in the database.

Gaucho points out an easy solution to this problem, using JPA's optimistic locking policy:

The default mechanism specified in JPA to avoid inconsistencies is a Version field applicable to the entities through the @Version annotation. Once you included the version field in your entities, you can just invoke the merge operation to re-attach detached objects and the container will handle the versioning for you - simple and easy (and safe).

Optimistic locking is but one of several JPA locking strategies. What techniques do you prefer when re-attaching persistent entities to a persistence context?

In my experience, optimistic locking is usually the best approach. The problem with optimistic locking is how to handle the situation where there is a collision. When I tell people that the second update will be rejected I am often told that the approach is unacceptable.

Pessimistic locking, on the other hand, creates a lot of problems (especially for stateless services) but people tend to be more comfortable with it.

> When I> tell people that the second update will be rejected I am> often told that the approach is unacceptable.

Do any of them offer a stable alternative? Didn't think so. Oracle does optimistic locking (any MVCC database, in fact) by default. Well, it doesn't actually lock. There is a reason Oracle is winning the database war in the Web over SQLServer and DB2. SQLServer now offers, sort of, MVCC semantics. Some folks argue that it isn't, really. But that's splitting hairs. Using code to control concurrency is so, COBOL/1960'S. But coders like that. More power in their hands. And more convoluted systems.

People complain that java is out of control, yet they (not necessarily the same "theys") keeping piling stuff on. Topsy grew that way. Separation of concerns is the mantra, until doing so cedes some concern (i.e. power/control). Bah.

The database controls concurrency. Period. Get over it.

Do the airline seat reservation example. If that doesn't shut them up, get new users; these are beyond help.

> > When I> > tell people that the second update will be rejected I> am> > often told that the approach is unacceptable.> > Do any of them offer a stable alternative? Didn't think> so.

They ask for pessimistic locking.

> Oracle does optimistic locking (any MVCC database, in> fact) by default. Well, it doesn't actually lock. There> is a reason Oracle is winning the database war in the Web> over SQLServer and DB2. SQLServer now offers, sort of,> MVCC semantics. Some folks argue that it isn't, really.> But that's splitting hairs. Using code to control> l concurrency is so, COBOL/1960'S. But coders like that.> More power in their hands. And more convoluted systems.

Can you explain how the database can manage optimistic locking in a stateless web service without any help from the 'code'?

> Can you explain how the database can manage optimistic> locking in a stateless web service without any help from> the 'code'?

Depends. Any "stateless web service", strictly speaking, can't participate in any kind of database locking (optimistic or pessimistic since it is disconnected), without a method of databinding. In the java world, an applet can (but not frequently done) bind to the database if there is an engine on the web server server. (IIRC, DB2 provides a proxy to redirect to a separate physical/virtual machine.)

With a normal servlet, it maintains the resultset, which is updateable. The servlet talks to the browser.

With an MVCC database, this is not a problem. With a traditional locker, it is. Optimistic or pessimistic doesn't matter; having an open active transaction in a locker is the issue.

> > Can you explain how the database can manage optimistic> > locking in a stateless web service without any help> from> > the 'code'?> > Depends. Any "stateless web service", strictly speaking,> can't participate in any kind of database locking> (optimistic or pessimistic since it is disconnected),> without a method of databinding. In the java world, an> applet can (but not frequently done) bind to the database> if there is an engine on the web server server. (IIRC,> DB2 provides a proxy to redirect to a separate> physical/virtual machine.) > > With a normal servlet, it maintains the resultset, which> is updateable. The servlet talks to the browser.

That woud be a stateful servlet. Statefulness has really big negative drawbacks.

> With an MVCC database, this is not a problem. With a> traditional locker, it is. Optimistic or pessimistic> doesn't matter; having an open active transaction in a> locker is the issue.

Again this is statefulness. You might argue that this makes concurrency easier (I'm not sure I agree) but the macro-level problems are not worth the payoff, that I am sure of. It's really easy to implement safe optimistic locking without any database support and without stateful services.

All involved parties must stick to the bargain and update the version column (it's a 'collaborative protocol'). When one program (e.g. because it doesn't use JPA) doesn't comply all others are affected. With 'OptimisticLockType.ALL' you are in full control.

> All involved parties must stick to the bargain and update> the version column (it's a 'collaborative protocol'). When> one program (e.g. because it doesn't use JPA) doesn't> comply all others are affected. With> 'OptimisticLockType.ALL' you are in full control.

Even if you lock on all columns, you are affected when a program doesn't comply.

'OptimisticLockType.ALL' means that all columns (and their original values) are used in the where clause of the update statement. Locks, triggers and collaboration from other programs are not necessary.

> 'OptimisticLockType.ALL' means that all columns (and their> original values) are used in the where clause of the> update statement. Locks, triggers and collaboration from> other programs are not necessary.

However, if the users dogmatically insist on pessimistic locking (because they want to see who's editing something, or balk at the usually tiny chance of having their changes discarded) - I negotiate for using *Leases* instead of pessimistic locking.

That removes a fair chunk of the reliability and clean-up issues (or statefulness if you worship the DB) associated with pessimistic locking. Otherwise you have no guarantee that process the user cancelled actually cleaned out the locks it held (again, unless you go stateful front-to-back, server-to-database).

Unfortunately, support for this isn't quite as pretty in most ORM frameworks, as optimistic locking is. But it beats pessimistic locking.

Once again: if you're wed to application code running the data, you're back to COBOL/VSAM/1970. It doesn't matter whether you've invented the euphemism "optimistic locking in a stateless environment". You're still retrograde.

As one of the earlier posts said: "All involved parties must stick to the bargain and update the version column (it's a 'collaborative protocol'). When one program (e.g. because it doesn't use JPA) doesn't comply all others are affected."

That's no way to run a railroad (recent crashes make it quite clear that depending on individual actors to act to protocol always fails). When you do that, you've just wasted the money you've spent on your industrial strength database. If you're doing MySql, then it doesn't matter, since it is just a sql parser fronting the filesystem, and provides none of the services of a real database (InnoDB possibly excepted).

Again, if you REALLY want your data correct, as the prime directive of your application, the database (or TPM) MUST control concurrency. The fact that many people are web addled doesn't change the semantics of transactions. Read Weikum and Vossen on transactions. What they say applies universally, not just through SQL databases.

The argument that stateless (with respect to the datastore) behaviour can be implemented in anything other than a siloed monolithic application (and even then, with multiple coders and multiple subsystems, good luck) is folly. Dr. Codd demolished that idea 40 years ago.

> Once again: if you're wed to application code running the> data, you're back to COBOL/VSAM/1970. It doesn't matter> whether you've invented the euphemism "optimistic locking> in a stateless environment". You're still retrograde.

It seems to me that you are stuck in the past (40 years ago to be precise.) The importance of statelessness is very basic and essential.

> As one of the earlier posts said: "All involved parties> must stick to the bargain and update the version column> (it's a 'collaborative protocol'). When one program (e.g.> because it doesn't use JPA) doesn't comply all others are> affected."

You can ensure the version field is updated using database triggers. I thought you'd be a big fan of that.

The argument that a solution is not reliable because you might make a mistake in implementing it properly is just rhetoric. It's a logical fallacy no more valid than saying seatbelts don't work because people can decide not to wear them. I could use your solution in my application and implement it improperly. Does that make your solution wrong?

If an application accesses the database and doesn't update the table properly, then that's a bug. A better solution is to not let multiple applications update the database and channel the transactions through a single owner.

> Again, if you REALLY want your data correct, as the prime> directive of your application, the database (or TPM) MUST> control concurrency.

Sorry, but that is just bullshit. A DBMS is software. It's not magical. I've had a to solve a number of critical bugs created by people who believe that everything needs to happen in the database.

> The fact that many people are web> addled doesn't change the semantics of transactions. Read> Weikum and Vossen on transactions. What they say applies> universally, not just through SQL databases.

The standard solution uses the features of the database. The point is that the database can't do it alone. The application must assist.

What exactly is your solution anyway? Do you have any concept of have devastatingly bad your solution would perform in a real web service?

> The argument that stateless (with respect to the> datastore) behaviour can be implemented in anything other> than a siloed monolithic application (and even then, with> multiple coders and multiple subsystems, good luck) is> folly. Dr. Codd demolished that idea 40 years ago.

I've been doing it for more many years. What you don't know is hurting you.