Path: dp-news.maxwell.syr.edu!spool.maxwell.syr.edu!news-spur1.maxwell.syr.edu!news.maxwell.syr.edu!newsfeed.media.kyoto-u.ac.jp!newsfeed.gamma.ru!Gamma.RU!newsfeed.icl.net!newsfeed.fjserv.net!newsfeed.freenet.de!border2.nntp.ams.giganews.com!border1.nntp.ams.giganews.com!nntp.giganews.com!sn-ams-06!sn-xt-ams-04!sn-post-ams-02!sn-post-sjc-01!supernews.com!corp.supernews.com!not-for-mail
From: Walt <walt_askier@SHOESyahoo.com>
Newsgroups: comp.databases.oracle.server
Subject: Re: sequence concurrency question
Date: Fri, 05 May 2006 15:54:38 -0400
Organization: Corwood Industries
Message-ID: <125nbbumukfbfd9@corp.supernews.com>
Reply-To: walt_askier@SHOESyahoo.com
User-Agent: Mozilla/5.0 (Windows; U; Windows NT 5.1; en-US; rv:1.7.2) Gecko/20040804 Netscape/7.2 (ax)
X-Accept-Language: en-us, en
MIME-Version: 1.0
References: <1146847762.940067.118520@g10g2000cwb.googlegroups.com>
In-Reply-To: <1146847762.940067.118520@g10g2000cwb.googlegroups.com>
Content-Type: text/plain; charset=us-ascii; format=flowed
Content-Transfer-Encoding: 7bit
X-Complaints-To: abuse@supernews.com
Lines: 68
Xref: dp-news.maxwell.syr.edu comp.databases.oracle.server:266977

amit.varde@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