I have a table that stores a pool of numbers. These numbers once used, should not be used again within 365 days. On top of that, different processes requesting for a number should be allocated different numbers. Basically a number should be used only once.
Below is the code I used to allocate 100 numbers. It's supposed to select a row, try to lock it by using "FOR UPDATE". If able to select, I assume it's available and update the LASTMODTM. If unable to select, that means another process has locked it, and I will proceed to try the next number.
I executed 2 processes running the below code together, expecting that each process will get 2 unique sets of numbers, but I end up with exactly the same list of numbers for both processes. I believe the SELECT FOR UPDATE is not working, that's why both processes are able to select the same row. Any advice for me?
DECLARE
v_nbr NUMBER_POOL.NBR%TYPE := null;
cnt INTEGER := 0;
v_nbrlist VARCHAR2(32676) := '';
BEGIN
FOR x IN (
SELECT ROWID RID
FROM NUMBER_POOL
WHERE
AND SYSDATE - LASTMODTM > 365
ORDER BY LASTMODTM, NBR
)
LOOP
BEGIN
--To lock the row so that it won't be allocated to other request
SELECT MAWB
INTO v_nbr
FROM NUMBER_POOL
WHERE ROWID = x.RID
FOR UPDATE NOWAIT;
EXCEPTION
--Unable to lock row, that means this nbr is locked by another concurrent process. Try the next nbr
WHEN OTHERS THEN
CONTINUE;
END;
UPDATE NUMBER_POOL
SET LASTMODTM = SYSTIMESTAMP
WHERE ROWID = x.RID;
cnt := cnt + 1;
v_nbrlist := v_nbrlist || ',' || v_nbr;
IF cnt = 100 THEN
DBMS_OUTPUT.PUT_LINE(SUBSTR(v_nbrlist, 2));
EXIT;
END IF;
END LOOP;
END;