DML Requests and Locks | Transaction Processing | Teradata Vantage - 17.10 - DML Requests and Locks - Advanced SQL Engine - Teradata Database

Teradata Vantage™ - SQL Request and Transaction Processing

Product
Advanced SQL Engine
Teradata Database
Release Number
17.10
Release Date
July 2021
Content Type
Programming Reference
User Guide
Publication ID
B035-1142-171K
Language
English (United States)

When it processes DML requests, Vantage accesses the information it needs 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 the operation would otherwise be 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 tables associated with it 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 tables associated with it if and only if the modified base table columns are also defined for the hash or join index.