Hi,

I'm trying to lock a row for update in a mysql db (v4.1 , type InnoDB):

Ond ond = (Ond)sess.load(Ond.class, ondVAF.getOndnr(),LockMode.UPGRADE);
...
sess.close();


this results in a sql query that should (?) lock this row:

17:15:23,150 INFO [STDOUT] Hibernate: select ondnr, version, bdrcd, afdcd, ondom, ondst from ond where ondnr =? for update

But the row is not locked, for i can select it for update with another webpage in my app, before the update in the first page is finished.

Does this type of locking work at all? If so, what am doing wrong?

TIA.

I've been experimenting with this and had a similar problem. You need to set the global or local MySQL variable tx_isolation for those types of locks to actually work when you use the appropriate type of LockMode. See:

http://www.mysql.com/documentation/mysq ... tion_model

Specifically, read section 7.5.9.1. I've tried it manually but not done it with Hibernate yet. I think the hibernate variable in
hibernate.properties is called "hibernate.connection.isolation=whatever"
or "connection.isolation" as the name in the hibernate.cfg.xml file.

Regards,
David

Thank you, I actually got it working, but i'm not sure it is the best way.

I've set the global transaction level in mysql to read committed.

The code when a user selects something to update:



I'm using Threadlocal for the session:



When the user submits the updated data:




The result is that the record is locked and that when a second user selects a record to update that a first user already is updating, the second user has to wait until either the first user commits his update or the lock timeout is reached, then a sqlexception is thrown.

Now, to achieve this , i have to keep the session open for as long as the user is updating the record. I have tried to use session.disconnect() at (1 <-- ) in the first code snippet and session.reconnect() later on, but that doesnt work; the record is no longer locked.
Is the the way i'm doing it now actually considered bad practice, is there a better way?

Your post is unclear about something: are you trying to ensure read access to the records while someone is holding it for writing? There are other MySQL transaction isolation levels (I believe InnoDB has 4 levels) which should allow for this, IF you are using session transactions.

To write-lock individual records between disconnected sessions, you're out of luck. If you keep your session open and in the client's HTTPSession, you eat up database connections and risk timeouts. Your best bet is probably to setup a "locking table" to keep track of which records are checked out by each person. That way, the record will be readable for everyone. When you want to update it, you can check your "locking table" to see if you 'checked' it out before writing. If you do this, be sure to add some sort of release mechanism: a manager who can see and remove locks, a function to see if the lock is XXX minutes/hours old and release it when someone else tries to save it, etc.

Regards,
David