Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> sequence concurrency question

sequence concurrency question

From: <amit.varde_at_gmail.com>
Date: 5 May 2006 09:49:22 -0700
Message-ID: <1146847762.940067.118520@g10g2000cwb.googlegroups.com>


Hi,

To reverse an account, we need to insert a duplicate set of records
(once) for a particular tran_id.
(result set below)

tran_id is being populated using a sequence number. The prnt_id col is supposed to contain a 0 for the parent record and the tran_id for the children records

Q: Is it possible that a concurrent transaction would pick up the prnt_id of another acct_no,
between the time "SELECT SEQ_TX_NO.NEXTVAL" executes and the "insert ... decode(prnt_id, 0 , 0, seqVal+1)" executes OR
Is there a better way of doing this?

We are using 10.1.0.4
****OBSERVATION:****
I fired 4 concurrent transactions and the timestamps seemed to be lined up one after the other.
Would this be true for 40 transactions at about the same time too?

****SQL CODE:****
CREATE SEQUENCE SEQ_TX_NO
    START WITH 1
    INCREMENT BY 1
    NOMINVALUE
    NOMAXVALUE
    NOCYCLE
    CACHE 2 --forcing disk read, since inserting 7 records??     NOORDER **CONCURRENT SQL**
DECLARE seqVal NUMBER;
BEGIN
SELECT SEQ_TX_NO.NEXTVAL INTO seqVal FROM dual; INSERT INTO tran_tbl
(tran_id,

 acct_no,
 prnt_id,
 LST_UPD_DT
)
SELECT
   SEQ_TX_NO.NEXTVAL,
   acct_no,
   decode(prnt_id, 0 , 0, seqVal+1) "prnt_id", /*would seqVal+1 always point to the correct SEQ_TX_NO.NEXTVAL*/

   sysdate
FROM tran_tbl
WHERE tran_id=7 OR prnt_id=7;

END; ****RESULT SET:****
tran_id acct_no prnt_id lst_upd_dt

  1    6000027      0    12/12/2006 2:00:00.000 AM
  2    6000027      1     3/08/2020 6:00:00.000 AM
  3    6000027      1     3/08/2020 6:00:00.000 AM
  4    6000027      1     3/08/2020 6:00:00.000 AM
  5    6000027      1     3/08/2020 6:00:00.000 AM
  6    6000027      1     3/08/2020 6:00:00.000 AM
 13    6000027      1     3/08/2020 6:00:00.000 AM
  7    6186763      0     3/08/2020 6:00:00.000 AM
  8    6186763      7     3/08/2020 6:00:00.000 AM
  9    6186763      7     3/08/2020 6:00:00.000 AM
 10    6186763      7     3/08/2020 6:00:00.000 AM
 11    6186763      7     3/08/2020 6:00:00.000 AM
 12    6186763      7     3/08/2020 6:00:00.000 AM
339    6000027      0     4/26/2006 6:37:20.900 PM
340    6000027    339     4/26/2006 6:37:20.900 PM
341    6000027    339     4/26/2006 6:37:20.900 PM
342    6000027    339     4/26/2006 6:37:20.900 PM
343    6000027    339     4/26/2006 6:37:20.900 PM
344    6000027    339     4/26/2006 6:37:20.900 PM
345    6000027    339     4/26/2006 6:37:20.900 PM
361    6186763      0     4/26/2006 6:37:21.478 PM
362    6186763    361     4/26/2006 6:37:21.478 PM
363    6186763    361     4/26/2006 6:37:21.478 PM
364    6186763    361     4/26/2006 6:37:21.478 PM
365    6186763    361     4/26/2006 6:37:21.478 PM
366    6186763    361     4/26/2006 6:37:21.478 PM

Thanks,
Amit Received on Fri May 05 2006 - 11:49:22 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US