Avoiding duplicates in multi-user environments
Avoiding concurrency issues in stored procedures can be tricky. I'm writing some functionality to add a document ID to a table in Oracle. The requirement is as follows:The document ID must consist of a string identifying the entity a string identifying the month and a string identifying the year and an increment.
[entity]_[mm]_[yyyy]_[nnn] which might look like 9999_01_2017_1
At first glance this looks like it should be quite easy. query the data by entity, month, year. count and add one. Cast the numeric values to a varchar and Concatenate. This, however doesn't take into account the fact that two concurrent inserts might not see a unique count. In this case you'll end up with the, possibility disastrous, duplicate. This is the scenario that DBMS_LOCK was designed to resolve.
For me the solution was to employ the example here
This very good article clearly shows how to lock a portion of pl/sql while an update is carried out and then release the lock such that the process remains isolated and gets an unimpeded view of the data it's updating and can remain confident that the process will function without concurrency issues.
v_doc_id varchar2(20);
v_call_status INTEGER;
v_lock_handle varchar2(128);
BEGIN
-- Calculate the document id
DBMS_LOCK.allocate_unique( 'SET_APPROVAL_DOCUMENT_ID', v_lock_handle );
v_call_status := DBMS_LOCK.REQUEST(v_lock_handle, DBMS_LOCK.X_MODE, 5, TRUE);
IF v_call_status = 0 THEN
Select ( select to_char(je.legal_entity_cd)||'_'||je.financial_period
from me_journal_entry je
where je.CFME_ID = p_cfme_id) ||'_'||
( select to_char(count(*)+1)
from V_ME_JOURNAL_APPROVAL_LOG al,
(select je.legal_entity_cd, je.financial_period
from me_journal_entry je
where je.CFME_ID = p_cfme_id) j
where al.is_approved = 'Y'
and j.legal_entity_cd = al.legal_entity_cd
and j.financial_period = al.financial_period)
into v_doc_id
from dual;
-- Insert the approval row
Insert into G79_CFM.ME_APPROVAL_LOG (IS_APPROVED, CFME_ID, APPROVAL_NOTES,USER_ID, APPROVAL_TYPE, CFME_DOCUMENT_ID, APPROVAL_DATE_TIME)
Values (p_is_approved, p_cfme_id, p_approval_notes, p_user_id, p_approval_type, v_doc_id, sysdate());
COMMIT;
ELSE
