Locks and Concurrency - Advanced SQL Engine - Teradata Database

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.

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.

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.