Hi,
We are maintaining unique session id in a table. once a session id pick as random we will be change the status as using mode until get response from third party service,then again simultaneously reusable.My scenario is parallely giving same session at a time even handle for update wait 2 as below
SELECT session_id
INTO v_session_id
FROM lf_session_log
WHERE (ROWID) IN (SELECT row_id
FROM (SELECT ROWID row_id
FROM lf_session_log
WHERE query_state = 'R'
AND expdttime > sysdate
AND service_type = 1
AND session_type = 'P')
WHERE rownum = 1)
FOR UPDATE wait 2;
UPDATE lf_session_log
SET query_state = 'U', expdttime = p_expdttime
WHERE session_id = v_session_id
RETURNING session_id INTO p_session_id;
commit;
Thanks,
Jai