From:Steve Adams
Date:17-Oct-2000 09:42
Subject:   SELECT FOR UPDATE locking problems

Andrew Stewart offered the following alternatives to using the new SKIP LOCKED syntax for control table updates. This may be useful for those not yet on release 8.1. Thanks Andrew.

Hope you don't mind an unsolicited suggestion (please let me know if I'm breaking the list protocol). I've had to perform a similar task in the past. If you're environment doesn't support the SKIP LOCKED feature, there are a couple of alternative approaches which I've used with dozens parallel processes (a similar control table type of arrangement, where each process is trying to find the id of an unused item from a shared pool of resources).

The simplest approach was just to keep the lock time to an absolute minimum, i.e. do something like

UPDATE    control_table
SET       resource_in_use = 'Y'
WHERE     resource_in_use = 'N'
AND       ROWNUM = 1
RETURNING resource_id, resource_description....
INTO     :b1, :b2....
and then commit immediately. ROWNUM = 1 makes the process pick just one arbitrary record, with the RETURNING clause gets around the need for a separate select to get whatever details you need about the resource. The processes will still queue behind the one which holds the lock, but the lock time itself is as low as I was able to get it.

If this doesn't work for you, you can implement the SKIP LOCKED feature programmatically by using two cursors. The outer cursor selects the ROWID of records which are flagged as available, but doesn't lock them. For each record fetched, an inner cursor uses the ROWID returned to attempt to lock the record with a SELECT FOR UPDATE...NOWAIT, checking that the row is still flagged as available. If it succeeds, it then updates the record and continues with its job. If it fails because the record is locked, or the record has been updated since the outer cursor was opened and is now flagged as busy, it returns to the outer cursor, fetches the next record and repeats until it succeeds or runs out of control table.

Hope that's some help.

This sounds like a classic application for the undocumented SELECT FOR UPDATE SKIP LOCKED command. It does exactly what you want. The potential problem is that most client environments will not recognise it as valid SQL syntax. However, if you are on 8.1, then I believe that you can use the following native dynamic SQL syntax.

     DECLARE
        TYPE ControlCursorType IS REF CURSOR;
        control_cursor ControlCursorType;
        my_key NUMBER;
     BEGIN
        OPEN control_cursor FOR
          'SELECT key_value
           FROM control_table
           WHERE -- conditions
           FOR UPDATE SKIP LOCKED';
        LOOP
           FETCH control_cursor INTO my_key;
           EXIT WHEN control_cursor%NOTFOUND;
           -- do whatever you need to
        END LOOP;
        CLOSE control_cursor;
     END;

I'm getting unusual behavior when running multiple copies of a program that contains a SELECT FOR UPDATE statement. The table is a control table, where the program finds out if there is an available thread for it to run. If so, it grabs the thread number and updates the control table to indicate that that thread is now in use.

The following scenario illustrates how we are getting locking. Program 1 comes in, and issues its SELECT FOR UPDATE. Assume it locks row 1. Program 2 comes in, and also issues a SELECT FOR UPDATE. Instead of selecting an unlocked row, it blocks behind program 1 until program 1 commits. When it does, program 2's query is processed and returns row 2, since row 1 has been updated by program 1 to say that thread 1 is no longer available. Though the wait times aren't very large by themselves, the add up when hundreds of these programs are active simultaneously.

I looked at your *excellent* web site and found the note on row-level locks. I changed the program to be SELECT FOR UPDATE NOWAIT. If ORA-54 occurs, I can then compute the rowid I just blocked on from V$SESSION and issue a new statement SELECT FOR UPDATE WHERE ROWID != . This works for 2 concurrent programs. However, in the scenario above, if program 3 fired when rows 1 and 2 were both locked, the SELECT FOR UPDATE NOWAIT would reveal that row 1 was the one waited for. If it then issued SELECT FOR UPDATE WHERE ROWID != , it would now block on row 2.

Is there any way to get around this locking behavior? I'm involved in a benchmark, so we're trying to squeeze every last second out of the application that we can. Thanks.