LOCKING Request Modifier
Purpose
Locks a database, table, view, or row‑hash at the specified severity level, overriding the default locking level the system places on a database, table, view, or row‑hash in response to a request.
Syntax
where:
|
Syntax Element … |
Specifies … |
|
DATABASE |
an optional keyword to indicate the type of object to lock. |
|
ROW |
an optional keyword specifying the row‑hash level for locking in accordance with the defining statement (see “Using LOCKING ROW” on page 418). |
|
database_name |
the name of the database or user to be locked. |
|
table_name |
the name of the base table to be locked. |
|
view_name |
the name of the view to be locked. |
|
FOR |
an introduction to the type of lock to be placed. |
|
ACCESS READ SHARE WRITE EXCLUSIVE CHECKSUM |
the severity, or granularity, of the lock to be placed. SHARE is a deprecated synonym for READ. CHECKSUM is only available for embedded SQL and stored procedure applications with updatable cursors. |
|
OVERRIDE |
to permit a READ lock for a single‑table SELECT operation when a rollback on an underlying base table in the query was cancelled using the Recovery Manager utility CANCEL ROLLBACK ON TABLE command. See Utilities. You can only specify the OVERRIDE option with a READ lock request. |
|
MODE |
an optional keyword. |
|
NOWAIT |
that 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. |
|
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‑hash‑level locking, but optional for database-, base table-, and view‑level locking. |
ANSI Compliance
LOCKING is a Teradata extension to the ANSI SQL:2011 standard.
Other SQL dialects support similar non‑ANSI standard statements with names such as the following:
Required Privileges
None.
Applicability
Besides its common use with DML statements, you can also specify the LOCKING modifier with most DDL, DCL, SHOW and HELP statements. In particular, it can be used with CREATE INDEX and COLLECT STATISTICS to override the default locking severities to prevent deadlocks. LOCKING is also frequently used within view definitions.
You cannot specify a LOCKING request modifier with a CALL request.
Locking Severities
You can specify the following lock severities. For additional information about locking, see “Locking and Transaction Processing” in SQL Request and Transaction Processing.
|
Lock Type |
Description |
|
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. |
|
READ |
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. SHARE is a deprecated synonym for READ. |
|
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. |
|
EXCLUSIVE |
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. |
|
CHECKSUM |
Used only with updatable cursors in embedded SQL and stored procedures. |
When Explicit Locking Is Necessary
The system automatically places locks on database objects each time an SQL request is performed. Therefore, the use of the LOCKING modifier is seldom necessary. Explicit user‑specified locks are only useful when you need to override the automatic locking specified by the Optimizer and placed by the Lock Manager.
Cancelling a Lock
An operation can wait for a requested lock indefinitely unless you specify the NOWAIT option. If you are working interactively and do not want to wait for a lock, you can issue the BTEQ .ABORT command to cancel the transaction.
LOCKING Request Modifier Use With Different DML Statements
The following table indicates associations between individual SQL DML statements and lock upgrades and downgrades at the row‑hash, view, base table, and database object levels:
|
This LOCKING modifier severity specification … |
Is available for this SQL DML statement … |
|
EXCLUSIVE |
DELETE |
|
WRITE |
SELECT |
|
READ |
SELECT |
|
ACCESS |
SELECT |
The reason that the only modifier for the DELETE, INSERT, MERGE, UPDATE, and SELECT AND CONSUME statements is LOCKING FOR EXCLUSIVE is that the default lock severity for these statements is WRITE. You cannot downgrade a WRITE lock for these statements because doing so would compromise the integrity of the database. Because the SELECT statement does not change data, and therefore its actions cannot compromise database integrity, you are permitted to change its default locking severity to any other severity.
You can specify lower locking severity levels than are listed in this table, but if you do, Teradata Database ignores them.
For details about locking levels, locking severities, and the relationship between them, see SQL Request and Transaction Processing.
Positioning Explicit Lock Requests
When you need to place an explicit lock, the LOCKING modifier must precede the SQL request that is to be affected by the requested lock.
The system places the requested lock on the object referenced by the LOCKING modifier for the duration of the transaction containing the modified SQL request. If the transaction is a single‑statement request, then the specified lock is only in effect for the duration of the request.
Using Locks with NULL SQL Requests
If the LOCKING modifier is followed by a null SQL request (see “Null” on page 299), the only effect of the modifier is to lock the specified object. While the LOCKING modifier can be used with a null request, it is best to use LOCKING with a user-generated transaction or as part of a multistatement request.
Using LOCKING ROW
Note that LOCKING ROW does not generally lock a single row, but rather all rows that hash to a specific value. It is a row hash lock, not a row lock.
The LOCKING ROW modifier cannot be used to lock multiple row‑hashes. If you specify LOCKING ROW FOR ACCESS with multiple row‑hashes, the lock is converted to LOCKING TABLE FOR ACCESS, but a WRITE or EXCLUSIVE row‑hash lock severity is not transferred to a table‑level lock. If a LOCKING ROW FOR WRITE severity is not applicable, the default table‑level lock severity is maintained.
A single value in an IN clause is accepted as a valid row‑hash lock specification.
The use of LOCKING ROW prevents possible deadlocks occurring when two simultaneous primary index SELECTs are followed by an UPDATE, DELETE, or INSERT on the same row.
For example:
User A:
BEGIN TRANSACTION;
SELECT y
FROM t
WHERE x=1;
UPDATE t
SET y=0
WHERE x=1;
END TRANSACTION;
User B:
BEGIN TRANSACTION;
SELECT z
FROM t
WHERE x=1;
UPDATE t
SET z=0
WHERE x=1;
END TRANSACTION;
The Lock Manager assigns a row‑hash‑level READ lock when a simple SELECT request is made using a UPI, NUPI, or USI.
The User A UPDATE request ROW FOR WRITE lock request waits for the User B ROW OR READ lock to be released. The ROW FOR READ lock is not released because the User B UPDATE request ROW FOR WRITE lock request is also waiting for the User A ROW FOR READ lock to be released. Previously, this deadlock was avoided by using the LOCKING TABLE modifier:
BEGIN TRANSACTION;
LOCKING TABLE t FOR WRITE
SELECT z
FROM t
WHERE x = 1;
UPDATE ...
END TRANSACTION;
Locking an entire base table across all AMPS is undesirable, and the use of LOCKING ROW here prevents the need to lock an entire base table across all AMPs.
The following example illustrates the use of LOCKING ROW:
User A:
BEGIN TRANSACTION;
LOCKING ROW FOR WRITE
SELECT y
FROM t
WHERE x=1;
UPDATE t
SET y=0
WHERE x=1
END TRANSACTION;
User B:
BEGIN TRANSACTION;
LOCKING ROW FOR WRITE
SELECT z
FROM t
WHERE x=1;
UPDATE t
SET z=0
WHERE x=1;
No deadlock occurs because the User B LOCKING ROW FOR WRITE request is blocked by the User A LOCKING ROW FOR WRITE. The User B LOCKING ROW FOR WRITE request completes when the User A END TRANSACTION statement is complete.
The system honors a LOCKING ROW modifier request only in the following situations:
|
Upgrade from this lock … |
To this lock … |
|
|
WRITE |
|
|
EXCLUSIVE |
|
WRITE |
EXCLUSIVE |
The system does not honor a LOCKING ROW modifier request in the following situations:
If no row‑hash lock is in place already, an ACCESS lock can be set instead of a default READ row‑hash lock.
Deadlock occurs when two primary index SELECTs are followed by primary index UPDATES and the SELECTs are on different row‑hashes. This is because a primary index update requires a table‑level WRITE lock on the target base table.
For example:
User A:
BEGIN TRANSACTION;
LOCKING ROW WRITE
SELECT x
FROM t
WHERE x = 1; (x is UPI, NUPI, or USI)
UPDATE t
SET x=2
WHERE x=1;
User B:
BEGIN TRANSACTION;
LOCKING ROW WRITE
SELECT x
FROM t
WHERE x=2;
UPDATE t
SET x=1
WHERE x=2;
The User B SELECT ROW WRITE lock is not queued behind the User A transaction because it has a different row‑hash access. Deadlock occurs because the User A table‑level lock request waits on the User B row‑hash lock, while the User B table‑level lock request waits on the User A row‑hash lock.
Multiple Locks
Multiple LOCKING modifiers can precede a request if it is necessary to lock more than one object at the same time, for example:
LOCKING TABLE employee FOR ACCESS
LOCKING TABLE department FOR ACCESS
SELECT name, loc
FROM employee, department
WHERE (empno=mgrno);
Referencing a Locked Object
The object that is locked by the LOCKING modifier does not have to be referenced in a subsequent SQL request.
A lock can be executed separately from the SQL request that it precedes. Therefore, a LOCKING modifier must reference the object on which a lock is being placed. The objects referenced in the SQL request have no effect on the execution of a LOCKING modifier.
When a LOCKING modifier references a view, the specified lock is applied to all underlying base tables. For example, if a view refers to tables t1 and t2, then a lock on that view would apply to both tables.
LOCKING DATABASE is the only way to lock a database or user.
Specify the Keyword For the Object To Be Locked
Be sure to specify the keyword for the object (database, table, view, or row‑hash) that is to be locked.
For example, if a database and table are both named accntrec, then you could specify the following form:
LOCKING TABLE accntrec FOR ACCESS
SELECT *
FROM accntrec;
If the TABLE keyword had not been included, the lock would have been placed on the accntrec database.
Locks and Views
A LOCKING modifier can be specified in a view definition. When a view is defined with a LOCKING modifier, the specified lock is placed on the underlying base table set each time the view is referenced in an SQL request.
For more information on defining views, see CREATE VIEW and CREATE RECURSIVE VIEW in SQL Data Definition Language.
When the Request and View Referenced Include LOCKING Request Modifiers
Although views are often created to enforce a LOCKING FOR ACCESS rule, any user can override the LOCKING FOR ACCESS by specifying a LOCKING FOR READ request modifier on the view. For example:
REPLACE VIEW vprod.ad_me_inf AS
LOCKING TABLE prod.ad_me_inf FOR ACCESS
SELECT ad_me_id, ad_me_dsc_tx
FROM prod.ad_me_inf;
If you do an EXPLAIN on the following query, the ACCESS lock can be seen in statement 1.
SELECT COUNT(*)
FROM vprod.ad_me_inf;
If you do an EXPLAIN on the following query, you can see a READ lock in statement 1 of the report.
LOCKING TABLE vprod.ad_me_inf FOR READ
SELECT COUNT (*)
FROM vprod.ad_me_inf;
This behavior could be considered undesirable because the LOCKING FOR ACCESS request modifier can be overridden by anyone at any time. However, some users find this to be useful and depend on being able to override lock clauses in views by placing a lock in the request.
READ Locks and Cancelled Rollback Operations
When you use the RcvManager utility to cancel a transaction rollback, the system marks the base table on which the rollback was cancelled as nonvalid. As a result, the table cannot be updated. With some restrictions, you can inspect the rows of the non‑valid base table if you specify a LOCKING FOR READ OVERRIDE modifier.
The following rules document the restrictions on the use of a LOCKING FOR READ OVERRIDE modifier:
Determining Which Locks Are Set
Use the EXPLAIN request modifier with an SQL request to determine what locks are set when the request is executed.
Example
The following LOCKING clause can be used to select data from the employee table while it is being modified:
LOCKING TABLE personnel.employee FOR ACCESS
SELECT name, salary
FROM employee
WHERE salary < 25000 ;
This query might do any of the following things.
Example
The system deals properly with the synchronization of base data rows and index subtable rows. However, an ACCESS lock can allow inconsistent results even when secondary indexes are used in conditional expressions because index constraints are not always rechecked against the data row.
For example, suppose a column named qualify_accnt is defined as a secondary index for a base table named accnt_rec as it does in the following request:
LOCKING TABLE accnt_rec FOR ACCESS
SELECT accnt_no, qualify_accnt
FROM accnt_rec
WHERE qualify_accnt = 1587;
The request could return:
accnt_no qualify_accnt
‑‑‑‑-‑‑‑ -‑‑‑‑‑‑‑‑‑‑‑‑
1761 4214
In this case, the value 1587 was found in the secondary index subtable and the corresponding data row was selected and returned. However, the data for account 1761 had been changed by another user while the retrieval was in process. This is referred to as a dirty read. See SQL Request and Transaction Processing.
Anomalous results like these are possible even if the data is changed only momentarily by a transaction that is ultimately aborted. The ACCESS lock is most useful to those who simply want an overview of data and are not concerned with consistency.
Example : LOCKING ROW
This example shows the proper use of a rowhash lock.
CREATE TABLE customer (
cust_id INTEGER,
phone INTEGER,
fax INTEGER,
telex INTEGER)
PRIMARY INDEX (cust_id),
UNIQUE INDEX(fax),
INDEX(telex):
CREATE TABLE sales (
custcode INTEGER,
zip INTEGER,
salesvol INTEGER);
User A:
BEGIN TRANSACTION;
LOCKING ROW EXCLUSIVE
SELECT phone
FROM customer
WHERE cust_id=12345;
UPDATE customer
SET phone=3108292488
WHERE cust_id=12345;
The User A EXCLUSIVE rowhash lock prevents another user from accessing the same row.
In the following, the user A rowhash WRITE lock, in conjunction with LOCKING TABLE, prevents user B from accessing the same row:
User A:
BEGIN TRANSACTION;
LOCKING TABLE sales FOR READ,
LOCKING ROW FOR WRITE
SELECT telex
FROM customer
WHERE fax=0;
UPDATE customer
SET telex=0
WHERE fax=0;
SELECT zip
FROM sales
WHERE custcode=111;
SELECT salesvol
FROM sales
WHERE custcode=222;
...
END TRANSACTION;
User B:
BEGIN TRANSACTION;
LOCKING ROW FOR WRITE
SELECT *
FROM customer
WHERE cust_id=12345
INSERT INTO customer (12345, 3108284422, 3108684231, 5555);
END TRANSACTION;
The User B LOCKING ROW FOR WRITE modifier waits until the User A transaction ends before it can be completed.
Example : NOWAIT Option
You have a request that you do not want to be placed in the lock queue if it cannot be serviced immediately. In this case, use the NOWAIT option. For example:
LOCKING employee FOR READ NOWAIT
SELECT employee_number, last_name
FROM employee
WHERE department_number=401;
*** Failure 7423 Object already locked and NOWAIT.
Transaction Aborted. Statement# 1, Info =0
Another request had employee locked, so you must resubmit the request.
LOCKING employee FOR READ NOWAIT
SELECT employee_number, last_name
FROM employee
WHERE department_number=401;
*** Query completed. 7 rows found. 2 columns returned.
*** Total elapsed time was 1 second.
employee_number last_name
--------------- --------------------
1003 Trader
1004 Johnson
1013 Phillips
1002 Brown
1010 Rogers
1022 Machado
1001 Hoover
This time, no locks were being held on employee, so the request completed successfully.