Oracle VS DB2
Oracle的优越多版本读一致性模型允许读和写完全独立完成而不冲突
IBM DB2要求读锁，读锁会引起死锁，影响并发能力，增大管理难度
Oracle仅对写活动要求锁
“Locks are acquired even if your application merely reads rows, so it is still important to commit read-only units of work. This is because shared locks are acquired by repeatable read, read stability, and cursor stability isolation levels in read-only applications. With repeatable read and read stability, all locks are held until a COMMIT is issued, preventing other processes from updating the locked data, unless you close your cursor using the WITH RELEASE clause. In addition, catalog locks are acquired even in uncommitted read applications using dynamic SQL or XQuery statements.”
http://publib.boulder.ibm.com/infocenter/db2luw/v9r5/topic/com.ibm.db2.luw.admin.perf.doc/doc/c0005276.html
IBM DB2 does not have rollback segments and thus requires read locks to provide read consistency
Oracle由于其良好的设计，所以不会发生锁升级(escalate locks):
IBM DB2在内存中管理锁，当内存不够用时可能将行级锁升级到表级锁
锁升级(escalate locks)将提升死锁(dead locks)发生的可能性
Oracle的锁不依赖于内存中的锁管理器实现，而存放在磁盘上
“The amount of memory devoted to locking is controlled by the locklist database configuration parameter. If the lock list fills, performance can degrade due to lock escalations and reduced concurrency on shared objects in the database. If lock escalations occur frequently, increase the value of either locklist or maxlocks, or both. Also, to reduce number of locks held at one time, ensure that transactions COMMIT frequently to free held locks.”
http://publib.boulder.ibm.com/infocenter/db2luw/v9r5/topic/com.ibm.db2.luw.admin.perf.doc/doc/c0005266.html
IBM DB2 locking conflict and deadlock management issues documented by IBM
IBM DB2 9.5 introduces enhanced optimistic locking feature
Workaround to locking conflict and deadlock management issues
Requires application and schema changes
Onus of resolving locking conflicts on users
| Feature |
Oracle Database 11g |
IBM DB2 9.5 LUW |
| Multi-version Read Consistency |
Yes |
No |
| Readers don’t block writers |
Yes |
No |
| Writers don’t block readers |
Yes |
No |
| Guaranteed Consistent Queries |
Yes |
No |
| No Lock Escalations |
Yes |
No |
| No Lock Escalation Deadlocks |
Yes |
No |
Oracle VS SQL SERVER
"In practice and under high load, SQL Server's locking system, which is based on lock escalation, does not perform well. Why? Lock contention. … In a system in which many users are modifying the database at once, and many more users are trying to access the database concurrently, the locks are flying, users spend a lot of time waiting to attain locks, deadlocks are frequent, and users are far from happy.
….
But throw a couple hundred concurrent users at your database and a constant stream of INSERTS and UPDATES with quite a few DELETEs sprinkled in, and you'll start reading Oracle literature and eyeing your war chest.”
Michael Balloni, SQL Server Lock Contention Tamed,
http://www.sql-server-performance.com/lock_contention_tamed_article.asp
Transaction Isolation The Problems with a Dirty Read
‘When using Uncommitted Read, you give up the assurance of
strongly consistent data in favor of high concurrency in the system
without users locking each other out. So when should you choose
Uncommitted Read?
Clearly, you don’t want to use it for financial transactions
in which every number must balance.’
Inside Microsoft SQL Server 2000
by Ron Soukup and Kalen Delaney
Microsoft Press