Hi,

I want to implement a mechanism where I can read a row from a table and lock it, then delete the row so that no other reads will see this row again, and each read is guaranteed to get a unique row.

Here's what I've tried:



LockMode.UPGRADE seems to have no effect in HSQLDB (Multiple threads can get the same row), and when I tried MySQL, the first select just hangs, so I guess I'm not doing the locking correctly, but I'm not sure what the correct way of doing this is.

This 'fetch' operation is done in a transaction with other data access code, so I don't want to change the isolation level of the entire transaction.

If there was a way to tell hibernate to generate the table with exclusive row read locks, that would be fine too, but I'm not sure how to do this either.

Any help is greatly appreciated!!!

Thanks,
John.

Hi John

I ran a couple of tests and can confirm that LockMode.UPGRADE doesn't work in HSQLDB. The documentation for HSQLDB states it doesn't support "select for update" which is probably why it fails.

However, against MYSQL (Ver 14.12 Distrib 5.0.41, for Win32) it works fine.

As an alternative you could lock the rows by updating them e.g.

session.createQuery("update SomeClass set id = id").executeUpdate();

...although this might be expensive if the table is large.

Mike

Thanks Mike,

There was a problem in my test code where the transaction writing the rows into the table was still active when I was trying to perform the "select .. for update" in a different transaction; hence the hang. I eliminated this problem, and it worked fine on MySQL. I also tried H2 instead of HSQLDB, since it supports this type of locking, and it worked there too.

A happy ending to this one :)

John.