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, MCDBAI'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
