Locking and concurrency considerations for online table reorganization

One of the most important aspects of online table reorganization-because it is so crucial to application concurrency-is how locking is controlled.

An online table reorg operation can hold the following locks:
  • To ensure write access to table spaces, an IX lock is acquired on the table spaces that are affected by the reorg operation.
  • A table lock is acquired and held during the entire reorg operation. The level of locking is dependent on the access mode that is in effect during reorganization:
    • If ALLOW WRITE ACCESS was specified, an IS table lock is acquired.
    • If ALLOW READ ACCESS was specified, an S table lock is acquired.
  • An S lock on the table is requested during the truncation phase. Until the S lock is acquired, rows can be inserted by concurrent transactions. These inserted rows might not be seen by the reorg utility, and could prevent the table from being truncated. After the S table lock is acquired, rows that prevent the table from being truncated are moved to compact the table. After the table is compacted, it is truncated, but only after all transactions that are accessing the table at the time the truncation point is determined have completed. If space reclamation is not a high priority, the truncate phase and the required S lock can be avoided using either NOTRUNCATE or CLEANUP OVERFLOWS ONLY options.
  • The reorg utility might wait at certain points on existing applications holding a lock on the table. The wait occurs when the reorg begins, ends, and during the truncate phase. This is accomplished by draining the existing applications through a Z table lock that does not block new requests for the lock. After all the previously existing applications release the table lock, the reorg execution continues. The lock mode, however, remains either IS or S as determined above.
  • A row lock might be acquired, depending on the type of table lock:
    • If an S lock is held on the table, there is no need for individual row-level S locks, and further locking is unnecessary.
    • If an IS lock is held on the table, an NS row lock is acquired before the row is moved, and then released after the move is complete.
    • In a Db2® pureScale® environment, an X row lock is acquired before the row is moved regardless of the lock intent on the table. The lock will be released after the move is complete
  • Certain internal locks might also be acquired during an online table reorg operation.

Locking has an impact on the performance of both online table reorg operations and concurrent user applications. You can use lock snapshot data to help you to understand the locking activity that occurs during online table reorganizations.