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
Published
January 2021
Language
English (United States)
Last Update
2021-01-22
dita:mapPath
vnq1596660420420.ditamap
dita:ditavalPath
hoy1596145193032.ditaval

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.