DB2/SQL Server Locking and Concurrency VS Oracle

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