When the Request and View Referenced Include LOCKING Request Modifiers - Analytics Database - Teradata Vantage

SQL Data Manipulation Language

Deployment
VantageCloud
VantageCore
Edition
Enterprise
IntelliFlex
VMware
Product
Analytics Database
Teradata Vantage
Release Number
17.20
Published
June 2022
Language
English (United States)
Last Update
2024-04-05
dita:mapPath
pon1628111750298.ditamap
dita:ditavalPath
qkf1628213546010.ditaval
dita:id
esx1472246586715
lifecycle
latest
Product Category
Teradata Vantage™

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.