Results 1 to 7 of 7
  1. #1
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1

    Unanswered: Is SMS space de-allocated during transaction rollback?

    We have DB2 UDB v.7.1 on Windows 2k with SMS tablespaces.

    I'm wondering whether the SMS space that have been allocated during a transaction (say, an insert caused a new page to be allocated, or an update to a LOB field...) would be de-allocated if the application initiates roll-back at some point, which will in turn cause resizing of an SMS container. I understand that page allocation does get recorded in the database log, but would it be reverted upon a roll-back, or just marked as free space?

    Thanks for your thoughts.

  2. #2
    Join Date
    Mar 2004
    Location
    Toronto, ON, Canada
    Posts
    513
    You need to REORG to deallocate the space for SMS.
    --
    Jonathan Petruk
    DB2 Database Consultant

  3. #3
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Originally posted by J Petruk
    You need to REORG to deallocate the space for SMS.
    So, a newly allocated page will still remain allocated, although empty, even if the transaction that caused the allocation never commits?

    This is the process how I see it:

    1) The application issues an INSERT
    2) The db agent searches for free space, doesn't find any, and requests new page allocation.
    3) The container gets extended (does it, really, at this point though? or does it happen when the buffer page is written to disk?)
    4) New page allocation is logged in the db log.
    5) A bufferpool page gets latched.
    6) New data is written to the db log.
    7) New data is written to the bufferpool page.
    8) Page is unlatched and marked as dirty.
    9) Page is written to disk
    10) The application issues a ROLLBACK
    11) ? what happens next ?

  4. #4
    Join Date
    Mar 2004
    Location
    Toronto, ON, Canada
    Posts
    513
    Originally posted by n_i
    So, a newly allocated page will still remain allocated, although empty, even if the transaction that caused the allocation never commits?

    This is the process how I see it:

    1) The application issues an INSERT
    2) The db agent searches for free space, doesn't find any, and requests new page allocation.
    3) The container gets extended (does it, really, at this point though? or does it happen when the buffer page is written to disk?)
    4) New page allocation is logged in the db log.
    5) A bufferpool page gets latched.
    6) New data is written to the db log.
    7) New data is written to the bufferpool page.
    8) Page is unlatched and marked as dirty.
    9) Page is written to disk
    10) The application issues a ROLLBACK
    11) ? what happens next ?
    Maybe someone will correct me (hope not!) but I don't believe the tablespace allocation is in any way transaction aware. Once allocated, it stays allocated until a REORG, regardless of what happens in the transaction.
    --
    Jonathan Petruk
    DB2 Database Consultant

  5. #5
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650
    Nick's point on

    3) The container gets extended (does it, really, at this point though? or does it happen when the buffer page is written to disk?)
    raises a question (Hope I'm not being stupid)

    If the pages will be allocated only when the data is being written to disk, what will happen if the transaction is committed, and when writing the dirty bp pages to the disk, db manager finds that there is no space on the filesystem to allocate the new extent ?
    Visit the new-look IDUG Website , register to gain access to the excellent content.

  6. #6
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650
    I did a small test on a test database(tablespace is USERSPACE1, SMS) and have the found :

    1) If a table is created, you have a page allocated in a container ... When rolled back, it releases the page back to the OS ...

    2) If data is inserted, it allocated pages to write the data . When rolled back, this does not release the page ...

    Cheers
    Sathyaram

    Originally posted by J Petruk
    Maybe someone will correct me (hope not!) but I don't believe the tablespace allocation is in any way transaction aware. Once allocated, it stays allocated until a REORG, regardless of what happens in the transaction.
    Visit the new-look IDUG Website , register to gain access to the excellent content.

  7. #7
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Originally posted by sathyaram_s
    I did a small test on a test database(tablespace is USERSPACE1, SMS) and have the found :

    1) If a table is created, you have a page allocated in a container ... When rolled back, it releases the page back to the OS ...

    2) If data is inserted, it allocated pages to write the data . Rolling back did not release the pages...

    Cheers
    Sathyaram
    That's right. I was just doing similar tests, and can confirm this. This also includes LOBs.
    Last edited by sathyaram_s; 03-26-04 at 13:07.

Posting Permissions

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