Re: Serialized Access

Phillip Mills wrote:
> On Wed, Jun 25, 2008 at 10:21 PM, Craig Ringer
> <craig(at)postnewspapers(dot)com(dot)au <mailto:craig(at)postnewspapers(dot)com(dot)au>> 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