17.05 - Using LOCKING ROW - 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 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 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 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;

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 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.