title: how to use "select FOR UPDATE" by locks in 11g scenario given below?
body: <p>I have multiple job types.</p>
  
  <pre><code>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;
  </code></pre>
  
  <p>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.</p>
  
  <p>So finally, the table should look like this.</p>
  
  <pre><code>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
  </code></pre>
  
  <p>Thread 1 processed MANAGER rows, thread 2 processed CLERK rows and thread 3 processed ANALYST rows.</p>
  
  <p>I am thinking along the lines of:</p>
  
  <p>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.</p>
  
  <p>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.</p>
  
  <p>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.</p>
  
  <p>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.</p>
  
  <p>One important criteria here is that I DO NOT want 2 threads to process 1 job type.</p>
  
  <p>Does this all make sense?</p>
  
  <p>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? </p>