Re: Stored Procedure with concurrent SELECT / UPDATE
Date: Mon, 7 Feb 2011 09:59:55 -0800 (PST)
Message-ID: <87babec3-853e-45ad-8463-505ccd3a3d87_at_i40g2000yqh.googlegroups.com>
On Feb 7, 3:57 am, Mau C <nob..._at_hotmail.com> wrote:
> 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.
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 -- Received on Mon Feb 07 2011 - 11:59:55 CST