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
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