Too aggressive pessimistic write lock in PostgreSQL

Hibernate ORM
1

Hi,

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_WRITE on 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?

Thanks

2

That’s a good observation.

There are two ways we can address this:

  1. We could add a Hibernate configuration property which will allow Hibernate to use NO KEYS when doing a PESSIMISTIC_READ or PESSIMISTIC_WRITE.
  2. We could add a new LockOptions.NO_KEYS for this.

I created the HHH-13135 Jira issue for this.

3

Hi,

Bumping this old topic, is HHH-13135 still considered or is it too postgres specific to be integrated ?

Thanks