Re: Stored Procedure with concurrent SELECT / UPDATE

From: Mau C <nobody_at_hotmail.com>
Date: Tue, 08 Feb 2011 09:29:11 +0100
Message-ID: <iiquso$go1$1_at_nnrp-beta.newsland.it>

Il 07/02/2011 18.59, Mark D Powell ha scritto:
> I hate to make suggestions without knowing what you are really after
> but here goes:

You are right.
This example is like a job scheduler or, if you prefer, a "process starter" :

a) it takes the first 10 available jobs (row=job)
b) it marks freed those rows and pass them to an external client
c) the external client processes those jobs and, when each job is

completed, deletes the corresponding row.

The stored procedure below deals with the a) and b) steps.

> Have the action select the keys to use via a stored function that uses
> an anonymous transaction to select 10 rows where the status = 0 using
> select for update nowait, immediately update the status, commit, and
> pass the selected keys back to your applicaiton. I would think that
> this process would also need to update the selected rows with
> information identifing the selecting process so the rows can be marked
> freed if the application does not use them.

According to the previous definition of Tmp_Table I've created this piece of code:

CREATE type tRecord
AS
  OBJECT
  (
    pkid INTEGER,
    field1 VARCHAR2(12),
    field2 VARCHAR2(12) ) ;
  /
CREATE OR REPLACE type vRecord
IS
  TABLE OF tRecord;
  /

CREATE OR REPLACE
PROCEDURE pSelectTMP(

    o OUT SYS_REFCURSOR )
AS
  PRAGMA AUTONOMOUS_TRANSACTION;
  v_record vrecord := vRecord();
BEGIN
  FOR cur_row IN
  (SELECT rowid,
    pkid,
    field1,
    field2
  FROM tmp_table
  WHERE status=0
  AND rownum <=10 FOR UPDATE OF status
  )
  LOOP
    v_record.extend;

v_record(v_record.Last):=tRecord(cur_row.pkid,cur_row.field1,cur_row.field2);

    UPDATE tmp_table SET status=1 WHERE rowid=cur_row.rowid;   END LOOP;
  COMMIT;
  OPEN o FOR SELECT pkid,
  field1,
  field2 FROM TABLE(CAST(v_record AS vrecord)); END;
/

So I'm able to obtain a Java result set through the Sys_RefCursor. I'm not really sure that is a "multi-process access" safe procedure...

Best regards,
M. Received on Tue Feb 08 2011 - 02:29:11 CST