Usage Notes - Teradata Database

Locks and Concurrency

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 SQL Data Definition Language Syntax and Examples.

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