The type of lock you specify with a LOCKING modifier does not always match the type of lock applied by the Lock Manager, as the following table describes:
| Lock type you specify for the table containing the index columns | Lock type placed by the system on the table |
|---|---|
|
WRITE |
|
READ |
| EXCLUSIVE | EXCLUSIVE |
For more information about lock types, see Database Locking Levels and Severities.
The READ or WRITE lock is upgraded to an EXCLUSIVE lock after the index subtables are created, but before locking the dictionary and modifying the headers. This improves concurrency by reducing the time SELECT requests against the table are blocked.
If you do not specify an explicit EXCLUSIVE lock, then a CREATE INDEX request on a table can run concurrently with a SELECT request that has an ACCESS or READ lock on the same table until the lock is upgraded to a severity of EXCLUSIVE. When that occurs, the statement that requests the lock first blocks the other.
A CREATE INDEX request holding an EXCLUSIVE lock on a table can run concurrently with a COLLECT STATISTICS statement on that table until the CREATE INDEX statement requests that the lock be upgraded to EXCLUSIVE. When that occurs, the CREATE INDEX statement is blocked until the COLLECT STATISTICS request completes. Any subsequent COLLECT STATISTICS statements on the table are blocked until the CREATE INDEX process completes.
A CREATE INDEX request cannot run concurrently against the same table with an ALTER TABLE statement or another CREATE INDEX statement. The statement that first requests the lock blocks the other.