RE: [Hibernate-devel] RE: Pessimistic locking?

[Steve <st...@ba...>]

> -----Original Message-----
> From: Paul Szego [mailto:pau...@ne...]
> 
> Steve Bate wrote:
> > I prefer to have the pessimistic locking supported at load time --
> > either by the query language or through the persistence
> > API for loading objects. Allowing explicit lock upgrades after
> > loading would be nice for flexibility, but it also can lead to
> > deadlocks in the database if multiple objects are being loaded
> > and modified in a transaction.
> 
> Deadlocks are nothing new. Databases have deadlock detection, and 
> there's a mountain of litreature on how to avoid avoid it in the first 
> place.
> 
> It's not just "something nice" - it's a fundamental requirement if 
> you're using pessimistic locks.

I don't understand your claim that post-load lock upgrades are a
fundamental requirement of pessimistic locking. In databases that
allow reads of rows with write locks (e.g. default behavior in Oracle)
that behavior would be considered a form of optimistic locking.

See: http://www.orafaq.com/papers/locking.pdf

At least with Oracle, unless you obtain the write lock at select
time the data may be altered before you update it. What are the
benefits in doing a "select for update" on the PK after the 
data has been loaded? Why not just write the data and let the
database upgrade the lock automatically? I suppose the first approach
gives you a somewhat smaller window for the data to change before
the update. On the other hand, it requires another access to the
database. If you are developing applications with relatively long
transactions I could see behavior potentially being a benefit. 
The systems I work on are high rate transaction processing systems 
with short transactions. The deferred, explicit lock upgrades are a 
"nice to have" but not very useful in that context.

I understand that deadlocks are nothing new. As I'm sure you know,
one of the techniques for avoiding deadlock is to use pessimistic
locking and lock the objects in the same order in each client.
The immediate load-time locking is a requirement for this strategy.
A deferred lock upgrade could still lead to deadlocks. Even though
the database will detect and break these deadlocks, the customers
I work with prefer to avoid them in the first place.

> > It's also effectively optimistic
> > locking since the data initially read may have changed between
> > the time you load the object and the time you upgrade the lock.
> 
> Not necessarily true. That's what things like read locks, isolation 
> levels and cursor stability are all about.

I'll be more specific. It is optimistic rather than pessimistic locking
in databases like Oracle and others that (by default) allow reads 
while a row is write locked.

Regards,

Steve