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

[Gavin_King/Cirrus%<CI...@ci...>]

> 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.

Oracle supports two isolation levels, read committed and serializable.

In ANY database, read committed would be equivalent to optimistic
locking.(Read committed is equivalent to "no read locks" which I think we
all agreed
to define as optimistic locking. It turns out that oracle actually
implements this internally using timestamps, which we know is another
feature
of optimistic locking.)

Now my understanding is that, contrary to Sybase and DB2, Oracle does not
use
read locks to implement serializable isolation. So you find out at
transaction commit time that the transaction was unserializable, rather
than having the transaction block halfway through. This does sound like
optimistic locking to me. I think postgres might also work this way.

P.S. I would personally be tempted to think of this as a misfeature of
oracle since it means that with very-high-concurrency data and a
high isolation level, the probability of transaction failure is
large. Perhaps I'm wrong on this; there certainly are performance
advantages to the non-blocking model.

> 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?

Well, in other databases where there are read locks (and assuming
isolation is at least repeatable read), a "select for update" would obtain
a
write lock, instead of a read lock. The oracle situation would be a write
lock as opposed to *no* lock.....

> 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.

Fair enough.

Anyways the result of this whole discussion is everyone convinced me we
need to let the user specify "select for update" where appropriate. That
feature is now implemented.

I also agreed to stop calling "long transactions with versioning" simply
"optimistic locking". (I have often heard "optimistic locking" used to
mean this in OO-development circles, but its clearly less-than-specific.)