DML Requests and Locks | Transaction Processing | Teradata Vantage - DML Requests and Locks - Analytics Database - Teradata Vantage

SQL Request and Transaction Processing
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
2024-10-04
dita:mapPath
zfm1628111633230.ditamap
dita:ditavalPath
qkf1628213546010.ditaval
dita:id
evd1472255317510
lifecycle
latest
Product Category
Teradata Vantage™

When processing DML requests, Vantage accesses the necessary information from data dictionary tables using internal express-request transactions that place READ locks on row hash values. These locks are released when the data is returned to the parser.

The default locks that the system applies for DML requests are listed in the following table.

When any of the modifications in the table are performed as nonconcurrent isolated load operations on a load-isolated table, the Lock Manager sets an EXCLUSIVE lock rather than what is listed in the table.
DML Request Updated Columns Selection Criteria Object Locked Locking Severity
SELECT Not applicable UPI or USI  Row hash READ

If a join or hash index includes the specified columns, the Lock Manager sets a READ lock on the entire join or hash index and does not access the base table.
  Set of rows READ

If a join or hash index includes the specified columns, the Lock Manager sets a READ lock on a set of join or hash index rows and does not access the base table.
Any other  Table READ

When the SELECT is a tactical query, the Lock Manager downgrades the lock request from READ to ACCESS if that keeps the operation from being blocked.
SELECT AND CONSUME Not applicable Any Row hash WRITE
DELETE 

On tables without a hash or join index

 Not applicable UPI or USI Row hash WRITE
NUPI Set of rows
Any other Table
DELETE

On tables with a hash or join index

 Not applicable UPI or USI Row hash WRITE

Deletes on a table with a hash or join index also require a WRITE lock on the index table plus READ locks on other tables associated with a join index.
NUPI Row hash
Any other Table
INSERT ... SELECT Not applicable Select table
UPI or USI Row hash READ

Inserts on a table with a hash or join index also require a WRITE lock on the index table plus READ locks on other tables associated with a join index.
NUPI Row hash
Any other Table
Insert table WRITE
INSERT [VALUES]

On tables without a hash or join index

 Not applicable Not applicable Primary row WRITE
INSERT [VALUES]

On tables with a hash or join index

 Not applicable Not applicable Primary row WRITE

Inserts on a table with a hash or join index also require a WRITE lock on the index table plus READ locks on other tables associated with a join index
UPDATE

On tables without a hash or join index

 Neither UPI nor USI UPI or USI Row hash WRITE

Updates on a table that does not have a join or hash index require WRITE locks on the table.
Neither NUPI nor USI NUPI Set of rows
Any other Table
USI USI Table
UPDATE 

On tables with a hash or join index

 Neither UPI nor USI UPI or USI Row hash WRITE

Updates on a table with a join or hash index require WRITE locks on the table and the hash or join index and READ locks on other associated tables if and only if the modified base table columns are also defined for the hash or join index.
Neither NUPI nor USI NUPI Set of rows
Any other Table
USI USI Table
MERGE (Update) Neither UPI nor USI UPI or USI Row hash WRITE

MERGE places a matching rowhash lock (a rowhash-level lock based on the primary index value specified by the MATCH condition) when performing the MERGE results in an update.
Neither NUPI nor USI NUPI Set of rows
Any other Table
USI USI Table
MERGE (Insert) Not applicable Not applicable Primary row WRITE

MERGE inserts on a table with a join or hash index require WRITE locks on the hash or join index and READ locks on other associated tables if and only if the modified base table columns are also defined for the hash or join index.