Locking and concurrency considerations for online index reorganization
In this topic, online index reorganization applies to an index reorganization that is run with the ALLOW READ ACCESS or ALLOW WRITE ACCESS parameters.
These options allow you to access the table while its indexes are being reorganized. During online index reorganization with the REBUILD option, new indexes are built as additional copies while the original indexes remain intact. Concurrent transactions use the original indexes while the new indexes are created. At the end of the reorganization operation, the original indexes are replaced by the new indexes. Transactions that are committed in the meantime are reflected in the new indexes after the replacement of the original indexes. If the reorganization operation fails and the transaction is rolled back, the original indexes remain intact. During online index reorganization with the CLEANUP and RECLAIM EXTENTS options, space is reclaimed and made available for use for all objects in the table space.
- To ensure access to table spaces, an IX-lock is acquired on the table spaces affected by the reorganization operation. This includes table spaces that hold the table, as well as partition, and index objects.
- To prevent the affected table from being altered during reorganization, an X alter table lock is acquired.
- A table lock is acquired and held throughout the reorganization
operation. The type of lock depends on the table type, access mode,
and reorganization option:
- For nonpartitioned tables:
- If ALLOW READ ACCESS is specified, a U-lock is acquired on the table.
- If ALLOW WRITE ACCESS is specified, an IN-lock is acquired on the table.
- If CLEANUP is specified, an S-lock is acquired on the table for READ access, and IX-lock for WRITE access.
- For partitioned tables, reorganization with ALLOW READ
ACCESS or ALLOW WRITE ACCESS is supported
at partition level only:
- If ALLOW READ ACCESS is specified, a U-lock is acquired on the partition.
- If ALLOW WRITE ACCESS is specified, an IS-lock is acquired on the partition.
- If CLEANUP is specified, an S-lock is acquired on the partition for READ access, and an IX-lock for WRITE access.
- An IS-lock is acquired on the table regardless of which access mode or option is specified.
- For nonpartitioned tables:
- An exclusive Z-lock on the table or partition is requested at the end of index reorganization. If a partitioned table contains nonpartitioned indexes, then the Z-lock is acquired on the table as well as the partition. This lock suspends table and partition access to allow for the replacement of the original indexes by the new indexes. This lock is held until transactions that are committed during reorganization are reflected in the new indexes.
- The IS table lock and NS row lock are acquired on the system catalog table SYSIBM.SYSTABLES.
- For a partition level reorganization, IS table lock and NS row lock are also acquired on the system catalog table SYSIBM.SYSDATAPARTITIONS.
- Certain internal locks might also be acquired during an online index reorganization operation.
- Online index reorganization might have impact on concurrency if the reorganization operation fails. For example, the reorganization might fail due to insufficient memory, lack of disk space, or a lock timeout. The reorganization transaction performs certain updates before ending. To perform updates, reorganization must wait on existing transaction to be committed. This delay might block other transactions in the process. Starting in Db2® version 9.7 Fix Pack 1, reorganization requests a special drain lock on the index object. Reorganization operations wait for existing transactions to finish; however, new requests to access the index object are allowed.