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

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

SQL Request and Transaction Processing

Product

Advanced SQL Engine

Teradata Database

Release Number

17.05

17.00

Published

June 2020

Language

English (United States)

Last Update

2021-01-24

dita:mapPath

ykx1561500561173.ditamap

dita:ditavalPath

lze1555437562152.ditaval

dita:id

B035-1142

lifecycle

Product Category

Teradata Vantage™

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.

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
		Any other	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
	Neither NUPI nor USI	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
	Neither NUPI nor USI	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
	Neither NUPI nor USI	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.