Re: INITRANS and MAXTRANS are confusing me?

"Howard J. Rogers" <howardjr2000_at_yahoo.com.au> wrote in news:pan.2003.03.11.20.20.52.379490_at_yahoo.com.au:

> Which is fair enough, but that is why Informix (like many other
> databases) do lock escalation. They see locks as a scarce resource
> because there is a central pool of locks which have to be divided
> amongst all possible users. So instead of letting you take 10000
> row locks, I will escalate that to a single table lock, and to hell
> with the consequences (like the minor one that you've just locked
> 5000 rows that you weren't intending to touch, but I was... so now I
> have to wait).

That's not why lock managers escalate locks. In the most simplest terms, is it easier to maintain 10,000 locks or 1,000 locks? Rather than maintaining individual locks, the lock manager escalates to get a 'handle' on them.

This is also why escalation is a configurable value on some DBMS -- I can't recall Informix. :)

> Oracle's locks are stored down in the row, therefore they are never a
> 'scarce resource'; therefore Oracle never feels the need to limit the
> number of them that you can take; and hence there is never lock
> escalation in Oracle.
>
> It's an entirely different approach (and a better one IMHO).

I tend to disagree that this is a better approach. The downside with the 'infinte locks' strategy is that now a SELECT-FOR-UPDATE is a read and a write - a write to write the information that we locked the data. If we compare speeds of disk and an in-memory lock manager, there isn't a comparison.

Thx!

-- 
Pablo Sanchez, High-Performance Database Engineering
http://www.hpdbe.com