Re: Db2, Oracle, SQL Server

Mark A wrote:

> Most databases that I know about lock on selects (these locks are not
> non-existent), but they are share locks, which can all coexist with
other
> share locks with any concurrency problems.

for sure you meant:
"without" any concurrency problems. Not "with"?

If they are share locks, then they are completely irrelevant for lock escalation or a discussion on exclusive lock contention, be it row level or otherwise. Why do you persist on using examples of features that are completely unrelated? Why do you insist on assigning locks to Oracle where they do not exist?

Repeat after me, as many times as needed: by default, Oracle does NOT place locks on rows on read. Shared or otherwise, in memory or on disk.

Ever. Got it?

It places a shared lock on the table, to avoid anyone changing the definition while the read progresses. But by default, it does not EVER place a lock on read on the row. That is what the multi-versioning is there for.

> The point is that lock escalation to table level is sometimes good,
as in a
> data warehouse application where there are only selects.

Totally out of context example. You continue to confuse shared read locks
with exclusive write locks. Shared read locks do not need ANY lock escalation. Ever. Particularly if placed at table level. It's when they become exclusive or are exclusive write locks that the problem of escalation may appear. And that has nothing to do with SELECTs.

> Table locks are a
> lot faster than locking every row (especially when one is doing a lot
of
> tablespace scans--common in data warehouse applications).

Only if you are locking on reads at row level. Which is not the case of Oracle on a read-only database. So, to mention lock escalation in DW
as something of relevance on a discussion of lock escalation for updates
is a total non-sequitur with the only purpose of confusing the issue and trying to avoid proper discussion of the problem.

Which is: row lock escalation on updates is not scalable. Received on Mon Feb 07 2005 - 21:54:37 CST