Unique problem

Hello Everybody, Env: 9.30 uc3 AIX 5.1 having HDR. Appl: VB 6. ODBC 2.81 We have a reservation table with UNIQUE key on resnumber, program generates some random number and checks in the table if that number exist. Program doesn't get the response back and assumes that the number doesn't exist and when it inserts into the table it fails because the number already exist in the table. I am not able to figure out, how this is taking place, have been talking to IBM and they have asked to open the trace log at ODBC level. Any ideas ? BTW. the software is outsourced we don't have any source-code in house. Thanks in advance, Sushil.... _________________________________________________________________ Free up your inbox with MSN Hotmail Extra Storage. Multiple plans available. http://join.msn.com/?pgmarket=en-us&page=hotmail/es2&ST=1/go/onm00200362ave/dire ct/01/

This could be a classic TOCTOU - time of check, time of use - problem. Unless you have your database rigged to prevent, you can have two processes, A and B, that do: A: check the non-existence B: check the non-existence of the number A: increment and insert and commit. B: increment and insert *fails* because the number it read is no longer unique. You have different options - I note you're using HDR. You can upgrade to 9.40 and use SEQUENCES instead. Or you can use a SERIAL column (insert a placeholder record - then update things appropriately). ...oh...you don't have source code. That's going to make life interesting. I guess you are left with export SQLIDEBUG=2:my_trace and then run the program. It should create a binary file my_trace_12345 for some process ID, and ship that off to IBM Tech Support. That's about all that you can do without modifying the app. Next time, remember to ask them for the explicit instructions - they may have something else in mind, but I don't know what. -- Jonathan Leffler (jleffler@us.ibm.com) STSM, Informix Database Engineering, IBM Data Management 4100 Bohannon Drive, Menlo Park, CA 94025 Tel: +1 650-926-6921 Tie-Line: 630-6921 "I don't suffer from insanity; I enjoy every minute of it!" forum.subscriber@iiug.org wrote on 04/01/2004 12:14:37 PM: > Hello Everybody, > > Env: 9.30 uc3 AIX 5.1 having HDR. Appl: VB 6. ODBC 2.81 > > We have a reservation table with UNIQUE key on resnumber, program generates > some random number and checks in the table if that number exist. Program > doesn't get the response back and assumes that the number doesn't exist and > when it inserts into the table it fails because the number already exist in > the table. > > I am not able to figure out, how this is taking place, have been talking to > IBM and they have asked to open the trace log at ODBC level. Any ideas ? > > BTW. the software is outsourced we don't have any source-code in house.

Hi Sushil, Couple of things to check -> 1) How are you checking for the row existing ? eg count(*) or slqca.sqlcode 2) When it errors with a unique constraint, how old is the row that is already there? Could be the random number is not so random and is being generated by another process just before you use it. 3) Why not change DB to use a serial ? Regards Chris

Hi Jonathan, Thanks for the response, good idea to trace the problem. It looks like we found the problem let me monitor for 3/4 days and I will update the details about it. The problem is in the apps. Thanks Sushil.... >From: Jonathan Leffler <jleffler@us.ibm.com> >To: "Sushil Shir...." <sushilps@hotmail.com> >CC: forum.subscriber@iiug.org, ids@iiug.org >Subject: Re: Unique problem [2778] >Date: Thu, 1 Apr 2004 15:22:40 -0800 >MIME-Version: 1.0 >Received: from e34.co.us.ibm.com ([32.97.110.132]) by mc12-f11.hotmail.com >with Microsoft SMTPSVC(5.0.2195.6824); Thu, 1 Apr 2004 15:22:47 -0800 >Received: from westrelay02.boulder.ibm.com (westrelay02.boulder.ibm.com >[9.17.195.11])by e34.co.us.ibm.com (8.12.10/8.12.2) with ESMTP id >i31NMi9x437616;Thu, 1 Apr 2004 18:22:45 -0500 >Received: from d03nm117.boulder.ibm.com (d03av04.boulder.ibm.com >[9.17.195.170])by westrelay02.boulder.ibm.com (8.12.10/NCO/VER6.6) with >ESMTP id i31NMgFZ382350;Thu, 1 Apr 2004 16:22:44 -0700 >X-Message-Info: JGTYoYF78jEeiRi2GoHGa2/gplY9Qufm >In-Reply-To: <200404012014.i31KEb32019302@ace.iiug.org> >X-Mailer: Lotus Notes Release 6.0.2CF1 June 9, 2003 >Message-ID: ><OF4CCA6707.8592214B-ON87256E69.007F5CA8-88256E69.00806502@us.ibm.com> >X-MIMETrack: Serialize by Router on D03NM117/03/M/IBM(Release 6.0.2CF2HF168 >| December 5, 2003) at 04/01/2004 16:22:43,Serialize complete at 04/01/2004 >16:22:43 >Return-Path: jleffler@us.ibm.com >X-OriginalArrivalTime: 01 Apr 2004 23:22:48.0191 (UTC) >FILETIME=[3EAFF8F0:01C41840] > >This could be a classic TOCTOU - time of check, time of use - problem. >Unless you have your database rigged to prevent, you can have two >processes, A and B, that do: >A: check the non-existence >B: check the non-existence of the number >A: increment and insert and commit. >B: increment and insert *fails* because the number it read is no longer >unique. > >You have different options - I note you're using HDR. You can upgrade to >9.40 and use SEQUENCES instead. Or you can use a SERIAL column (insert a >placeholder record - then update things appropriately). > >...oh...you don't have source code. That's going to make life >interesting. I guess you are left with export SQLIDEBUG=2:my_trace and >then run the program. It should create a binary file my_trace_12345 for >some process ID, and ship that off to IBM Tech Support. That's about all >that you can do without modifying the app. Next time, remember to ask >them for the explicit instructions - they may have something else in mind, >but I don't know what. > > >-- >Jonathan Leffler (jleffler@us.ibm.com) >STSM, Informix Database Engineering, IBM Data Management >4100 Bohannon Drive, Menlo Park, CA 94025 >Tel: +1 650-926-6921 Tie-Line: 630-6921 > "I don't suffer from insanity; I enjoy every minute of it!" > > > >forum.subscriber@iiug.org wrote on 04/01/2004 12:14:37 PM: > > > Hello Everybody, > > > > Env: 9.30 uc3 AIX 5.1 having HDR. Appl: VB 6. ODBC 2.81 > > > > We have a reservation table with UNIQUE key on resnumber, program >generates > > some random number and checks in the table if that number exist. Program > > > doesn't get the response back and assumes that the number doesn't exist >and > > when it inserts into the table it fails because the number already exist >in > > the table. > > > > I am not able to figure out, how this is taking place, have been talking >to > > IBM and they have asked to open the trace log at ODBC level. Any ideas >? > > > > BTW. the software is outsourced we don't have any source-code in house. > > _________________________________________________________________ Watch LIVE baseball games on your computer with MLB.TV, included with MSN Premium! http://join.msn.com/?page=features/mlb&pgmarket=en-us/go/onm00200439ave/direct/0 1/

