Results 1 to 3 of 3
  1. #1
    Join Date
    Nov 2010
    Posts
    99

    Unanswered: committed and uncommitted read of same row

    I am curious about the steps that DB2 performs when reading the "clean" page during an update that has not been committed or that the page cleaner has not written the page back to disk.

    Let's say you updated a row but have not committed the transaction. If you want to read the same row without UR (let me see the clean page) does DB2 perform another i/o from disk into the bufferpool - at which point there would be 2 versions of the page in the bufferpool (the before page and after page) or does db2 read the transaction log (although there may not be a transaction since there was no COMMIT) or is there something else DB2 does to save the i/o and read the before version of the page that is in the bufferpool?

    Now let's say same example as above but in this scenario you've committed the transaction BUT the page hasn't been written to disk. Again you want to read the clean page (the one on disk, not the one that has yet to be written to disk) does DB2 perform an i/o - force the page cleaner to flush the page back to disk and move the "new" page back to the bufferpool or again does DB2 somehow keep track of the before and after page via log or some other way?

    thanks in advance

    -j

  2. #2
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    First, I am assuming you are referring to two different applications, because the same application will treat any uncommitted data as committed if it reads it in the same unit of work.

    DB2 does not flush a page from a bufferpool just because it is cleaned (written to disk). A page will only get flushed from a bufferpool if room is needed for another page to put into the bufferpool. The same page will not exist twice in the same bufferpool. Things are a little more complicated for pureScale (DB2's version of RAC), but that is totally different architecture with multiple "members" each with their own bufferpool and also a master bufferpool on the Caching Facility node.

    Things get a little complicated if one has Currently Committed enabled (default for new databases since 9.7). In that case, since DB2 will not wait for a lock to be released and if it needs to get the state of the row before it was locked by another application, DB2 will go into the logs to do that. It will not go to the tablespace on disk. If Currently Committed is not enabled, the other application will have to wait until the lock is released by the application who has updated it and not committed (doesn't automatically get the previous state).

    However, if UR isolation level is used for a SELECT, then DB2 will assume any changed data will eventually be committed, and will return the uncommitted state as it currently exists, ignoring any existing locks on the data.
    Last edited by Marcus_A; 06-03-13 at 11:46.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  3. #3
    Join Date
    Nov 2010
    Posts
    99
    Yeah 2 applications, and am using 9.7 fp5 on LINUX, Currently Committed is enabled.

    So with my current configuration DB2 will read the log, got it.

    Thanks!

Posting Permissions

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