Usage Notes - Teradata Database

SQL Data Manipulation Language

Product
Teradata Database
Release Number
16.10
Published
June 2017
Language
English (United States)
Last Update
2018-04-25
dita:mapPath
psg1480972718197.ditamap
dita:ditavalPath
changebar_rev_16_10_exclude_audience_ie.ditaval
dita:id
B035-1146
lifecycle
previous
Product Category
Teradata® Database

LOCKING Request Modifier and CALL Requests

You cannot specify a LOCKING request modifier with a CALL request.

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. Overriding the default may be necessary to prevent deadlock if subsequent requests in the transaction require a higher level lock for an object.

LOCKING Request Modifier Use With DML Statements

The following table lists the associations between individual SQL DML statements and lock upgrades and downgrades at the row (rowkey or rowhash), view, base table, and database object levels:

This LOCKING modifier severity specification … Is available for this SQL DML statement …
EXCLUSIVE or

WRITE

DELETE
INSERT
MERGE
SELECT
SELECT AND CONSUME
UPDATE
READ or SHARE SELECT
ACCESS SELECT
ACCESS for LOAD COMMITTED SELECT

EXCLUSIVE and WRITE are the only lock severities for the DELETE, INSERT, MERGE, UPDATE, and SELECT AND CONSUME statements because 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 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, the system ignores them and uses WRITE.

For details about locking levels, locking severities, and the relationship between them, see SQL Request and Transaction Processing .

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.

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, the only effect of the modifier is to lock the specified object. See “Null”. 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

LOCKING ROW does not generally lock a single row, but rather all rows that hash to a specific value. It is a rowhash lock or rowkey lock, not a row lock.

The LOCKING ROW modifier cannot be used to lock multiple rowhashes. If you specify LOCKING ROW FOR ACCESS with multiple rowhashes, the lock is converted to LOCKING TABLE FOR ACCESS.

The use of LOCKING ROW on a row-partitioned table may place a rowhash or rowkey lock. The rowhash lock places a lock on a single rowhash in all partitions. The rowkey lock places a lock on a single rowhash in a single partition.

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 rowhash-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:

  • For single-table retrievals, using primary or unique secondary index searches.
  • When specified with a SELECT request, because the LOCKING ROW modifier picks up the rowhash to be locked from the SELECT request.
  • To upgrade any of the following locks:
Upgrade from this lock … To this lock …
  • READ
  • SHARE
WRITE
  • READ
  • SHARE
EXCLUSIVE
WRITE EXCLUSIVE

The system does not honor a LOCKING ROW modifier request in the following situations:

  • Where a lock on the target base table is already in place.
  • Where an aggregate/DISTINCT/GROUP BY operation is part of the SELECT request.
  • To downgrade a lock from READ to ACCESS, once that lock is in place.

    If no rowhash lock is in place already, an ACCESS lock can be set instead of a default READ rowhash lock.

Deadlock occurs when two primary index SELECTs are followed by primary index UPDATES and the SELECTs are on different rowhashes. 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 rowhash access. Deadlock occurs because the User A table-level lock request waits on the User B rowhash lock, while the User B table-level lock request waits on the User A rowhash 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 rowhash) 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:
  • You can only read from a single base table using this modifier. Attempts to perform multitable operations return an error.
  • You can specify LOCKING FOR READ OVERRIDE for any single base table, whether a rollback has been cancelled on that table or not.
  • The Optimizer only uses indexes to read base valid base tables. If you use LOCKING FOR READ OVERRIDE to access a non-valid table, then the system always uses a full-table scan.

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.