When acquiring a
PESSIMISTIC_WRITE on an object Hibernate executes a SELECT … FOR UPDATE. This kind of lock, at least in PostgreSQL, is too aggressive because it prevents insertion of new rows in other entities if these new rows have a foreign key to the locked object. Note this problem is database specific and, for example in Oracle, it does not occur.
PostgreSQL introduced back in 2013 in its 9.3 version a new lock: SELECT … FOR NO KEY UPDATE; which relaxes the lock allowing insertions with foreign keys to locked rows.
Note current behavior can cause severe contention problems.
I’ve created a simple reproducer, here (locking branch), which:
- Defines a simple model: a bank account which has deposits
- Thread T1 creates a
PESSIMISTIC_WRITEon Account 1, and holds it for 2 seconds
- Thread T2 inserts a deposit in Account 1
In Oracle, T2 finishes as expected before than T1. But in PostgreSQL, T2 needs to wait till T1 has finished in order to be acquire the lock T1 had.
I also noticed H2 database works as PostgreSQL, but I’m not sure if it has an equivalent less heavy lock. Neither I do for other databases.
Shouldn’t this lock be relaxed, at least in those databases supporting it, to work in the way Oracle does?