15.00 - CREATE INDEX - Teradata Database

Teradata Database SQL Data Definition Language Detailed Topics

Product
Teradata Database
Release Number
15.00
Content Type
Programming Reference
Publication ID
B035-1184-015K
Language
English (United States)

CREATE INDEX

Locks and Concurrency

The severity of locking you specify with a LOCKING modifier does not always match the severity of locking applied by the Lock Manager, as the following table describes:

 

IF you explicitly specify this lock severity for the table that contains the index columns …

THEN the system places this lock severity on that table …

  • None
  • WRITE
  • WRITE

  • ACCESS
  • READ
  • SHARE
  • READ

    EXCLUSIVE

    EXCLUSIVE

    For more information about locking severities, see SQL Request and Transaction Processing.

    For more information about the LOCKING modifier, see SQL Data Manipulation Language).

    The READ or WRITE lock is upgraded to an EXCLUSIVE severity after the index subtables are created, but prior to 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.