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.
- 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.
- 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.
DB2 Version 9.7 for Linux, UNIX, and Windows