CHRIS PRIEST wrote: >Hi Sushil, > > Couple of things to check -> > > 1) How are you checking for the row existing ? eg count(*) or slqca.sqlcode > 2) When it errors with a unique constraint, how old is the row that is already there? Could be the random number is not so random and is being generated by another process just before you use it. > 3) Why not change DB to use a serial ? > > > That might be tricky considering they have no source - their app will still generate a random number to insert into the serial field. >Regards > > Chris > > > > > >

Chris, The number is checked by count(*), and the dup number used is random not the number which is recently used. Sushil... >From: "Danny Wright " <dwright@sherwoodfoods.com> >To: ids@iiug.org >Subject: Re: Unique problem [2784] Date: Fri, 2 Apr 2004 10:13:37 -0500 >(EST) >Received: from mc6-f21.hotmail.com ([65.54.252.157]) by mc6-s21.hotmail.com >with Microsoft SMTPSVC(5.0.2195.6713); Fri, 2 Apr 2004 07:16:03 -0800 >Received: from ace.iiug.org ([216.177.38.212]) by mc6-f21.hotmail.com with >Microsoft SMTPSVC(5.0.2195.6713); Fri, 2 Apr 2004 07:15:26 -0800 >Received: from ace.iiug.org (localhost [127.0.0.1])by ace.iiug.org >(8.12.10-14/8.12.8) with ESMTP id i32FH4qb003941;Fri, 2 Apr 2004 10:17:08 >-0500 (EST) >Received: (from nobody@localhost)by ace.iiug.org (8.12.10-14/8.12.8/Submit) >id i32FDbMS003750;Fri, 2 Apr 2004 10:13:37 -0500 (EST) >X-Message-Info: jl7Vrt/mfsokfQFkfj6xvBfTxfGBIj5h >Message-Id: <200404021513.i32FDbMS003750@ace.iiug.org> >Apparently-To: forum.subscriber@iiug.org >Precedence: bulk >Return-Path: nobody@ace.iiug.org >X-OriginalArrivalTime: 02 Apr 2004 15:15:27.0055 (UTC) >FILETIME=[540675F0:01C418C5] > >CHRIS PRIEST wrote: > > >Hi Sushil, > > > > Couple of things to check -> > > > > 1) How are you checking for the row existing ? eg count(*) or >slqca.sqlcode > > 2) When it errors with a unique constraint, how old is the row that >is already there? Could be the random number is not so random and is being >generated by another process just before you use it. > > 3) Why not change DB to use a serial ? > > > > > > > >That might be tricky considering they have no source - their app will >still generate a random number to insert into the serial field. > > >Regards > > > > Chris > > > > > > > > > > > > > > _________________________________________________________________ Persistent heartburn? Check out Digestive Health & Wellness for information and advice. http://gerd.msn.com/default.asp

Thanks everybody for the input, there is nothing to update. right now working with application programmer and tech. support. Sushil... >> > > Hello Everybody, > > > > Env: 9.30 uc3 AIX 5.1 having HDR. Appl: VB 6. ODBC 2.81 > > > > We have a reservation table with UNIQUE key on resnumber, program >generates > > some random number and checks in the table if that number exist. Program > > > doesn't get the response back and assumes that the number doesn't exist >and > > when it inserts into the table it fails because the number already exist >in > > the table. > > > > I am not able to figure out, how this is taking place, have been talking >to > > IBM and they have asked to open the trace log at ODBC level. Any ideas >? > > > > BTW. the software is outsourced we don't have any source-code in house. > > _________________________________________________________________ Check out MSN PC Safety & Security to help ensure your PC is protected and safe. http://specials.msn.com/msn/security.asp