Steps to reproduce

1. Start 1st transaction and execute SELECT .... FOR UPDATE
2. Start 2nd transaction and execute SELECT ..... FOR UPDATE on the same row
3. Now it hangs forever with PostgreSQL by default

It should be possible to configure javax.persistence.lock.timeout to change this behaviour. There are more ways how to configure it.

Using EMF property

properties.put("javax.persistence.lock.timeout", lockTimeout);

This seems to not work at all with PostgreSQL. I tried lockTimeout (Long) values 0 and 1000.

Using Query hint / find properties

em.find(entityType, uuid, LockModeType.PESSIMISTIC_WRITE, Collections.singletonMap("javax.persistence.lock.timeout", lockTimeout))

This works with lockTimeout equal to 0 but nothing else.

Is there some way to make this work with PostgreSQL? Or is this a bug?

Workaround

There is a workaround that makes lock timeout work with PostgreSQL but it requires a custom configuration for each EntityManager created.

    protected EntityManager getEntityManager() {
        EntityManager em = emf.createEntityManager();
        
        if ("postgresql".equals(databaseShortName) || "cockroachdb".equals(databaseShortName)) {
            Long lockTimeout = config.getLong("lockTimeout");
            if (lockTimeout != null) {
                em.unwrap(SessionImpl.class)
                        .doWork(connection -> {
                            PreparedStatement preparedStatement = connection.prepareStatement("SET LOCAL lock_timeout = '" + lockTimeout + "';");
                            preparedStatement.execute();
                        });
            }
        }
        return em;
    }