16.20 - Overriding LOCKING FOR READ in a View - Teradata Database - Teradata Vantage NewSQL Engine

Teradata Vantage™ SQL Data Definition Language Detailed Topics

Product
Teradata Database
Teradata Vantage NewSQL Engine
Release Number
16.20
Release Date
March 2019
Content Type
Programming Reference
Publication ID
B035-1184-162K
Language
English (United States)

A LOCKING modifier that specifies locking for ACCESS can be used in a CREATE VIEW statement to give concurrent access to ad hoc users and users who will modify the data.

A READ lock in a view can be overridden by placing a LOCKING FOR ACCESS modifier on the view.

For example, assume that an ad hoc user selects data at the same time another user attempts to modify the data. The READ lock placed by the ad hoc user prevents the modifying user from accessing the data because update transactions require a WRITE lock. By creating a view for the ad hoc user that specifies an ACCESS lock, that user cannot prevent a modifying user from completing any desired table modifications.

An ACCESS lock allows data to be retrieved during write activities. Be aware that a view defined with an ACCESS lock might display inconsistent results.