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;