Hi there!

I'm trying to lock an object which i've read from the db so no one else can modify it, while i'm working on it.

I tried the following:

collection = hibernateSession.filter(myObject.getCollection(),
"where this.date >= ?", dateFrom, Hibernate.DATE);

Iterator iter = collection.iterator();

Transaction tx = hibernateSession.beginTransaction();
while (iter.hasNext()) {
session.lock(iter.next(), LockMode.READ);
}



Now when i debug the code to the point right after the while loop, open Enterprise Manager (we're using SQLServer) i can still modify the corresponding records.

I tried LockMode.UPGRADE too, didn't work either :-/

What am i doing wrong, or have i misunderstood the whole concept?

kind regards,

Torsten

Hey Torsten,

You have indeed misunderstood the use of LockMode. It's for controlling how and when hibernate attempts to lock the database during an object's lifetime. It looks like you've just told hibernate to lock all the rows for those objects you've loaded, guaranteeing that anyone will be able to change them later :)

There is a way to get a read-only session where no objects can be modified loaded via the session. It's normally used for perforamance improvements in cases where you just want to display data, but it may be useful for you as well. Unfortunately for the life of me I can't remember or find how to do it, I'll keep my eyes open though and post here if I remember.

Matt

Turns out what I was remembering was session.setFlushMode(FlushMode.NEVER) which speeds things up by not checking the session against the DB, but doesn't actually protect against updates happening in the future if you made an explicit session.flush().

So my suggestion to you is to make a cloneReadOnly() method in that object which produces an object that has no setters and that you call that on each of the results and pass that new list along.

Make sense?

Matt

thx for your assistance, but this not really going to help me.

What I'm actually trying to achieve is to lock the record in the db. I want to make sure that no one else (with a different hibernate session) modifies the data while i'm working on the object.

Is there any way to do this with hibernate?

I am also using SQL Server, and found that the locking does work between sessions. However, I also found that if I modified the data using the EM it would lock out my java and change it, thereby throwing out an exception in my code.

Strangely enough, there have been one or two rare occasions, where the locking did in fact stop me saving changes in a table (In EM) that was simply being queried and not updated.

If you really want to test, write a simple stand-alone application, that you can run from the DOS-prompt while you are debugging. Have it try to access/modify the same data you have in your code above using the same Hibernate mapping, etc.. It should output that the data is locked.

Try that and see what happens.

-G

great idea! i'll try that now!

thx!

SQL Server does not support the "FOR UPDATE" syntax. However, I've recently learned that it has some "funny" syntax that we might support in future.

I just don't get it to work :-/

I wrote a standalone application that tries to modify data using hibernate and it still can.

I don't understand it. Is there some special trick to it?

Or is it because i didn't read the records from the db with session.find() but used session.filter() on a lazy collection instead? Maybe you can't lock those.

Could someone please post a peace of code here how to lock the corresponding records for objects in a collection that you got by running session.filter().

It would be really appreciated.

thx for your response, gavin.

does that mean there's no way to lock records on the SQL-Server?

Exactly. SQL Server is not a database that emphasizes the use of explicit pessimistic locks. This is quite common.

All right. I'll have to find another solution then. Thx a lot for the info!

Btw. while I'm here, let me thank you for the good work you've done.

Hibernate is really great!

We're working with it for 2 months now, and never ran into unsolvable problems although we have quite a complex db schema represented by 56 classes. It has many n:m relations and uses table-per-class-hierarchy as well as table-per-subclass-hierarchy.
We're running cascading updates and deletes and everything works fine.
We keep on discovering nice features all the time. Just this week we found out about the object arrays that Hibernate queries can return for example. This is amazing!

So thanx again and keep up the good work. I'm keen to see what the next versions of hibernate will be capable of!

MSQL server does support pessimistic locking, but not with the "FOR UPDATE" syntax.


The magic words would be

"Select * from MyTable with (UPDLOCK) where A = ...

The lock lasts until the transaction is comitted and the select statement is closed.

yeah, I think we should add support for this syntax.

Hi,
I checked release notes for newest versions and didn't found it.

Was this implemented?


Thanks

[quote="richard"]
"Select * from MyTable with (UPDLOCK) where A = ...
[/quote]

I think better would be (XLOCK).
UPDLOCK wouldn't block read access.

I read somewhere that you should even use the additional hint HOLDLOCK, to ensure that the lock is only release on the end of the transaction.

Select * from MyTable with (HOLDLOCK, XLOCK) where A = ...