Re: [Hibernate-devel] Pessimistic locking?

[Paul S. <pau...@ne...>]

Gavin_King/Cirrus%CI...@ci... wrote:
>>It seems that way. There are formal definitions - optimistic concurrency
>>control schemes have been around for a while (and not like how you
>>describe).
>>
> 
> I went and had a good look on google to see how these terms are commonly
> used. Everyone agrees that optimistic locking means the application does
> some work by retaining a version number/timestamp and that pessimistic
> locking works by the database retaining a "lock" at read time. No-one seems
> terribly explicit as to whether a read lock constitutes a lock or not. I
> would have thought it did. Apparently you don't think so.... I would love
> to be sure of terminology here because when we come to document this I
> don't want to look clueless ;)
> 
> 
>>Check out: "Concurrency Control and Recovery in Database Systems" by
>>Bernstein, Hadzilacos & Goodman (1987).
>>
> 
> k, i will try to find it (p.s. i am a mathematician not a c.s. major so
> sometimes im missing authorative sources here)

There's better stuff out there (more recent). That one's just a good 
summary of what had happened up until that point. For more recent stuff, 
check out the Morgan Kaufmann series on Data Management Systems. The 
Transaction Processing Concepts and Techniques by Jim Gray is a classic 
(althoug almost 10 years old).

Somone else here might know better references?

>>At the very least, the lock() method on an already loaded object would
>>do the trick for starters. As long as the call makes its way through to
>>the database.
>>
> 
> Yup. After i posted that last email i wondered about this option. Its a
> nice one i think. Frees you to lock an object even sometime after you first
> retrieved it. All good. Im guessing its enough to just touch the row,
> right, ie. just select the primary key with "for update" to lock the whole
> row?

Yep. There is an "OF" operator in the FOR UPDATE clause that specifies 
columns, but it's only used to determine what tables will have rows locked.

One other thing: what about the "NOWAIT" operator? Comes in handy.

> Would probably be good to allow "for update" in query language also, since
> that would let you lock a bunch of rows in one query.

But you can't use it with the "DISTINCT" clause, so it might be an issue 
with the query stuff. Other restictions that exist in Oracle at least, 
and probably in other DB's (e.g can't use the group-by clause, not in 
sub-queries). Don't know which might be an issue here.

I have no idea, but is there any kind of "standard" out there (explicit 
or de-facto) that addresses this? SQL92 or OQL? JDO? Is it part of the 
query language or the API in the ODMG or JDO stuff? Or both?

PaulS.