Results 1 to 9 of 9
  1. #1
    Join Date
    Mar 2004
    Location
    Toronto, ON, Canada
    Posts
    513

    Unanswered: DB2 locking vs. Oracle locking

    I was reading about Oracle's locking model, maybe someone here knows a bit about both and can clarify.

    According to the article, Oracle doesn't block reads while an application updates rows! (or vice versa)

    While I'm sure this is great for concurrency, it seems to me that it gives inaccurate results...

    ie:
    Oracle:
    Application 1 - updates row (no commit)
    Application 2 - reads row (gets old data!)

    DB2:
    Application 1 - updates row (no commit)
    Application 2 - reads row, blocks
    Application 1 - commit
    Application 2 - gets new data

    (Unless using UR)

    So it seems like a fundamental difference as to when a change takes place... in DB2 the row is deemed to have changed when the statement is issued, in Oracle it's deemed to have changed when the statement is committed.

    Is my understanding accurate?
    --
    Jonathan Petruk
    DB2 Database Consultant

  2. #2
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    I think your understanding is correct. It is recommended when developing with Oracle to re-read the record that you're about to update using "FOR UPDATE" clause - basically, use optimistic locking strategy.

  3. #3
    Join Date
    Jan 2004
    Location
    Europe, Finland, Helsinki
    Posts
    64
    Yes!

    Your understanding is all correct. In Oracle, an update will create a new version of the data and a commit will allow others to see it. Old data can now discarded. However, for recovery needs Oracle needs also logging.
    - If a rollback is needeed, this new data is just discarded.

    In DB2 when you do an update, DB2 log saves old version and then data will be overwritten. However, others may not see this new data, until a commit is issued.
    - If a rollback is needeed, DB2 fetch old version fron the log and rewrite old data back.


    Cheers, Bill

  4. #4
    Join Date
    Mar 2004
    Location
    Toronto, ON, Canada
    Posts
    513
    Very interesting, thanks.
    --
    Jonathan Petruk
    DB2 Database Consultant

  5. #5
    Join Date
    Sep 2003
    Posts
    237
    Stinger and DB2_EVALUNCOMMITTED registry variable. With this variable enabled, predicate evaluation can occur on uncommitted data. This means that a row that contains an uncommitted update may not satisfy the query, whereas if the predicate evaluation waited until the updated transaction completed, the row may satisfy the query.
    mota

  6. #6
    Join Date
    May 2009
    Posts
    1
    How we can check the table locking in the DB2 Z OS? How we can avoid it?

  7. #7
    Join Date
    Apr 2008
    Location
    Iasi, Romania
    Posts
    561
    Provided Answers: 2
    If you are concerned about accuracy in an Oracle environment, you should know that Oracle has a SERIALIZABLE isolation level that is, somehow, similar to DB2 RR isolation level. Although the internal mechanisms for assuring isolation are quite different, yes.
    Florin Aparaschivei
    DB2 9.7, 10.5 on Windows
    Iasi, Romania

  8. #8
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    Quote Originally Posted by aflorin27
    If you are concerned about accuracy in an Oracle environment, you should know that Oracle has a SERIALIZABLE isolation level that is, somehow, similar to DB2 RR isolation level. Although the internal mechanisms for assuring isolation are quite different, yes.
    The posts about Oracle (and DB2) prior to the most recent post about Db2 for z/OS before yours, are almost 5 years old.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  9. #9
    Join Date
    Apr 2008
    Location
    Iasi, Romania
    Posts
    561
    Provided Answers: 2
    Sorry I usually read the new post - so I didn't pay attention to the first posts' dates.
    Florin Aparaschivei
    DB2 9.7, 10.5 on Windows
    Iasi, Romania

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •