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.