Thank you for your response.
I am sorry if I didn't explain the problem properly. Although here is what I am looking for:
e.g.
-- I have multiple job types.
CREATE TABLE EMP (EMPNO NUMBER(10) PRIMARY KEY, JOB_TYPE VARCHAR2(20), PROCESSED VARCHAR2(3), THREAD NUMBER(4));
INSERT INTO EMP VALUES (7125, 'MANAGER', 'NO', NULL);
INSERT INTO EMP VALUES (7250, 'MANAGER', 'NO', NULL);
INSERT INTO EMP VALUES (7400, 'MANAGER', 'NO', NULL);
INSERT INTO EMP VALUES (7445, 'CLERK', 'NO', NULL);
INSERT INTO EMP VALUES (7550, 'CLERK', 'NO', NULL);
INSERT INTO EMP VALUES (7600, 'CLERK', 'NO', NULL);
INSERT INTO EMP VALUES (7945, 'CLERK', 'NO', NULL);
INSERT INTO EMP VALUES (7965, 'ANALYST', 'NO', NULL);
INSERT INTO EMP VALUES (7970, 'ANALYST', 'NO', NULL);
INSERT INTO EMP VALUES (7975, 'ANALYST', 'NO', NULL);
INSERT INTO EMP VALUES (7980, 'ANALYST', 'NO', NULL);
INSERT INTO EMP VALUES (7985, 'ANALYST', 'NO', NULL);
INSERT INTO EMP VALUES (7990, 'ANALYST', 'NO', NULL);
COMMIT;
Now I want to spawn multiple parallel threads that will process each job type without waiting for each other. Once the row is processed, the thread will mark it processed (Update PROCESSED to YES and THREAD to thread number) and move on to the next row.
So finally, the table should look like this.
EMPNO JOB_TYPE PROCESSED THREAD
7125 MANAGER YES 1
7250 MANAGER YES 1
7400 MANAGER YES 1
7445 CLERK YES 2
7550 CLERK YES 2
7600 CLERK YES 2
7945 CLERK YES 2
7965 ANALYST YES 3
7970 ANALYST YES 3
7975 ANALYST YES 3
7980 ANALYST YES 3
7985 ANALYST YES 3
7990 ANALYST YES 3
Thread 1 processed MANAGER rows, thread 2 processed CLERK rows and thread 3 processed ANALYST rows.
I am thinking along the lines of:
Thread 1 comes in, BULK FETCHes all the MANAGER rows, locks them and processes them.
Thread 2 comes in, tries to BULK FETCH all the MANAGER rows, finds that it is locked, so skips to next JOB_TYPE. BULK FETCHes CLERK rows, locks them and processes them.
Thread 3 comes in, sees that MANAGER and CLERK is locked, so moves onto ANALYST rows.
Also note that each thread will process rows that are marked NO. So after one thread is complete with its processing and released the locks, the other thread should not re-process the same rows because they are now marked PROCESSED=YES.
In a production like environment, I am going to have hundreds of job types and thousands of rows per job type. No. of parallel threads will be controlled externally by a DBA depending upon amount of resources used in the database at any given time.
One caveat I found about FOR UPDATE SKIP LOCKED is that the locks are obtained when you actually fetch the rows (if you are using a cursor in a pl/sql) as opposed to when you open the cursor.
One important criteria here is that I DO NOT want 2 threads to process 1 job type.
Does this all make sense?
My comment about not using AQ is that I need additional privileges to set up AQ in the database whereas SKIP LOCKED seems like it might just be able to accomplish this. Are you saying that SKIP LOCKED cannot be used to replace AQ in 11g?
Thank you.