Oracle PL/SQL Programming, Third Edition

SELECT...FOR UPDATE

When you issue a SELECT statement against the database to query some records, no locks are placed on the selected rows. In general, this is a wonderful feature because the number of records locked at any given time is kept to the absolute minimum: only those records that have been changed but not yet committed are locked. Even then, others will be able to read those records as they appeared before the change (the “before image” of the data).

There are times, however, when you will want to lock a set of records even before you change them in your program. Oracle offers the FOR UPDATE clause of the SELECT statement to perform this locking.

When you issue a SELECT...FOR UPDATE statement, the RDBMS automatically obtains exclusive row-level locks on all the rows identified by the SELECT statement, holding the records “for your changes only” as you move through the rows retrieved by the cursor. No one else will be able to change any of these records until you perform a ROLLBACK or a COMMIT—but other sessions can still read the data.

Here are two examples of the FOR UPDATE clause used in a cursor:

CURSOR toys_cur IS
   SELECT name, manufacturer, preference_level, sell_at_yardsale_flag
     FROM my_sons_collection
    WHERE hours_used = 0
      FOR UPDATE;

CURSOR fall_jobs_cur IS
   SELECT task, expected_hours, tools_required, do_it_yourself_flag
     FROM winterize
    WHERE year = TO_CHAR (SYSDATE, 'YYYY')
      FOR UPDATE OF task;

The first cursor uses the unqualified FOR UPDATE clause, while the ...