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

关注dbDao.com的新浪微博

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

Comments

  1. xkglob刀 says:

    6年前他们告诉我,oracle的select查询不会被锁影响.
    后来我见过:

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

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

  2. ERIC says:

    以其用oracle 11g跟MSSQL 2000比来证明Oracle所谓的优越性,那还比如拿oracle 11g跟 1989年 SQL Server 1.0 来作比较了,这样不是更能显示oracle所谓的优越性?
    给你补补一些基础知识吧,
    关于锁升级,MSQL默认情况是锁升级,但是你可以做到在表或者DB级别不让其锁升级,具体怎么做看MSDN,如果你找不到,请回复。
    关于你说的什么读、写之间的相互阻塞,MSQL当然也可以做到读、写之间的相互不阻塞,你真要对比事务并发方面的能力,那也应该拿同年代的产品来对比吧,来,我来给你帖个表格,你自己数数呗。

    1,MSQL:SELECT … WITH (UPDLOCK)
    Performs conflict checks immediately.
    ORALCE:SELECT… FOR UPDATE
    Locks a record within a transaction to prevent conflicts.

    2,MSQL: READ COMMITTED
    ORALCE:No equivalent

    3.MSQL:READ COMMITTED using row versioning
    ORALCE:READ COMMITTED

    4.MSQL:SNAPSHOT
    ORALCE:SERIALIZABLE

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

    6.MSQL:REPEATABLE READ
    ORALCE:No equivalent

    7.MSQL:SERIALIZABLE
    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.

  3. ERIC says:

    关于MSSQL 办法处理方面的能力,看看下面的文档,好好学着点呗,别老是半桶水的水平来宣扬什么O所谓的优越性。
    http://download.microsoft.com/download/f/1/c/f1cf7b8d-7fb9-4b71-a658-e748e67f9eba/RowVersioningBasedIsolation.doc

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

      我欢迎有同学来探讨这些技术产品的比较,但是请语气平和、心境平和,谢谢!

  4. ERIC says:

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

Speak Your Mind

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