17.10 - Overriding LOCKING FOR READ in a View - Advanced SQL Engine - Teradata Database

Teradata Vantage™ - SQL Data Definition Language Detailed Topics

Advanced SQL Engine
Teradata Database
Release Number
Release Date
July 2021
Content Type
Programming Reference
Publication ID
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.