Locking and concurrency considerations for online index reorganization

One of the most important aspects of online index reorganization is how locking is controlled. Locking control is crucial to application concurrency.

Online index reorganization applies to an index reorganization that is run with the ALLOW READ ACCESS or ALLOW WRITE ACCESS options. These options allow you to access the table while the indexes of the table are being reorganized.

An online index reorganization operation can hold the following locks:
  • A IX lock on the table spaces affected by the reorganization operation is acquired. This ensures access to these table spaces. The affected table spaces include the table spaces that hold the table, as well as partition, and index objects.
  • A X alter table lock to prevent the affected table from being altered during reorganization.
  • A IS table lock is acquired on the system catalog table SYSIBM.SYSTABLES.
  • A NS row lock is acquired on the row for the affected table in the system catalog table SYSIBM.SYSTABLES.
  • A IS table lock is acquired on the system catalog table SYSIBM.SYSDATAPARTITIONS, for a partition level reorganization.
  • A NS row lock is acquired on the row for the affected table in system catalog table SYSIBM.SYSDATAPARTITIONS, for a partition level reorganization.
  • The ExtentMovement lock is taken for the duration of index reorganization, which will prevent the reclaimable storage feature from making progress.
  • A table lock is acquired and held throughout the reorganization operation. The type of lock depends on the reorganization option, access mode, and table type. Details are described in the following tables:

Table locking and REBUILD operations

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 before the replacement of the original indexes. If the reorganization operation fails and the transaction is rolled back, the original indexes remain intact.

Before the reorganization of each index begins, the reorganization waits for all current write transactions to complete. While the reorganization is waiting, it allows new transactions to start.
Table 1. Required table lock for REBUILD operation
Access mode Table type Additional condition Lock acquired
ALLOW READ Non-partitioned tables   U-lock on the table
ALLOW READ Partitioned tables With a table partitioning clause specified.
  • IS-lock on the table
  • U-lock on the partition
ALLOW READ Partitioned tables For non-partitioned index. U-lock on the table and every partition
ALLOW WRITE Non-partitioned tables   IN-lock on the table
ALLOW WRITE Partitioned tables With a table partitioning clause specified. IS-lock on the table and the partition
ALLOW WRITE Partitioned tables For non-partitioned index. IN-lock on the table and every partition
Attention:
For REBUILD and ALLOW WRITE, a U-lock is acquired on the table or partition after all the indexes have been built and concurrent updates have been reflected sufficiently. This suspends write access to the table or partition, while any remaining updates from concurrent transactions are included in the new indexes. If a partition clause was specified, the U-lock is acquired on the partition. Otherwise, the U-lock is acquired on the table. The following steps can be taken to minimize the time that writers are blocked.
  • Running the reorganization when concurrent application traffic is as light as possible to minimize the amount of updates that remain to be reflected in the new indexes while writers are blocked.
  • Ensuring page cleaners are working efficiently during the reorganization to minimize the time needed to complete the flush of the indexes while writers are blocked.

For REBUILD and either ALLOW WRITE and ALLOW READ ACCESS, an exclusive Z-lock on the table or partition is requested at the end of index reorganization. The Z lock is on the partition if a partition clause was specified. Otherwise, it is on the table. This lock suspends table or partition access to allow for the replacement of the original indexes by the new indexes.

Table locking and CLEANUP and RECLAIM EXTENTS operations

During online index reorganization with the CLEANUP option, space is reclaimed and made available for reuse by the index object being cleaned.

During online index reorganization with the RECLAIM EXTENTS option, space is reclaimed and made available for use for all objects in the table space. To maximize the amount of space reclaimed, run the index reorganization with the CLEANUP option first, and then with the RECLAIM EXTENTS option. You can also maximize the reclaimed space by running the index reorganization with both options selected.
Attention: In the final phase of RECLAIM EXTENTS, before actually freeing extents if any are to be freed, the reorganization waits for concurrent read and write transactions to complete. New transactions are allowed to start during this wait.
Table 2. Required table lock for CLEANUP and RECLAIM EXTENTS operations
Access mode Table type Additional condition Lock acquired
ALLOW READ Non-partitioned tables   S-lock on the table
ALLOW READ Partitioned tables With a table partitioning clause specified.
  • IS-lock on the table
  • S-lock on the partition
ALLOW READ Partitioned tables For REORG INDEX of a non-partitioned index or REORG INDEXES ALL with no partition clause. S-lock on every partition and the table
ALLOW WRITE Non-partitioned tables   IX-lock the table
ALLOW WRITE Partitioned tables With a table partitioning clause specified.
  • IS-lock on the table
  • IX-lock on the partition
ALLOW WRITE Partitioned tables For REORG INDEX of a non-partitioned index or REORG INDEXES ALL with no partition clause. IX-lock on every partition and the table