Explicitly Acquiring Row Locks

You can use a SELECT ... FOR UPDATE statement to lock a row without actually changing it. For example, several triggers in Chapter 9 show how to implement referential integrity. In the EMP_DEPT_CHECK trigger (see ), the row that contains the referenced parent key value is locked to guarantee that it remains for the duration of the transaction; if the parent key is updated or deleted, referential integrity would be violated.

SELECT ... FOR UPDATE statements are often used by interactive programs that allow a user to modify fields of one or more specific rows (which might take some time); row locks on the rows are acquired so that only a single interactive program user is updating the rows at any given time.

If a SELECT ... FOR UPDATE statement is used when defining a cursor, the rows in the return set are locked before the first fetch, when the cursor is opened; rows are not individually locked as they are fetched from the cursor. Locks are only released when the transaction that opened the cursor is committed or rolled back; locks are not released when a cursor is closed.

Each row in the return set of a SELECT ... FOR UPDATE statement is locked individually; the SELECT ... FOR UPDATE statement waits until the other transaction releases the conflicting row lock. Therefore, if a SELECT ... FOR UPDATE statement locks many rows in a table and the table experiences reasonable update activity, it would most likely improve performance if you instead acquired an exclusive table lock.

When acquiring row locks with SELECT ... FOR UPDATE, you can indicate if you do or do not want to wait to acquire the lock. If you specify the NOWAIT option, you only acquire the row lock if it is immediately possible. Otherwise, an error is returned to notify you that the lock is not possible at this time. In this case, you can attempt to lock the row later. If NOWAIT is omitted, the transaction does not proceed until the requested row lock is acquired. If the wait for a row lock is excessive, users might want to cancel the lock operation and retry later; you can code such logic into your applications.

As described , a distributed transaction waiting for a row lock can timeout waiting for the requested lock if the elapsed amount of time reaches the interval set by the initialization parameter DISTRIBUTED_LOCK_TIMEOUT.