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