Locks and Concurrency | CREATE INDEX | Teradata Vantage - Locks and Concurrency - Analytics Database - Teradata Vantage

SQL Data Definition Language Detailed Topics

Deployment
VantageCloud
VantageCore
Edition
Enterprise
IntelliFlex
VMware
Product
Analytics Database
Teradata Vantage
Release Number
17.20
Published
June 2022
Language
English (United States)
Last Update
2023-07-11
dita:mapPath
vuk1628111288877.ditamap
dita:ditavalPath
qkf1628213546010.ditaval
dita:id
B035-1184
lifecycle
latest
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.