Results 1 to 11 of 11
  1. #1
    Join Date
    Jan 2003
    Posts
    1,605

    Unanswered: "Currently Committed" default isolation level in v9.5

    Hi,
    I have been reading what will be new features in v9.7 that will be released in June 2009. One of the features is "Currently Committed" isolation level that will be default (prior to 9.7 default isolation level was "Cursor stability").

    I just surfed the web bug can't fine any useful info. Has someone else found out any useful info?

    From web page: DB2 9.7 Announced by IBM - I think it has something for everyone
    "Currently committed semantics so that readers don’t block writers and writers don’t block readers"
    Regards

  2. #2
    Join Date
    Oct 2001
    Location
    Bangalore
    Posts
    186

    "Currently Committed" default isolation level in v9.5

    May be too late to reply to you but you can check out:

    IBM DB2 9.7 for Linux, UNIX and Windows Information Center

    In layman's term it is just getting the last committed data from the logs to the readers thus avoiding locks.

    Even if the same row is being updated and not committed in some other transaction, the reader will be seeing the last committed data and there will be no deadlock or lock timeout situation which was a big problem for developers for a long time.

    Regards,
    Prashant
    Prashant

  3. #3
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    Quote Originally Posted by dahalkar_p View Post
    May be too late to reply to you but you can check out:

    IBM DB2 9.7 for Linux, UNIX and Windows Information Center

    In layman's term it is just getting the last committed data from the logs to the readers thus avoiding locks.

    Even if the same row is being updated and not committed in some other transaction, the reader will be seeing the last committed data and there will be no deadlock or lock timeout situation which was a big problem for developers for a long time.

    Regards,
    Prashant
    For poorly written code, this can eventually result in DB2 going deeper into the logs (past the log buffer, to the active log files and archive log files) and killing performance. Be very careful if you use this new feature (which is the default on a newly created 9.7 database).
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  4. #4
    Join Date
    Dec 2010
    Location
    India
    Posts
    4
    Is there someway that this feature can be used in 9.5. We have developed an application based on stored procs. One SP performs insert/update in almost all the tables in the database and when this happen, it holds the other SPs which execute only select querries on hold. This causes a delay by few seconds which is not good from performance perspective.

  5. #5
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    You cannot use currently commited in 9.5, but there are some db2set variables you can switch to ON or YES (also in 9.7):

    DB2_EVALUNCOMMITTED=ON
    DB2_SKIPDELETED=ON
    DB2_SKIPINSERTED=ON

    You must restart the instance after setting these.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  6. #6
    Join Date
    Oct 2011
    Posts
    2

    please elaborate

    Can you please elaborate on the notion of db2 going deeper into the logs, my understanding is db2 only goes to the logs to get the last committed transaction from the logs if it detects an uncommitted data in the table, the one scenario that I can think is if there is a long running transaction that has not issued a commit for an extended amount of time, such as a batch program. So, do you mean not committing frequently when you say 'poorly written code', just trying to understand the comment.

    Thanks in advance.

    Quote Originally Posted by Marcus_A View Post
    For poorly written code, this can eventually result in DB2 going deeper into the logs (past the log buffer, to the active log files and archive log files) and killing performance. Be very careful if you use this new feature (which is the default on a newly created 9.7 database).

  7. #7
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    If DB2 can get the log information from the log buffer, then performance will not suffer to any noticeable degree. This is the scenario that IBM did most of its benchmark testing with. But once the log buffer is flushed to disk (I believe it is at least once per second), and if the required logs are no longer available in memory, it will require disk I/O to retrieve the information from the logs, and I don't believe there is any kind of random access method (requires reading the logs sequentially I assume). There may be some additional log buffers I don't know about, but eventually (if the amount of poorly written code increases) DB2 will have to go deeper into the logs to the point of always requiring disk access to retrieve them.

    It is true that avoiding deadlocks is often better than 30% worse performance (wild guess), but the main reason for this new feature is Oracle compatibility introduced in DB2 9.7 and lots of Oracle application code is very poorly written so pessimistic locking is often not an option if one wants to migrate from Oracle to DB2 without rewriting the application code. It is also probably a good marketing tool to help sell DB2 to nitwit managers and executives who think one can choose the best database by comparing which one has the most features.

    One aspect of poorly written code is failure to commit frequently or properly close transactions. There are other aspects also, such as making sure different programs access multiple tables (and take locks) in the same sequence so as to reduce the likelihood of deadlocks, making sure index access is used whenever possible to avoid table scans (which usually locks a larger number of of rows), etc.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  8. #8
    Join Date
    Oct 2011
    Posts
    2

    Thanks

    Thanks for the quick reply and for highlighting the possibility of the disk i/o, are you aware of any other disadvantages especially turning it on a system that has been upgraded from a previous version i.e. effect on legacy batch COBOL programs that were previously running under CS (Cursor Stability) isolation level. I am sure there are several variable such as how the code is written assuming pessimistic locking mechanism..but still wanted to throw the question out there.

    One big advantage of this isolation level is that several java O/R mapping tools such as Hibernate work much better under optimistic concurrecy model, in case of these tools the developer has minimal control over the SQL statements that get generated and the order in which the locking can take place. (I guess one can argue that is poor code but it is generated not written and saves developers lot of time and also reduces the amount of code that needs to be maintained). Thanks for your time.

  9. #9
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    One disadvantage of Currently Committed is that if a lock wait situation occurs, to resolve it DB2 retrieves stale data from the logs instead of waiting (even for waiting for a short time) for the other transaction to finish so it can get the latest data. Oracle optimistic locking has the same result of reading stale data. Obviously, if the other transaction is not going to commit any time soon, stale data is better than a locktimeout in most cases (not all). Personally, I prefer more liberal use of UR to resolve these problems.

    Regarding you comments that it is easier and faster to write crappy code than good code, yes, but is a false economy IMO. Quality is always cheaper in the long run.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  10. #10
    Join Date
    Jul 2011
    Location
    USA
    Posts
    80
    Quote Originally Posted by Marcus_A View Post
    You cannot use currently commited in 9.5, but there are some db2set variables you can switch to ON or YES (also in 9.7):

    DB2_EVALUNCOMMITTED=ON
    DB2_SKIPDELETED=ON
    DB2_SKIPINSERTED=ON

    You must restart the instance after setting these.
    Hi Marcus,

    Want to know if we will enable above db2set variable in 9.5 then it will behave similar to currently commited isolation ?
    Because I am finding that it will not read the data from log it will skip that data. Let me know If I am wrong.
    Last edited by niteshtheone; 03-02-13 at 05:10.

  11. #11
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650
    Nitish, You are right. the db2 registry parameters behave different. But, this is another way of avoiding/reducing lock timeout/deadlock situations.

    --
    Visit the new-look IDUG Website , register to gain access to the excellent content.

Posting Permissions

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