A DELETE statement sets a WRITE lock for the table, partitions, or row. For a nonconcurrent load isolated delete operation on a load isolated table, the delete operation sets an EXCLUSIVE lock.
For a SELECT subquery, the lock 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 or DELETE statement.
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 UPDATE Request |
---|---|---|---|
SERIALIZABLE | FALSE | READ | READ |
TRUE | READ | ||
READ UNCOMMITTED | FALSE | READ | |
TRUE | ACCESS |
For more information, see:
- 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