Hi All,

Firstly let me say that I am new to hibernate so if i haven't provided enough information please let me know. I am using hibernate (version 3.2.3) with DB2 (version 9.1) and WebSphere 6.1. I have configured the hibernate environment configuration file and I am retrieving and updating data using hibernate. I am now attempting to read records with pessimistic locking, but its not working. Before I go on here are some details from the configuration file :


<property name="show_sql">true</property>
<property name="dialect">org.hibernate.dialect.DB2Dialect</property>
<property name="hibernate.transaction.factory_class">
org.hibernate.transaction.CMTTransactionFactory
</property>
<property name="hibernate.transaction.manager_lookup_class">
org.hibernate.transaction.WebSphereTransactionManagerLookup
</property>

<property name="jta.UserTransaction">
jta/usertransaction
</property>

The code which is being executed looks like this :


session = MyHibernateHelper.getSessionFactory().openSession();

tx = session.beginTransaction();

MfSyncControlHModelElement syncControl = null;

// Search Statement 1: Want a Select for update to be executed.

syncControl =
(MfSyncControlHModelElement) session.get(MfSyncControlHModelElement.class, propertyName);

session.lock(syncControl, LockMode.UPGRADE);


//.. do some stuff and calculate new property value
//.. now want to update the same property calue

//.. Search Statement 2: Using this to re-sync object
syncControl =
(MfSyncControlHModelElement) session.get(MfSyncControlHModelElement.class, propertyName,
LockMode.UPGRADE);

// update property value
syncControl.setPropertyValue(propertyValue);
session.update(syncControl);


tx.commit();
session.close();


I expected Search Statement 1 to issue a SELECT FOR UPDATE command (enforcing an exclusive row lock), however the following SQL was executed:

select mfsynccont0_.PROPERTY_NAME as PROPERTY1_1_0_, mfsynccont0_.PROPERTY_VALUE as PROPERTY2_1_0_ from TAC.MF_SYNC_CTRL mfsynccont0_ where mfsynccont0_.PROPERTY_NAME=? for read only with rs

This is not that same as a SELECT FOR UPDATE command. Our DBA has confirmed that a SELECT FOR UPDATE has not been executed. I have found some old user queries which indicated that there may be a problem with the DB2 driver and how DB2 manages its cursor, which dictates UOW. However, I still think that the SELECT FOR UPDATE should have been executed. So really, I am not sure where the problem is:

Have I set up the hibernate config correctly ?
Is the hibernate code OK ?
Is it a DB2 driver thing ?

I have been stuck on this issue for the last few days and I don't think I getting very far with it. Any help or feedback would be greatly appreciated.

Regards.
Chris

Hi,

by default hibernate will use your databases standard-lockmode. get() or load() won't explicitly read "FOR UPDATE".
So you might
1.) Set the isolation-level to some higher level for the whole apllication (which I'm pretty sure you don't want to do).
or
2.) Use session.lock(...) to explicitly lock certain objects where needed, which will result in a SELECT FOR UPDATE-Statement.

Thanks for the reply piet.t.

From the code in my original I'm already doing the session.lock(..)

// Search Statement 1: Want a Select for update to be executed.

syncControl =
(MfSyncControlHModelElement) session.get(MfSyncControlHModelElement.class, propertyName);

session.lock(syncControl, LockMode.UPGRADE);

Have I got it in the correct order (pretty sure seen this in examples everywhere) but is having the lock after the session.get correct ?

Hasn't the record already been read in session.get

Again thanks for the feedback.

I've found what I believe is a bug in the DB2Dialect which uses the incorrect syntax for Derby (a subclass Dialect). If DB2 is expecting the same "for update" syntax as does Derby, then using the LockMode.UPGRADE won't work for you either.

Check out my earlier post about Derby to see if it matches your experience, and if so, then we can make a case for changing the DB2Dialect.

http://forum.hibernate.org/viewtopic.ph ... 02#2356502