Issues with Record Locking

["Hawley, Dan" <da...@lmco.com>]

Hi,

I seem to need some help understanding how record locking works in Derby because I can't seem to make it work for me.

I have a single table with about 5,000 records.  There is an Ocean field that has one of two values: ATL or PAC.  I want the users to be able to query the table using the Ocean attribute and obtain a lock on all of the "ATL" records from the time the select statement is issued until they end their editing session and finally update the database.  The lock could be need for several hours.

I am using Derby in network server mode on my local machine.  I am using JPA to access the database.  I am using Hibernate Persistence as my JPA provider.  In the persistence.xml file I set the following property:

<property name = "hibernate.dialect" value="org.hibernate.dialect.DerbyDialect" />
<property name = "hibernate.connection.isolation" value="4" />

When I start the session, it appears that a repeatable read connection is made to the database.

There is an index created on the ocean field in the class using JPA with hibernate extensions:

@Column(name = "OCEAN", nullable = "false");
@org.hibernate.annotations.Index(name = "ocean", columNames="OCEAN"))

I issue the following select statement to retrieve the records from the database and hopefully obtain the lock:

Public ArrayList<FleetExchangeActivity> getActivities(Ocean ocean, boolean isReadOnly){
  Query q = this.getEntityManager().createQuery("select a from FleetExchangeActivity a where a.ocean = :ocean").setHint("org.hibernate.readOnly", isReadOnly);
q.setParameter("ocean", ocean);
return (ArrayList<FleetExchangeActivity>) q.getResultList();

}

I use the isReadOnly boolean to indicate whether the user wishes to view ( no lock) or edit (lock please) the result set.

I am missing something here?  When I run a test and query all the records with ocean=ATL, then run the same query again, using the same EntityManager object and the same ocean attribute, I am able to update the records returned by the second query.

Any help you can provide will be greatly appreciated.  Thanks, Dan



Dan Hawley
Lockheed-Martin Space Systems Company, Sunnyvale, CA
Business Application Programmer, Sr. Staff
Program Plans and Requirements O/B31S
B/181 Col 1A3
Office: (408) 742-3888

Re: Issues with Record Locking

["Dag H. Wanvik" <Da...@Sun.COM>]

Sylvain Leroux <sl...@wanadoo.fr> writes:

> Depending on your needs, you may be interested in more informations
> about Derby's Locks:
> http://db.apache.org/derby/docs/10.0/manuals/develop/develop74.html

For the record, the current locking docs are here:

http://db.apache.org/derby/docs/10.5/devguide/cdevconcepts36402.html

Only minor changes, though.

Dag

RE: Issues with Record Locking

["Hawley, Dan" <da...@lmco.com>]

Hi Sylvain,

Thanks for your excellent response to my request for help.  I too am worried that I am confusing Hibernate and Derby locks.  My goal is use derby locking because we will have multiple desktop applications accessing the derby network database so it seems to me the best place to lock records is at the source.

I definitely think I need to look at the sql statements that are being generated by hibernate and see if they look correct for what I am trying to accomplish.  My major concern was that I was asking Derby to do something that it couldn't handle.  It sounds like, from your response, that what I am trying to accomplish is possible.

Thanks again for the advice!!!  Dan


-----Original Message-----
From: Sylvain Leroux [mailto:sl20@wanadoo.fr]
Sent: Thursday, September 03, 2009 1:15 AM
To: Derby Discussion
Subject: Re: Issues with Record Locking

Hawley, Dan a écrit :
> Hi,
>
> I seem to need some help understanding how record locking works in
> Derby because I can't seem to make it work for me.
[...]
> Any help you can provide will be greatly appreciated.  Thanks, Dan

Hi Dan,

Well, if "any" help will be appreciated, I take my chance!
That's quite a long time I haven't used Hibernate. So I'm a little bit
rusty. But, I am wondering if you don't mix up two things:
* Database locks
* Hibernate locks

As far as I know, database locks allow one to prevent other transactions
to access some rows from a table while you are holding it.
With Derby, you may achieve such exclusive access by using
a LOCK TABLE statement (among others). This is not recommended if you
intent to hold the lock for a long period of time.

Depending on your needs, you may be interested in more informations
about Derby's Locks:
http://db.apache.org/derby/docs/10.0/manuals/develop/develop74.html


In the other hand, as far as I remember, Hibernates locks are something
else: they will prenvent the modification of the same persistent objet
in two different contexts.
By default, Hibernate use optimistic locking. Only when a dirty object
(i.e.: an object you have modified) is write back to the DB, Hibernate
is able to say: "Hey, I can't do that: Someone has changed it in your
back!".


Concerning your specific problem, in theory,
'setHint("org.hibernate.readOnly", true)' will prevent Hibernate to
"persist back" the object to the DB. I don't think this will change the
SQL query in any way. Moreover, in your case, I guess your second query
will hit the cache, returning the same "updatable" objects than the
first one. Once again, this is only a guess. And I couldn't say if this
a bug or a feature.

At least, I think this is more of an Hibernate than a Derby issue.


Finally, maybe I totally missed the point here ;) So if you think this
is definitively a Derby problem, please log the SQL query issued by
Hibernate: this will give us a better understanding of what's going on
there.

Anyway, let us know if you solved your problem.


Hope this helps at least a little,
Sylvain.

--
Website: http://www.chicoree.fr

Re: Issues with Record Locking

[Sylvain Leroux <sl...@wanadoo.fr>]

Hawley, Dan a écrit :
> Hi,
> 
> I seem to need some help understanding how record locking works in
> Derby because I can't seem to make it work for me.
[...]
> Any help you can provide will be greatly appreciated.  Thanks, Dan

Hi Dan,

Well, if "any" help will be appreciated, I take my chance!
That's quite a long time I haven't used Hibernate. So I'm a little bit
rusty. But, I am wondering if you don't mix up two things:
* Database locks
* Hibernate locks

As far as I know, database locks allow one to prevent other transactions
to access some rows from a table while you are holding it.
With Derby, you may achieve such exclusive access by using
a LOCK TABLE statement (among others). This is not recommended if you
intent to hold the lock for a long period of time.

Depending on your needs, you may be interested in more informations
about Derby's Locks:
http://db.apache.org/derby/docs/10.0/manuals/develop/develop74.html


In the other hand, as far as I remember, Hibernates locks are something
else: they will prenvent the modification of the same persistent objet 
in two different contexts.
By default, Hibernate use optimistic locking. Only when a dirty object
(i.e.: an object you have modified) is write back to the DB, Hibernate
is able to say: "Hey, I can't do that: Someone has changed it in your
back!".


Concerning your specific problem, in theory,
'setHint("org.hibernate.readOnly", true)' will prevent Hibernate to
"persist back" the object to the DB. I don't think this will change the
SQL query in any way. Moreover, in your case, I guess your second query
will hit the cache, returning the same "updatable" objects than the
first one. Once again, this is only a guess. And I couldn't say if this 
a bug or a feature.

At least, I think this is more of an Hibernate than a Derby issue.


Finally, maybe I totally missed the point here ;) So if you think this
is definitively a Derby problem, please log the SQL query issued by
Hibernate: this will give us a better understanding of what's going on 
there.

Anyway, let us know if you solved your problem.


Hope this helps at least a little,
Sylvain.

-- 
Website: http://www.chicoree.fr