Re: sequence concurrency question

amit.varde_at_gmail.com wrote:
> 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;
Maybe I'm not seeing this correctly, but it appears that you are selecting from a sequence, then incrementing the value by one and hoping that the incremented value doesn't conflict with anything.

My response is: are you feeling lucky? This is the worst kind of bug, because it will probably work reliably enough to make it through development, testing, & QA only to fail intermittantly in production, and some poor slob will spend weeks trying to figure out why.

If you need a sequence value, select it from the sequence, do *not* increment a previous value.

//Walt

//Walt Received on Fri May 05 2006 - 14:54:38 CDT