17.05 - Locks and Concurrency - Advanced SQL Engine - Teradata Database

Teradata Vantage™ - SQL Data Manipulation Language

Product
Advanced SQL Engine
Teradata Database
Release Number
17.05
Release Date
January 2021
Content Type
Programming Reference
Publication ID
B035-1146-175K
Language
English (United States)
An INSERT operation sets a WRITE lock for the table being updated at the appropriate level:
  • Table-level lock for all rows in the table on all AMPs
  • Partition lock for all rows in the selected partitions on all AMPs
  • Rowhash for the rows with the same hash value on one AMP or rowkey for the rows with the same partition and hash values on one AMP

If the target table is load isolated and the insert is not an isolated load operation, then an EXCLUSIVE lock is applied at that level, instead of a WRITE lock. If the WITH NO ISOLATED LOADING option is specified or isolated load operations are disabled on the table or in the session, then the insert is not an isolated load operation. For information on the CREATE TABLE WITH ISOLATED LOADING option and the SET SESSION FOR ISOLATED LOADING statement, see Teradata Vantage™ - SQL Data Definition Language Syntax and Examples, B035-1144.

The lock set for SELECT subquery operations depends on the isolation level for the session, the setting of the AccessLockForUncomRead DBS Control field, and whether the subquery is embedded within a SELECT operation or within an INSERT request.

Transaction Isolation Level DBS Control AccessLockForUncomRead Field Setting Default Locking Severity for Outer SELECT and Ordinary SELECT Subquery Operations Default Locking Severity for SELECT Operations Embedded Within an INSERT Request
SERIALIZABLE FALSE READ READ
TRUE READ
READ UNCOMMITTED FALSE READ
TRUE ACCESS
See also:
  • SET SESSION CHARACTERISTICS AS TRANSACTION ISOLATION LEVEL in Teradata Vantage™ - SQL Data Definition Language Detailed Topics , B035-1184
  • Teradata Vantage™ - SQL Request and Transaction Processing, B035-1142
  • Teradata Vantage™ - Database Utilities , B035-1102