17.05 - When the Request and View Referenced Include LOCKING Request Modifiers - 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)

Although views are often created to enforce a LOCKING FOR ACCESS rule, any user can override the LOCKING FOR ACCESS by specifying a LOCKING FOR READ request modifier on the view. For example:

     REPLACE VIEW vprod.ad_me_inf AS
     LOCKING TABLE prod.ad_me_inf FOR ACCESS
     SELECT ad_me_id, ad_me_dsc_tx
     FROM prod.ad_me_inf;

If you do an EXPLAIN on the following query, the ACCESS lock can be seen in statement 1.

     SELECT COUNT(*)
     FROM vprod.ad_me_inf;

If you do an EXPLAIN on the following query, you can see a READ lock in statement 1 of the report.

     LOCKING TABLE vprod.ad_me_inf FOR READ
     SELECT COUNT (*)
     FROM vprod.ad_me_inf;

This behavior could be considered undesirable because the LOCKING FOR ACCESS request modifier can be overridden by anyone at any time. However, some users find this to be useful and depend on being able to override lock clauses in views by placing a lock in the request.