Database Wars: Oracle vs SQL Server Lock Escalation

[Jeff Roughgarden]

I was at a conference and had it asserted to me by an Oracle
afficiando that Oracle and DB2 handled low-level locks "better" than
SQL Server, and that this was likely the cause of SQL Server's
relatively slower and more deadlock-prone performance when running the
same application. (SQL does seem to perform more poorly for this app,
a PeopleSoft customer service and billing app.)

Is there any significant difference in lock escalation strategies
between major databases, and if so, what are the implications?

Jeff Roughgarden, MSCD, MCDBA

[Serge Rielau]

I'm surprised that Oracle dude had a good word for DB2 ;-)

DB2 supports, row (default) and table locks (explicit) and does lock
escalation in specific circumstances. There are numerous lock-avoidance
strategies such as "type 2 indexes" which limit impact of locks on
indexes in case of DML.

SQL Server (for historical reasons I presume) also supports page-locks.
I have little knowledge of SQL Servers locking strategies so I can't,
and shouldn't judge.

Oracle supports a non standard isolation level which gives the
transaction a "snapshot in time" (read consistency).
In this isolation levels readers and writers do not lock each other when
colliding at the same row because a reader would get an old copy.
Often this isolation level is what Oracle fans refer to as the superior
locking strategy.

Cheers
Serge
--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab