2

I'm currently working on a project that requires I use a database as a queue. For performance reasons I need to be be able launch multiple services so it can scale sideways. Initially I was using a table in mysql which made this process very easy using a query similar to the sample below.

SELECT * FROM work_orders WHERE state = 'available' LIMIT 1 FOR UPDATE

Then in my Java code I perform an update to the state column changing it from available to queued. This prevents any other pending select for updates from acting on it since state changes from available to queued.

However the scope has changed and now this needs to run as a stored procedure in Oracle 11.2. I've found that implementing the above in Oracle is a bit more complex. I based my solution on the example from this Stack Exchange question.

How to solve ORA-02014: cannot select FOR UPDATE from view with DISTINCT, GROUP BY

Running several simple java test services show each one getting a unique item of work as expected. My concern with my solution though is that I'm not completely confident in the select for update locking the row. As you can see in my code below the inner most select has no lock as the lock occurs at the initial select. My question is how is this handling this behind the scenes?

CREATE OR REPLACE PROCEDURE getOneAtomicWorkOrder(
    calling_service_name   IN LOCKING_TEST.process%TYPE,
    out_job_id  OUT LOCKING_TEST.JOB_ID%TYPE,
    out_application OUT LOCKING_TEST.APPLICATION%TYPE,
    out_old_state OUT LOCKING_TEST.STATE%TYPE)

IS

BEGIN
    SELECT
      job_id, application, state
      INTO out_job_id, out_application, out_old_state
    FROM
      LOCKING_TEST WHERE job_id IN
      (
              SELECT job_id FROM ( SELECT * FROM LOCKING_TEST WHERE state = 'available' ) WHERE ROWNUM <=1
      )
    FOR UPDATE;

    UPDATE LOCKING_TEST SET state = 'queued', process = calling_service_name WHERE job_id=out_job_id;

    COMMIT;

    EXCEPTION
      WHEN no_data_found
        THEN
          out_job_id := 0;
          out_application := NULL;
          out_old_state := NULL;
          ROLLBACK;
END;

EDIT: This new sql script based on Shannon's input is much cleaner.

    CREATE OR REPLACE PROCEDURE getOneAtomicWorkOrder(
        calling_service_name   IN LOCKING_TEST.process%TYPE,
        out_job_id  OUT LOCKING_TEST.JOB_ID%TYPE,
        out_application OUT LOCKING_TEST.APPLICATION%TYPE,
        out_old_state OUT LOCKING_TEST.STATE%TYPE)

IS
BEGIN

  SELECT job_id,
         application,
         state
         INTO out_job_id,
              out_application,
              out_old_state
              FROM LOCKING_TEST
                WHERE state = 'available' and rownum = 1  ORDER BY job_id for update;

  UPDATE LOCKING_TEST SET state = 'queued', process = calling_service_name WHERE job_id=out_job_id;

  COMMIT;

  EXCEPTION
    WHEN no_data_found
      THEN
        out_job_id := 0;
        out_application := NULL;
        out_old_state := NULL;
        ROLLBACK;
END;
Community
  • 1
  • 1
DRM
  • 21
  • 1
  • 4
  • Why not, ` SELECT job_id, application, state INTO out_job_id, out_application, out_old_state FROM LOCKING_TEST WHERE state = 'available' and rownum <= 1 for update;`? If you have enough processes trying to read from the queue, google the `with nolock` hint. Also, you don't have any ordering, so which row is picked is arbitrary. Not really a queue (first in first out). – Shannon Severance Apr 05 '16 at 20:37
  • Thank you Shannon that appears to work just fine. I'll also read up on the nolock feature. – DRM Apr 06 '16 at 15:39

0 Answers0