Using LOCKING ROW - Teradata Vantage

LOCKING ROW locks all rows that hash to a specific value. LOCKING ROW 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 SELECT statements 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 shows 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;

A deadlock does not occur 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:

Lock to Upgrade	Upgraded 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 the User B request 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.