Re: Serialized Access

Phillip Mills wrote:
> On Wed, Jun 25, 2008 at 10:21 PM, Craig Ringer 
> > wrote:
> 
> 
>     You might want to look into advisory locking. If your locks don't need
>     to be longer than the life of an active EntityManager session then you
>     can probably just issue a native query through the EntityManager to
>     acquire the lock before doing anything more.
> 
> 
> Thank you very much for this and the link.  (I'm much more an OOP 
> programmer than a DB programmer.)  Too bad about the non-portability, 
> but I suppose it had to be.

Not necessarily. Your other option is to use SELECT ... FOR UPDATE to 
explicitly lock the records you are interested in. This should be quite 
portable - at least to any database you might actually want to use. I've 
had no cause to test that in practice, though.

SELECT ... FOR UPDATE takes locks that are automatically released at 
transaction commit or rollback. That's a rather significant advantage to 
my mind.

Another option is a table-level lock using LOCK TABLE . You have a 
variety of exclusion levels available, so you don't have to lock out 
read only transactions if you don't want to. LOCK TABLE probably isn't 
very portable at least in the details of its locking options and 
behaviour. Like SELECT ... FOR UPDATE, LOCK TABLE locks are released at 
transaction commit/rollback.

Whether advisory locking, table-level locking, or SELECT ... FOR UPDATE 
is more appropriate depends a lot on the details of your app's needs and 
how well each approach works with your tools.

With JPA1, in all three cases that's "not very well" regarding tools 
suppport. I think you can convince Hibernate to use SELECT ... FOR 
UPDATE for pessimistic locking, so it should theoretically be possible 
with Hibernate EntityManager as well so long as you don't mind some 
Hibernate specific code. Table level locks are just an easy native query 
through the EntityManager, and advisory locks shouldn't be any harder. 
You could also issue SELECT ... FOR UPDATE queries on the data of 
interest through the native query mechanism if you can't or don't want 
to use Hibernate's support.

In all three cases you need to watch Hibernate's session lifetimes very 
carefully. With advisory locks you also need to keep a careful eye on 
post-transaction cleanup, because if you end up returning a connection 
that holds locks to the connection pool after a commit or rollback 
you'll be in deadlock central (and a debugging nightmare).

It's hard to say what the best approach is with the limited amount of 
information available. Maybe you could describe your problem in a little 
more detail?

--
Craig Ringer