I'm using Postgres 9.3.5 and recently updated the hibernate from 3.2 to 4.3.11.
As a result I can't run "SELECT... FOR UPDATE OF" queries, and simply 'select.. for update' is not enough in my case since it returns
could not extract ResultSet. Reason: ERROR: FOR UPDATE cannot be applied to the nullable side of an outer join
The criteria I'm trying to use looks like this:
Criteria criteria = session.createCriteria(objectType).add(Restrictions.eq("name", objectName).ignoreCase());
I'm using the following locking:
in 3.2: criteria.setLockMode(LockMode.UPGRADE);
in 4.3.11: criteria.setLockMode(LockMode.PESSIMISTIC_WRITE);
I have an hierarchy of hibernate (& DB) objects which cause the hibernate perform several joins while constructing the above query. the 'objectType' is a joined-subclass of the main class
<class name="BaseObject" table="BASE_OBJECTS">
While using hibernate 3.2 the final query (taken from Postgres logs) ended with: "for update of this_2_"
(when this_2_ is the alias given by hibernate to the main table (BaseObject) mapped in hbm.xml file)
After upgrading to 4.3.1.1 the same query returns the above mentioned exception. which means the final query performed as for update (without the name of the table on which to perform the lock)
After an extensive look of the web I could find only that the "for update of" in hibernate with Postgres is not supported any more?
[https://hibernate.atlassian.net/browse/HHH-5654][2]
It seems very unlikely since it's quite an important sql feature and a big degradation in usage.
Am I missing something here?
02.09.15:
I'll try to clarify myself:
using an example given in the hibernate documentation at
https://docs.jboss.org/hibernate/orm/3.5/reference/en/html/inheritance.html
class name="Payment" table="PAYMENT">
<id name="id" type="long" column="PAYMENT_ID">
<generator class="native"/>
</id>
<property name="amount" column="AMOUNT"/>
...
<joined-subclass name="CreditCardPayment" table="CREDIT_PAYMENT">
<key column="PAYMENT_ID"/>
<property name="creditCardType" column="CCTYPE"/>
...
</joined-subclass>
<joined-subclass name="CashPayment" table="CASH_PAYMENT">
<key column="PAYMENT_ID"/>
...
</joined-subclass>
<joined-subclass name="ChequePayment" table="CHEQUE_PAYMENT">
<key column="PAYMENT_ID"/>
...
</joined-subclass>
If I want to perform something like: select p from Payment p where id=1
Hibernate will perform an outer join (on the key) on all tables .
Adding a lock (.setLockMode(LockMode.PESSIMISTIC_WRITE)) will lock the lines on the four tables (as 'For update'), instead of only on table "Payments" ('for update of p') - which did happen in hibernate 3.2
So what We have, is that Something which was supplied earlier by hibernate, is not working any more, using their own mapping examples?
Thanks in advance Marina
select for update
on a query using joins, let alone using outer joins. – a_horse_with_no_name Sep 1 '15 at 9:25