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)

A DML SELECT sets a default READ lock on the tables or rows referenced in the SELECT statement, depending on session isolation level (refer to the table, below) and the locking level the Lock Manager imposes or the locking level you specify using the LOCKING modifier. See LOCKING Request Modifier. If the SELECT statement references a view, then the system places a READ lock on each of its underlying tables.

This topic does not apply to DDL statements such as CREATE JOIN INDEX and CREATE VIEW that nest SELECT subqueries in their object definition statements.

For outer SELECT statements and SELECT subqueries that are not nested within a DML statement that manipulates data using a DELETE, INSERT, MERGE, or UPDATE statement, the default locking severity is always READ whether the session transaction isolation level is SERIALIZABLE.

If the session transaction isolation level is READ UNCOMMITTED and the DBS Control field AccessLockForUncomRead is set to TRUE, then the default locking level for SELECT operations depends on depends on the factors explained in the following paragraph and table.

For SELECT subqueries that are nested within a DML statement that manipulates data using a DELETE, INSERT, MERGE, or UPDATE statement, the default locking severity is ACCESS when the DBS Control field AccessLockForUncomRead is set TRUE and the transaction isolation level for the session is READ UNCOMMITTED, as the following table indicates.

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 DELETE, INSERT, MERGE, or UPDATE Statements
SERIALIZABLE FALSE READ READ
TRUE READ
READ UNCOMMITTED FALSE READ
TRUE ACCESS

When a SELECT statement does not specify a LOCKING modifier, but a view it uses to access tables does, the database places the lock specified by the view and does not comply with the defaults described in the preceding table.

For example, suppose you create this view.

     CREATE VIEW access_view (a, b) AS
     LOCKING TABLE accntrec FOR ACCESS
     SELECT accntno, qualifyaccnt
     FROM accntrec
     WHERE qualifyaccnt = 1587;

This SELECT statement places an ACCESS lock on table accntrec regardless of the specified session isolation level and setting of the AccessLockForUncomRead field.

     SELECT a, b
     FROM access_view;

For details, see SET SESSION CHARACTERISTICS AS TRANSACTION ISOLATION LEVEL in Teradata Vantage™ - SQL Data Definition Language Syntax and Examples, B035-1144.