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!newshub.sdsu.edu!postnews.google.com!g10g2000cwb.googlegroups.com!not-for-mail From: amit.varde@gmail.com Newsgroups: comp.databases.oracle.server Subject: sequence concurrency question Date: 5 May 2006 09:49:22 -0700 Organization: http://groups.google.com Lines: 85 Message-ID: <1146847762.940067.118520@g10g2000cwb.googlegroups.com> NNTP-Posting-Host: 198.204.133.208 Mime-Version: 1.0 Content-Type: text/plain; charset="iso-8859-1" X-Trace: posting.google.com 1146847771 31811 127.0.0.1 (5 May 2006 16:49:31 GMT) X-Complaints-To: groups-abuse@google.com NNTP-Posting-Date: Fri, 5 May 2006 16:49:31 +0000 (UTC) User-Agent: G2/0.2 X-HTTP-UserAgent: Mozilla/5.0 (Windows; U; Windows NT 5.0; en-US; rv:1.7.5) Gecko/20060111 Netscape/8.1,gzip(gfe),gzip(gfe) Complaints-To: groups-abuse@google.com Injection-Info: g10g2000cwb.googlegroups.com; posting-host=198.204.133.208; posting-account=-awtjw0AAACxyys903-wtGXt3uJp3lAa Xref: dp-news.maxwell.syr.edu comp.databases.oracle.server:266968 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