On Wed, Jun 25, 2008 at 9:21 AM, Phillip Mills <pmills@systemcore.ca> 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.
Until you benchmark it for your app you really don't know how
inefficient it really is compared to pessimistic locking.
And what extra logic is needed to retry the failed transaction, or do
you mean the logic in the app / jdbc, and not in postgresql. Because
the logic in pgsql should pretty much be to just run the whole
transaction over again. Since there's usually only one db but
multiple web servers, having the web servers work a little harder is
scalable, while doing anything that slows down the db will cost
everyone.
But I'm not really sure what exactly you're trying to do, so I don't
really know what to advise on this one. But definitely use the
serializable transaction level with retry for a benchmark to see how
it scales under your contention conditions. Just to have a baseline to
compare to.