15.10 - DML Requests and Locks - Teradata Database

Teradata Database SQL Request and Transaction Processing

prodname
Teradata Database
vrm_release
15.10
category
Programming Reference
User Guide
featnum
B035-1142-151K

When it processes DML requests, Teradata Database 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.

Note: 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) whether performance of the MERGE results in an update, an insert, or neither.

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.