Hi,

We are using the “Select FOR UPDATE” in DB procedure for updating a sequence number which needs to be unique per transaction. This DB procedure is called from the Java based web application under the JOTM transaction management.

But, in some conditions, which we are not sure as of now, when there are some DB performance issues, then there is duplicate sequence number generated for different transaction.


Duplicate Transaction sample:

Transaction No Transaction Submission Date / Time

SC0812TEC00647 (6/8/2012) / (15:17)
SC0812TEC00647 (6/8/2012) / (14:48)

SC1312TEC02936 (6/8/2012) / (14:19)
SC1312TEC02936 (6/8/2012) / (14:13)

SC0112GEC12619 (6/8/2012) / (13:51)
SC0112GEC12619 (6/8/2012) / (13:51)

SC2112GEC01693 (6/8/2012) / (14:25)
SC2112GEC01693 (6/8/2012) / (14:13

SC1012TAP00233 (6/8/2012) / (15:07)
SC1012TAP00233 (6/8/2012) / (14:43)

The problem is coming when same sequence number is returned for two different workflow transactions.
Below is the DB procedure that is responsible to retrieve the sequence number:

PROCEDURE Dp_Generateprocessno (pv_comp_code IN VARCHAR2, pv_process IN VARCHAR2, lv_process_no OUT
VARCHAR2)
IS

lv_year VARCHAR2(2);
lv_rowid VARCHAR2(50);
lv_new_no nexus_seqno.nexus_srno%TYPE;
lv_process nexus_process_mast.ALT_PROCESS_CODE%TYPE;

BEGIN
SELECT NVL(ALT_PROCESS_CODE, pv_process), TO_CHAR(SYSDATE,'yy')
INTO lv_process, lv_year FROM nexus_process_mast
WHERE comp_code = pv_comp_code AND process_code = pv_process;

-- Fetch the record from NEXUS_SEQ_NUM For Process
SELECT SUBSTR(LTRIM(TO_CHAR(TO_NUMBER(nexus_srno)+1,'0000 000000')), -1*nexus_srno_width)
INTO lv_new_no
FROM nexus_seqno
WHERE comp_code = pv_comp_code AND nexus_tran = pv_process AND nexus_yr = lv_year
FOR UPDATE;

lv_process_no := pv_comp_code || lv_year || lv_process || lv_new_no;

-- update lap_no in DB start
UPDATE nexus_seqno SET NEXUS_SRNO = lv_new_no WHERE comp_code = pv_comp_code AND nexus_tran =
pv_process AND nexus_yr = lv_year;

-- commit;

EXCEPTION

WHEN OTHERS THEN
NULL;

END Dp_Generateprocessno;

----------------------------------------------------

I have received below suugestion too, but need to know via some example or reference as to how to resolve the duplicacy issue:

The select for update is not a good locking strategy because there are many things that can go wrong.

Instead of select for update, savvy Oracle developers will adopt alternatives mechanisms like:

1 - On initial read, save the row contents in RAM.

2 - Re-read upon update commit time. If the rows has not changed since the initial read, update the row.

3 - If row has changed re-read again, process and update the row

http://www.dba-oracle.com/t_select_for_update.htm

------------------------------------------------------

Regards,
Kapil Raut