Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> sequence concurrency question
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