I'm using Hibernate 3 with a MySQL 5.1 server.

Hibernate seems to be ignoring my request to use pessimistic locking. I'm testing this by having two threads run the below code with different delays before and after reading from the db (thread 1: 1 sec then 4 secs; thread 2: 2 secs then 1 sec):

Session session = HibernateUtil.openSession();
Transaction transaction = session.beginTransaction();
Thread.sleep(getDelay1InSecs()*1000);
//this line should obtain a pessimistic lock
UnconfirmedUser user = (UnconfirmedUser)session.get(UnconfirmedUser.class, (long)1, LockMode.UPGRADE);
Thread.sleep(getDelay2InSecs()*1000);
user.setPassword(user.getPassword()+getThreadId());
transaction.commit();
session_.close();

I would expect this code to append the thread id's from both threads to the end of the existing password. This is not happening; the password only has the id of the last thread to commit appended to it (i.e. I have a lost-update problem).

It's definitely not a MySQL problem: All my db tables are using the InnoDb engine (which supports transactions and row-level locking) and I've confirmed that select ... for update does block as appropriate by manually opening two mysql consoles and doing the equivalent test in sql.

Do I need to do more than set the LockMode to UPGRADE?

I've searched forums all over the place. I really hope somebody can help.

P.S. This is the sysout:
Hibernate:
select
unconfirme0_.id as id12_0_,
unconfirme0_.displayName as displayN2_12_0_,
unconfirme0_.emailAddy as emailAddy12_0_,
unconfirme0_.confirmationCode as confirma4_12_0_,
unconfirme0_.password as password12_0_
from
UnconfirmedUsers unconfirme0_
where
unconfirme0_.id=?

Isn't it meant to say "for update" at the end?

try locking the object instead of class. for example,

session.lock(object, LockMode.UPGRADE);

I've tried that too - but to no avail.

I've also tried
System.out.println(session.getCurrentLockMode(user));
to confirm that the object is being placed in the correct lock mode (LockMode.UPGRADE) and it is.

Thanks though .

Anybody else have any ideas?

ping

I was wondering about this myself, but in terms of a db that does not support row locking ie ISAM. The hibernate documentation says that it will Does anyone have any experience with this?