The FOR UPDATE Clause

You may want to lock rows before you update or delete rows. Add the FOR UPDATE clause in the cursor query to lock the affected rows when the cursor is opened. Because the Oracle Server releases locks at the end of the transaction, you should not commit across fetches from an explicit cursor if FOR UPDATE is used.

In the syntax:

  column_reference  is a column in the table against which the query is performed. (A   list of columns may also be used.)

  NOWAIT  returns an Oracle error if the rows are locked by another session

The FOR UPDATE clause is the last clause in a select statement, even after the ORDER BY, if one exists. When querying multiple tables, you can use the FOR UPDATE clause to confine row locking to particular tables. Rows in a table are locked only if the FOR UPDATE clause refers to a column in that table. FOR UPDATE OF col_name(s) locks rows only in tables that contain the col_name(s).

The SELECT ... FOR UPDATE statement identifies the rows that will be updated or deleted, then locks each row in the result set. This is useful when you want to base an update on the existing values in a row. In that case, you must make sure the row is not changed by another user before the update.

The optional NOWAIT keyword tells Oracle not to wait if requested rows have been locked by another user. Control is immediately returned to your program so that it can do other work before trying again to acquire the lock. If you omit the NOWAIT keyword , Oracle waits until the rows are available.

Note: If the Oracle server cannot acquire the locks on the rows it needs in a SELECT FOR UPDATE, it waits indefinitely. You can use the NOWAIT clause in the SELECT FOR UPDATE statement and test for the error code that returns because of failure to acquire the locks in a loop. You can retry opening the cursor n times before terminating the PL/SQL block. If you have a large table, you can achieve better performance by using the LOCK TABLE statement to lock all rows in the table. However, when using LOCK TABLE, you cannot use the WHERE CURRENT OF clause and must use the notation WHERE column = identifier.

It is not mandatory that the FOR UPDATE OF clause refer to a column, but it is recommended for better readability and maintenance.

Note: The WHERE CURRENT OF clause is explained later in this lesson.

The FOR UPDATE clause identifies the rows that will be updated or deleted, then locks each row in the  result set. This is useful when you want to base an update on the existing values in a row. In that case, you must make sure the row is not changed by another user before the update.

The WHERE CURRENT OF Clause

When referencing the current row from an explicit cursor, use the WHERE CURRENT OF clause. This allows you to apply updates and deletes to the row currently being addressed, without the need to explicitly reference the ROWID. You must include the FOR UPDATE clause in the cursor query so that the rows are locked on OPEN.