15.10 - LOCKING Clause - Teradata Database

Teradata Database SQL Data Definition Language Syntax and Examples

prodname
Teradata Database
vrm_release
15.10
created_date
December 2015
category
Programming Reference
featnum
B035-1144-151K

For more information on the LOCKING clause, see SQL Data Manipulation Language, B035-1146.

LOCKING

Type of lock to be placed on a database, table, view, or row hash. This setting will override any default lock placed on that object by the system.

You can specify a LOCKING clause in the seed portion of a recursive view definition, but not in the recursive portion of its definition.

DATABASE

Specifies that a lock is to be placed at the database level for this view definition.

database_name
user_name
Name of the database or user to be locked.

TABLE

Specifies that a lock is to be placed at the table or view level for this view definition.

table_name
Name of a user base table to be locked for this view definition.
You can create views that reference global temporary tables and volatile tables.
A view can reference a global temporary trace table, but such a view is not updatable. See CREATE GLOBAL TEMPORARY TRACE TABLE.
A recursive view definition can reference both row-level security-protected (RLS) tables and non-RLS tables, or other views that are based upon RLS tables, but all RLS base tables referenced in the view must have the same RLS constraints. It is not necessary to specify the row-level security constraint columns in the view definition. However when users access the view, the system enforces row-level security constraints for base tables regardless of whether the constraints are part of the view definition.
database_name
user_name
Name of the containing database or user for table_name if different from the current database or user.

VIEW

Specifies that a lock is to be placed at the view level for this view definition.

view_name
Name of a view to be locked for this view definition.
database_name
user_name
Name of the containing database or user for view_name if different from the current database or user.

ROW

Specifies that a lock is to be placed at the row hash level for this view definition.

FOR

Optional keyword that introduces the severity of the lock to be placed.

lock type

Severity of the lock to be placed when accessing base tables through this view as:
  • ACCESS
  • READ
  • WRITE
  • EXCLUSIVE
  • LOAD COMMITTED

MODE

Optional keyword.

NOWAIT

Specifies that if the indicated lock cannot be obtained, the request using this view should be aborted.

This option is used to avoid a potential deadlock situation, where a request is waiting for resources, possibly tying up other resources in the process of waiting for a lock to become available.