Hi all,
here are the requirements that I'm messing with:
- Oracle 10g
- Table like this
CREATE TABLE TMP_TABLE
(
PKID INTEGER NOT NULL ENABLE, -- Sequence
FIELD1 VARCHAR2(45) NOT NULL ENABLE,
FIELD1 VARCHAR2(45) NOT NULL ENABLE,
STATUS CHAR(1) DEFAULT '0' NOT NULL ENABLE, -- 0 free (unset) , 1
reserved (set)
CONSTRAINT TMP_TABLE_PK PRIMARY KEY (PKID) USING INDEX ENABLE
)
;
with about hundreds of thousands records.
- Stored procedure should be Select the first available 10 records with
status=0 (no sorting required), update status=1 and returns the selected
rows into a ref cursor (Java Oracle.CURSOR) with the TMP_TABLE%ROWTYPE.
- Stored procedure should be called by concurrent processes: no more one
process must select the same 10 records (PKIDs).
I'm starting with

create or replace
PACKAGE PKGTEST IS
TYPE vCursor IS REF CURSOR RETURN TMP_TABLE%ROWTYPE;
END PKGTEST;
/

CREATE OR REPLACE
PROCEDURE pSelectTMP(
O OUT PKGTEST.VCURSOR
)
AS
PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
--
-- Something I don't know to do...
--
COMMIT;
END pSelectTMP;
/

If REF CURSOR can't be used... I could change also the Java-side client
to manage anything else (VARRAY?).

I'v tried with the UPDATE ... RETURNING clause without success...
Thanks in advance
Best regards,
M.

On Feb 7, 3:57*am, Mau C <nob... (AT) hotmail (DOT) com> wrote:

I hate to make suggestions without knowing what you are really after
but here goes:

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.

Now the application uses the keys to fetch the rows identified,
updates them, clearing the reservation information. You would need a
process that scans the table for reserved rows that were never
actually updated.

HTH -- Mark D Powell --

select 10 rows for update with the skip locked option?

Il 07/02/2011 18.59, Mark D Powell ha scritto:
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.

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.

Il 08/02/2011 9.29, Mau C ha scritto:

b) it marks "taken" those rows... (so nobody else shoud get the same
subset of rows)

On Feb 8, 12:29*am, Mau C <nob... (AT) hotmail (DOT) com> wrote:

That it isn't was my first though when I saw the pragma autonomous,
but I haven't really worked it out. I was thinking of a race
condition, but Mark's suggestion of select for update ought to avoid
that.

Here are some examples: http://asktom.oracle.com/pls/asktom/...:4530093713805

jg
--
@home.com is bogus.
""Give a man a fish, or he will destroy the only existing vial of
antidote." - xkcd.com/857

Il 08/02/2011 18.51, joel garry ha scritto:
I see that the "simple" FOR UPDATE clause is not enough.
So I've found http://tinyurl.com/69mkw3c
and SKIP LOCKED seems to be the right way to avoid problems in high
concurrency process contention.

So, my stored should be changed in

....
CURSOR c1 IS
SELECT pkid,
field1,
field2
FROM tmp_table
WHERE status=0
AND rownum <=10 FOR UPDATE OF status SKIP LOCKED;
...
FOR cur_row IN c1
LOOP
...
UPDATE tmp_table SET status=1 WHERE CURRENT OF c1;
END LOOP;
COMMIT;
...

Does anybody agree ?

Regards,
M.

On Feb 9, 4:10*am, Mau C <nob... (AT) hotmail (DOT) com> wrote:
The skip lock option which is a newer option than nowait should work
better for you than nowait would for what you want to do. Just be
sure to run an adequate test trying to get concurrent usage of the
logic before you put your code into production.

HTH -- Mark D Powell --

Mau C wrote:
I always agree with my own propositions.

On Feb 9, 10:11*am, "Gerard H. Pille" <g... (AT) skynet (DOT) be> wrote:
After thinking about this stuff too much, I have to tell myself "not
tonight, I have a headache."

jg
--
@home.com is bogus.
NOVA show about Watson tonight.