JPA Pessimistic Lock attempt never times out

Question

I'm trying to use Pessimistic locking in JPA, over Hibernate 3 against a Postgres Database. I can't get the lock to time out - it just seems to hang forever.

Here's an example:

EntityManagerFactory factory; 

// (initialise the factory )

EntityManager em1 = factory.createEntityManager();
EntityManager em2 = factory.createEntityManager();

// em1 gets a lock

EntityTransaction transaction1 = em1.getTransaction();
transaction1.begin();
MyObject object1 = em1.find( MyObject.class, 1, LockModeType.PESSIMISTIC_READ );

// em2 tries for a lock

Map<String,Object> timeoutProperties = new HashMap<String,Object>();
timeoutProperties.put("javax.persistence.lock.timeout", 5000);

EntityTransaction transaction2 = em2.getTransaction();
transaction2.begin();
MyObject object2 = em2.find( MyObject.class, 1, LockModeType.PESSIMISTIC_READ, timeoutProperties );

// After five seconds I expect em2 to bail out, but it never does.

transaction1.rollback();
transaction2.rollback();

As I understand it, em2 should have tried for up to five seconds (5000ms) to get the lock and then should have thrown an exception. Instead the code becomes deadlocked.

If I run this in two different threads then I see that thread2 (with em2) gets the lock as soon as thread1 (em1) releases it. So the locking is happening, just never timing out.

I get the same effect with PESSIMISTIC_WRITE, and with any timeout value (2ms, 0ms 'NO WAIT') etc.

I'm using Hibernate 3.6.10 Final (the latest Hibernate 3 version) and Postgres jdbc driver 9.2-1003.jdbc4 (the latest driver). I'm running against a Postgres 8.4 database.

All the documentation I've found suggests this should work. Any ideas?

thanks, Alastair

score 11 · Accepted Answer · answered Nov 08 '13 at 03:06

11

Postgres SELECT for update syntax only provides the options to not wait if a lock can not be obtained right away. See postgres docs.

To prevent the operation from waiting for other transactions to commit, use the NOWAIT option. With NOWAIT, the statement reports an error, rather than waiting, if a selected row cannot be locked immediately. Note that NOWAIT applies only to the row-level lock(s) — the required ROW SHARE table-level lock is still taken in the ordinary way (see Chapter 13). You can use LOCK with the NOWAIT option first, if you need to acquire the table-level lock without waiting.

When working with postgres I have observed that any value over 0 for the timeout will cause hibernate to issue SELECT FOR UPDATE but when timeout is 0 it will issue SELECT FOR UPDATE NO WAIT

answered Nov 08 '13 at 03:06

ams

60,316
68
200
288

While not the answer I was hoping for, that does seem consistent with what I've seen - thanks! – Alastair Nov 09 '13 at 11:07
1

If you dont set javax.persistence.lock.timeout to zero in the properties file, the query is executed only with "FOR UPDATE". If you set it to zero (as I said), the "FOR UPDATE NOWAIT" is executed. – Marcio J Dec 11 '13 at 12:15
At least in Hibernate 4.3.x & JPA 2.1 you can specify these settings on a query-level. Both in a @NamedQuery (via hints property) and in a query created via entityManager.createQuery(CriteriaBuilder.createQuery(Entity...)) using setHint(...). – gkephorus Oct 17 '14 at 07:31
1

@Lock(value = LockModeType.PESSIMISTIC_WRITE) for me makes hibernate add "FOR UPDATE" but setting props.setProperty("javax.persistence.lock.timeout", "0"); does not add the NOWAIT. I've tried injecting the EntityManager and querying it also returns 0, em.getProperties().get("javax.persistence.lock.timeout"). I'm trying raw sql, but then hibernate doesn't wrap the exceptions appropriately. – Joel Jul 10 '15 at 20:32
@Joel i found that this is due the Dialect used, if you use the proper Postgres-Dialect this Property is honored and translated into `NOWAIT` – hotzen Oct 16 '18 at 10:04
@Joel same problem happening with me. I tried many things but javax.persistence.lock.timeout doesn't seem to be working in any case. I have two transactions running parallelly. First trasanction acquires pessimistic lock and wait for some time before it releases the lock. Meanwhile when second transaction tried to get lock on same row of table then instead of waiting, it thows error that lock cannot be aquired. – old_soul_on_the_run Nov 24 '19 at 16:37

score 0 · Answer 2 · answered Oct 30 '13 at 17:21

0

Put this in your persistence.xml:

<property name="javax.persistence.lock.timeout" value="0"/>

Or set the property before calling the first lock.

answered Oct 30 '13 at 17:21

Marcio J

673
7
17

Hi Márcio, thanks but that made no difference either as a persistence.xml variable or as a property on the first lock. – Alastair Nov 01 '13 at 10:17

score 0 · Answer 3 · answered Nov 24 '19 at 20:12

javax.persistence.lock.timeout doesn't to be working for me either when provided like below

@QueryHints({@QueryHint(name = "javax.persistence.lock.timeout",value = "15000")})

But then I tried something else which worked. Instead of using @Repository and using CrudRepository, now I am configuring my hbernate using entity manager. Used createQuery along with lock and setting lock timeout. And this configuration is working as expected. I have two transaction running in parellel and trying to lock exact same row in DB. First transaction is able to acquire WRITE lock and holds the lock for around 10 secs before releasing lock. Meanwhile, second transaction tries to acquire lock on same row but since javax.persistence.lock.timeout is set to 15 secs, it waits for lock to be released and then acquires its own lock. Hence making the flow serialized.

@Component
public class Repository {

    @PersistenceContext
    private EntityManager em;

    public Optional<Cache> getById(int id){
        List<Cache> list = em.createQuery("select c from Cache c where c.id = ?1")
                            .setParameter(1, id)
                            .setHint("javax.persistence.lock.timeout", 15000)
                            .setLockMode(LockModeType.PESSIMISTIC_WRITE)
                            .getResultList();


        return Optional.ofNullable(list.get(0));
    }

    public void save(Cache cache) {
        cache = em.find(Cache.class, cache.getId());
        em.merge(cache);
    }
}

JPA Pessimistic Lock attempt never times out

3 Answers3

Linked