Skip site navigation (1) Skip section navigation (2)

Re: Serialized Access

From: Craig Ringer <craig(at)postnewspapers(dot)com(dot)au>
To: Phillip Mills <pmills(at)systemcore(dot)ca>
Cc: pgsql-general(at)postgresql(dot)org
Subject: Re: Serialized Access
Date: 2008-06-26 02:21:42
Message-ID: 4862FD36.8040607@postnewspapers.com.au (view raw or flat)
Thread:
Lists: pgsql-general
Phillip Mills wrote:
> I'm working on an application that uses EJB3 entities in JBoss, with
> Hibernate and a PostgreSQL database.  One of the entity tables needs
> consistent, synchronized updates to rows in an environment where telling the
> user that their operation failed and starting over is not an option.
> 
> Because it's the default, I've used EJB3's optimistic locking with a
> strategy of catching EJBExceptions and retrying my updates.  Since
> contention can be frequent, the overhead (and extra logic) for this seems
> like a waste.  I would like to try pessimistic locking and compare the
> results, but here's where my problem arises.  EJB documentation passes this
> off to the application server, which considers it a ORM problem.  Hibernate
> says it doesn't add any lock features beyond what JDBC and the database
> provide....
> 
> In the end, I need Java code in a stateless bean that causes serialized
> access to database rows that are under the control of an EntityManager, but
> the approach to doing that is eluding me.

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.

The main thing you need to be aware of is that advisory locks are not
transaction scoped, they're connection scoped. They are held until
explicitly released, or until connection close. You'll have to make sure
to release any locks when you catch persistence exceptions that leave
the connection usable. If the connection is broken you don't need to do
anything special as Pg will release the locks for you.

Advisory locking is specific to PostgreSQL and will not be portable to
other databases.

See:

http://www.postgresql.org/docs/current/static/explicit-locking.html#ADVISORY-LOCKS

I'm going to have a play and see if I can come up with a demo that uses
advisory locking through Hibernate. So far I'm only using it with direct
JDBC calls in some of the trickier bits of the app I'm working on, so I
haven't had cause to combine it with Hibernate yet.

--
Craig Ringer

In response to

Responses

pgsql-general by date

Next:From: Tom LaneDate: 2008-06-26 02:32:45
Subject: Re: ERROR: could not open relation with OID 2836
Previous:From: Craig RingerDate: 2008-06-26 02:12:44
Subject: Re: ERROR: could not open relation with OID 2836

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group