Ok. Since the discussion last night I have been doing some thinking, reading and testing. I also had a glance in HIA.

It seems there are two different roots for the problems I am seeing.

The first root is that, for historical reasons, we do not use optimistic locking with versioning or timestamps. When the project started, I was the only one with experience of OO and SQL. It has been a long, and sometimes adventurous, road to move the development from function orientation and "raw" JDBC to OO and proprietary ORM. Adding versioning to the older ("raw") parts of the system would be very cumbersome.

The other root of the problem is the way MySQL implements the repeatable read isolation level. We have to avoid what Hibernate in Action calls the "second lost updates problem" (and as I said, without versioning). HIA claims that repeatable read is a sufficient isolation level for avoiding this problem. Today I found that for PostgreSQL and Mimer SQL indeed it is - PostgreSQL for example gives "ERROR: could not serialize access due to concurrent update" (since PostgreSQL uses serializable for the repeatable read setting). But for MySQL (InnoDB) it is not. To avoid the "second lost updates problem" (without versioning) you have to use explicit pessimistic locking or serializable isolation level (which causes implicit pessimistic locking).

So, as I see it, there are three theoretically possible solutions. Lower the global isolation level to repeatable read and...
1. add versioning/timestamps
2. add explicit pessimistic locking where needed
3. increase isolation level to serializable (and thus adding implicit pessimistic locking) where needed

Number 1 is probably out of the question, since we would have a lot of redesigning to do. (The app need redesign anyway, but it will have to wait for the next generation).

Number 2 is quite feasible. Since I have marked all updating methods (i.e. methods that need more isolation) with annotations (used for AOP) and all JDBC calls go through a wrapper, I could add "FOR UPDATE" in this wrapper. BUT this adds problems should we start using Hibernate. Then we would either have to start adding locking explicitly on every lookup (which the project manager wants to avoid), or find a way to intercept Hibernate lookups and add locking (which should be possible with AOP, but it seems Hibernate Interceptor is not of much help).

Number 3 would probably be the easiest to implement, but I'm not sure it would actually work (since there might be database access before the isolation level change in some instances).
Any thoughts here will be appreciated.

Essentially this seems to be more of a MySQL issue than a Hibernate issue, and I would like to know how other users of MySQL have resolved this. But when Googling for "second lost updates problem" all I get is the HIA index at Manning.

So my bottom line question is: are there any other names for the "second lost updates problem", that I can use for searching more info?

Can your explain your use case ? Versioning is used to detect conflict for "dettached" data in web applications, this "problem" exists with any isolation level. As I understand it is not related to transaction serialization.

Short story: we have a lot of different object, such as invoices, that may be edited by the user of a web application. When they are "finished" a flag is set in the database (and a lot of other things are triggered). It is absolutely necessary that two users can not concurrently finish for example the same invoice.

For this we are currently using serializable isolation level. Thus we read the object (which gives us an exclusive lock), make sure it is not yet finished, update and save. If there are concurrent transactions, the last one will have to wait until the first one commits and thus sees the finished flag is already set.

Using serializable for all operations has caused problems since we have some really slow ones (some recent even 30 min+) which gives transaction timeout/deadlock if you try to do something else simultaneously.

The solution we are currently testing is to avoid (JTA) transactions (or use auto-commit, as some might put it) for all read only operations. But as I have recently learned Hibernate requires that there is a (non auto-commit) transaction even around read only operations. So to keep the door to Hibernate migration open, I want to find a way to get rid of the deadlocks that is also conceptually correct (or shall we say "Hibernate compatible").

You need versionig for this stuff, it is too expensive to keep transaction "open" in web applications for "edit session" with any isolation level.

BTW you can keep versions in Map without any changes in model, but it will not work in cluster.

Surely I don't mean that we keep the JTA/JDBC transaction open during user interaction. When the user posts, we re-read the object from DB to verify the flag has not been changed since page view. (I know there are other problems around this resolved by versioning, which we don't need to address here).

The problem is when two users post a "finish" concurrently; that's when we need exclusive locks to make sure only one of them actually finishes.

Hi mate.

Did you solve your problem?

I have a similar situation in which varios concurrent users acces the same transaction and the same objects and I want they wait until the previous finish their work.

I've used the SERIALIZABLE isolation level but I get a deadlock timeout when the second transaction tries to lock the object.

I've tried against a DB2 database

Thanks