Locks and Concurrency | CREATE INDEX | Teradata Vantage - Locks and Concurrency - Advanced SQL Engine - Teradata Database

SQL Data Definition Language Detailed Topics

Product
Advanced SQL Engine
Teradata Database
Release Number
17.10
Published
July 2021
Language
English (United States)
Last Update
2021-07-27
dita:mapPath
imq1591724555718.ditamap
dita:ditavalPath
imq1591724555718.ditaval
dita:id
B035-1184
lifecycle
previous
Product Category
Teradata Vantage™

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
  • None
  • WRITE
WRITE
  • ACCESS
  • READ
  • SHARE
READ
EXCLUSIVE EXCLUSIVE

For more information about lock types, see Teradata Vantage™ - SQL Request and Transaction Processing, B035-1142.

For more information about the LOCKING modifier, see Teradata Vantage™ - SQL Data Manipulation Language, B035-1146.

The READ or WRITE lock is upgraded to an EXCLUSIVE lock 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.