DB2/SQL Server Locking and Concurrency VS Oracle

Oracle VS DB2



IBM DB2要求读锁,读锁会引起死锁,影响并发能力,增大管理难度


“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.”


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)发生的可能性



“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.”



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






“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,
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


扫码关注dbDao.com 微信公众号:

  1. 6年前他们告诉我,oracle的select查询不会被锁影响.

    select被 enq: TX阻塞
    select被row cache lock阻塞

    后来,我看到你写的 – Oracle由于其良好的设计,所以不会发生锁升级(escalate locks) – ,我再也不信了.

  2. 以其用oracle 11g跟MSSQL 2000比来证明Oracle所谓的优越性,那还比如拿oracle 11g跟 1989年 SQL Server 1.0 来作比较了,这样不是更能显示oracle所谓的优越性?

    Performs conflict checks immediately.
    Locks a record within a transaction to prevent conflicts.

    ORALCE:No equivalent

    3.MSQL:READ COMMITTED using row versioning


    5.MSQL:READ UNCOMMITTED Provides access to uncommitted data.
    ORALCE:No equivalent

    ORALCE:No equivalent

    ORALCE:No equivalent. The lack of read locking can cause design challenges for the developer, as outlined in Oracle9i Application Developer’s Guide—Fundamentals Release 2 (9.2)

    8.MSQL:Can use blocking in pessimistic isolation levels, or must handle conflicts (data row updated outside of the transaction) and retry failed transactions. Row-level versioning reduces chances of conflict.
    ORALCE:Must handle conflicts (ORA-08177: data page updated outside of the transaction) and retry failed transactions.

    9.MSQL:The application can choose an appropriate concurrency model.
    ORALCE:The application always sees potentially stale data unless manual table locking or SELECT…FOR UPDATE is used, because there is no choice between concurrency models.

    10.MSQL:Transact-SQL TRY…CATCH logic handles conflict errors, but does not handle out-of-space issues with tempdb.
    ORALCE:PL/SQL has error handling that enables error handing for ORA-08177 (conflict), but does not handle ORA-01555 (rollback segment space issue). With Undo Tablespaces, a similar out of space issue can arise.

    • 这个比较是基于 Oracle 版本8和 MSSQL 2000的, 是同时代产品的比较, 或者说Oracle 8还要老于MSSQL 2000。


  3. 你在2011年末来比较N前的技术是没有太大的意义的,从历史上说,oracle DB至少比SQL SERVER 早发展10年,如果你拿Oracle 1989年前的版本跟SQ SERVER比优越性,那Oracle 保证完胜,因为那时候SQL SERVER 还没有诞生,希望你抛弃偏见,与时俱进的看待问题,免得误导别人。

沪公网安备 31010802001379号

TEL/電話+86 13764045638
Email service@parnassusdata.com
QQ 47079569