LOCKING Syntax | SQL Statements | Teradata Vantage - 17.05 - LOCKING Request Modifier Syntax - Advanced SQL Engine - Teradata Database

Teradata Vantage™ - SQL Data Manipulation Language

Product
Advanced SQL Engine
Teradata Database
Release Number
17.05
Release Date
January 2021
Content Type
Programming Reference
Publication ID
B035-1146-175K
Language
English (United States)
locking_specification [...] SQL_request [;]

Syntax Elements

locking_specification
LOCKING { [ DATABASE ] database_name |
          [ TABLE ] table_name |
          [ VIEW ] view_name |
          ROW
        } [ FOR | IN ] lock_type [ MODE ] [ NOWAIT ]
SQL_request
A valid SQL request.
You cannot specify LOCKING with the CALL statement.
If SQL_request is null, then the only effect of LOCK is to lock the specified object.
This specification is mandatory for row-level locking (rowhash or rowkey), but optional for database-level locking, base table-level locking, and view-level locking.
DATABASE
Optional keyword to indicate the type of object to lock is a database.
database_name
Name of the database or user to be locked.
TABLE
Optional keyword to indicate the type of object to lock is a table.
table_name
Name of the base table to be locked.
VIEW
Optional keyword to indicate the type of object to lock is a view.
view_name
Name of the view to be locked.
ROW
Optional keyword specifying rowhash or rowkey level for locking in accordance with the defining statement. See Using LOCKING ROW.
lock_type
{ ACCESS |
  { EXCLUSIVE | EXCL } |
  READ [ OVERRIDE ] |
  SHARE |
  WRITE |
  CHECKSUM |
  LOAD COMMITTED
}
MODE
Optional keyword following the lock type.
NOWAIT
If the indicated lock cannot be obtained, the request should be aborted.
Specify this option for situations in which it is not desirable to have a request wait for resources, and possibly tie up resources another request could use, while waiting.
ACCESS
Permits selection of data from a base table that can be locked for write access by other users.
The data selected using an ACCESS lock can be inconsistent because the data may be modified concurrently with the request. Therefore, you should only use this lock for casual inspection of data.
Placing an ACCESS lock requires the SELECT privilege on the specified object.
EXCLUSIVE
EXCL
Excludes all other users.
This is the most restrictive lock.
EXCLUSIVE locks are rarely used except to make structural changes to a database.
Placing an EXCLUSIVE lock on a database object requires the DROP privilege on that object.
READ
SHARE [Deprecated]
Ensures data consistency during a read operation such as a SELECT request.
Multiple users can concurrently hold a READ lock on a base table. As long as a READ lock is in place, no modification of the object is allowed.
Placing a READ lock requires the SELECT privilege on the specified object.
OVERRIDE
Permit a READ lock for a single-table SELECT operation when a rollback on an underlying base table in the query was canceled using the Recovery Manager utility CANCEL ROLLBACK ON TABLE command. For more information, see Teradata Vantage™ - Database Utilities , B035-1102 .
You can only specify the OVERRIDE option with a READ lock request.
WRITE
Enables a single user to modify data.
As long as the WRITE lock is in place, all other users are excluded from viewing or modifying the object except readers who are viewing data using an ACCESS lock.
Until a WRITE lock is released, no new READ locks are permitted on the locked object.
Placing a WRITE lock requires an UPDATE, INSERT, or DELETE privilege on the specified object.
CHECKSUM
Used only for updatable cursor queries submitted outside of stored procedures.
LOAD COMMITTED
Load-committed read mode. This option is independent of the session transaction isolation level.
Permits selection of committed data from a base load isolated table that can be locked for write access by other transactions.
Placing an LOAD COMMITTED lock requires the SELECT privilege on the specified object.
Internally, an ACCESS lock is applied when load-committed read mode is specified.