JL Computer Consultancy

A recent article described a way of emulating some aspects of Oracle Parallel Query engine for processing very large tables in calculated chunks bounded by rowid ranges. The main purpose of that article was simply to split the table up so that each chunk could be updated independently with minimum contention and wasted I/O.

This article describes a general-purpose mechanism that can be used to allow multiple concurrent processes to run through a list of (similar) tasks grabbing one task at a time, processing, and then going back for the next available task until all the tasks are complete. In a follow-up article I will show how to combine the table-splitting tools with the concurrency package to produce a simple program that will:

        break up a table into a collection of chunks then

        kick off an arbitrary number of concurrent processes to update randomly selected chunks

By combining the two mechanisms in this way you can set up a very simple framework for updating very large tables (or doing other large jobs) very rapidly with minimum contention whilst taking advantage of all the resources available to you.

The Strategy:

The basic idea is very simple: we define a table which identifies a list of tasks, and holds a flag identifying the state of that task. The states I cater for are: 'Awaiting processing (or New)', 'Being processed (or Active)', 'Completed', 'Error'. The procedures we need to create are essentially processes which grab a row in one state, change its state, and commit.

The only important thing about implementing these procedures is to ensure that cater for concurrent usage; this is quite easy to manage by ensuring that all attempts to acquire a row from the table are done using 'select for update nowait' with a trap for 'resource busy (Oracle error 54)' and then issuing a commit as soon as they have done a state change. The following extract is the most significant section of code and demonstrates the idea:

The presence of the commit is probably the most important feature to consider when using this package - it is deliberate and in my opinion necessary to ensure that the mechanism cannot be oncorrectly used to produce contention, long queues and dead-locks and accidetal re-processing - however it is important to remember (for example) that if the code you write to do a particular task fails, then you have to do a rollback for your changes before you call the package to mark the task as Errored.

Code Sample

procedure allocate_target_item(

               i_driving_task         in      varchar2,

               o_payload                 out  varchar2,

               io_return_code         in out  number,

               io_error_message       in out  varchar2

) is

m_rowid        rowid;

m_payload      varchar2(40);

m_loop         number;

begin

        io_return_code := c_success;

        io_error_message := null;

        o_payload := null;

        commit;

        for m_loop in 1..5 loop

        begin

               select  rowid, payload

               into    m_rowid, m_payload

               from    parallel_allocation_list

               where   driving_task = i_driving_task

               and     status = c_awaiting_processing

               and     rownum = 1

               for update of status nowait;

        exception

               when no_data_found then

                       io_return_code := c_no_rows_left;

                       io_error_message := sqlerrm;

                       return;

               when nowait_failed then 

                       if m_loop = 5 then

                               io_return_code := c_row_locked;

                               io_error_message := sqlerrm;

                               return;

                       else

                               sys.dbms_lock.sleep(1);

                       end if;

               when others then

                       io_return_code := c_general_error;

                       io_error_message := sqlerrm;

                       return;

        end;

        end loop;

        update parallel_allocation_list 

        set status = c_being_processed

        where rowid = m_rowid;

        o_payload := m_payload;

        commit;

end allocate_target_item;

The code was written on Oracle 7.2, so there are a few changes that could be made to improve the efficiency and reduce the volume of code. The most significant change I plan to make, though, is in the mechanism of the driving table - in theory each class of task could have its own table with its own payload type, in the short term I created a static table and added an extra column to it to identify the class of task (hence class of concurrent processes) that a row was relevant to.

One little warning - as you can see the package does a back-off if the row it tried to grab was locked. To do this it calls the dbms_lock.sleep procedure that is usually owned by SYS. The id that owns my code has to have the privilege to execute this package granted to it.

Apart from the obvious 'grab a row', the package as a whole contains a total of 10 procedures:

Between them, these 10 procedures should allow you to create simple, but robust, batch processes to handle arbitrarily large groups of similar tasks with an arbitrary degree of concurrency. (My first use was for a batch run to turn 104 tables of weekly detail into 104 tables of summaries each Saturday).

There are three pieces of code to collect:

        create the driving table

        create the package header

        create the package body