When the Request and View Referenced Include LOCKING Request Modifiers - Analytics Database - 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.