Error NO. 243 and 144

Informix Dynamic Server 7.31UD2R1 on HP-UX 11.0 There are two programs with same syntax tries to run almost at the same time and sometimes end up in getting this error. Any ideas or solutions to avoid this will be highly appreciated. Both are using Committed Read Isolation level and lock mode set to not wait. Here is the program select count(*) from table test1if count(*) > 0 then update test1 else insert into test1.. It gets the exception 144. __________________________________ Do you Yahoo!? Yahoo! Calendar - Free online calendar with sync to Outlook(TM). http://calendar.yahoo.com

Haven't seen anyone else take a shot at this. . . so . . . A 243 error gives a general description of the issue; the 144 error explains it in greater detail. Here's the 'finderr' output . . . -144 ISAM error: key value locked. The current operation inserts a row with a certain primary key value or updates a row with a certain primary key value, but a transaction that has not yet been committed has deleted that key value from the index. This error occurs only when the lock mode is set to NOT WAIT. Treat it the same as error -107 (record is locked). Roll back the current transaction, and re-execute it after a delay. Then, if the other transaction was committed, the lock no longer exists. If it was rolled back, the key exists, and this operation receives a duplicate-key error. "SET LOCK MODE TO WAIT <N>" will allow the first program to complete while making the second program wait until the transaction is completed. Of course, this cause other issues within the program as well. -----Original Message----- From: Vineet Mehr.... [mailto:vin_us@yahoo.com] Sent: Tuesday, June 10, 2003 10:49 AM To: ids@iiug.org Subject: Error NO. 243 and 144 [1314] Informix Dynamic Server 7.31UD2R1 on HP-UX 11.0 There are two programs with same syntax tries to run almost at the same time and sometimes end up in getting this error. Any ideas or solutions to avoid this will be highly appreciated. Both are using Committed Read Isolation level and lock mode set to not wait. Here is the program select count(*) from table test1if count(*) > 0 then update test1 else insert into test1. It gets the exception 144. __________________________________ Do you Yahoo!? Yahoo! Calendar - Free online calendar with sync to Outlook(TM). http://calendar.yahoo.com "CONFIDENTIALITY NOTICE: This message originates from WHSmith USA Travel Retail. This email message and all attachments may contain legally privileged and confidential information intended solely for the use of the addressee. If you are not the intended recipient, you should immediately stop reading this message and delete it from the system. Any unauthorized reading, distribution, copying, or other use of this message or its attachments is strictly prohibited. All personal messages express solely the sender's views and not those of WHSmith USA Travel Retail. This message may not be copied or distributed without this disclaimer."

Well, how do you avoid this kind of conflict? I've had the same experience as Vineet mentioned in my company applications. There are many applications that used the 'set isolation...'. And some of these applications have a transation log. Tah Davis -----Original Message----- From: John Carlson [mailto:John_Carlson@whsmithusa.com] Sent: Tuesday, June 10, 2003 4:04 PM To: ids@iiug.org Subject: RE: Error NO. 243 and 144 [1320] Haven't seen anyone else take a shot at this. . . so . . . A 243 error gives a general description of the issue; the 144 error explains it in greater detail. Here's the 'finderr' output . . . -144 ISAM error: key value locked. The current operation inserts a row with a certain primary key value or updates a row with a certain primary key value, but a transaction that has not yet been committed has deleted that key value from the index. This error occurs only when the lock mode is set to NOT WAIT. Treat it the same as error -107 (record is locked). Roll back the current transaction, and re-execute it after a delay. Then, if the other transaction was committed, the lock no longer exists. If it was rolled back, the key exists, and this operation receives a duplicate-key error. "SET LOCK MODE TO WAIT <N>" will allow the first program to complete while making the second program wait until the transaction is completed. Of course, this cause other issues within the program as well. -----Original Message----- From: Vineet Mehr.... [mailto:vin_us@yahoo.com] Sent: Tuesday, June 10, 2003 10:49 AM To: ids@iiug.org Subject: Error NO. 243 and 144 [1314] Informix Dynamic Server 7.31UD2R1 on HP-UX 11.0 There are two programs with same syntax tries to run almost at the same time and sometimes end up in getting this error. Any ideas or solutions to avoid this will be highly appreciated. Both are using Committed Read Isolation level and lock mode set to not wait. Here is the program select count(*) from table test1if count(*) > 0 then update test1 else insert into test1

The application is doing something wrong. It could be processing and locking tables in a different order or scanning a large number of rows in order to update 1 row. If the latter, put on an appropriate index to avoid this behaviour. This assumes that the correct update stats is run regularly on the database. MW > -----Original Message----- > From: forum.subscriber@iiug.org [mailto:forum.subscriber@iiug.org]On > Behalf Of tah davis > Sent: Wednesday, 11 June 2003 9:24 a.m. > To: ids@iiug.org > Subject: RE: Error NO. 243 and 144 [1321] > > > Well, how do you avoid this kind of conflict? I've had the > same experience > as Vineet mentioned in my company applications. There are > many applications > that used the 'set isolation...'. And some of these > applications have a > transation log. > Tah Davis > > -----Original Message----- > From: John Carlson [mailto:John_Carlson@whsmithusa.com] > Sent: Tuesday, June 10, 2003 4:04 PM > To: ids@iiug.org > Subject: RE: Error NO. 243 and 144 [1320] > > > Haven't seen anyone else take a shot at this. . . so . . . > > A 243 error gives a general description of the issue; the 144 > error explains > it in greater detail. Here's the 'finderr' output . . . > > -144 ISAM error: key value locked. > > > The current operation inserts a row with a certain primary > key value or > updates a row with a certain primary key value, but a > transaction that > has not yet been committed has deleted that key value from > the index. > This error occurs only when the lock mode is set to NOT WAIT. > Treat it > the same as error -107 (record is locked). Roll back the > current > transaction, and re-execute it after a delay. Then, if the > other > transaction was committed, the lock no longer exists. If it > was rolled > back, the key exists, and this operation receives a > duplicate-key > error. > > "SET LOCK MODE TO WAIT <N>" will allow the first program to > complete while > making the second program wait until the transaction is completed. Of > course, this cause other issues within the program as well. > > > -----Original Message----- > From: Vineet Mehr.... [mailto:vin_us@yahoo.com] > Sent: Tuesday, June 10, 2003 10:49 AM > To: ids@iiug.org > Subject: Error NO. 243 and 144 [1314] > > > Informix Dynamic Server 7.31UD2R1 > on HP-UX 11.0 > > There are two programs with same syntax tries to run > almost at the same time and sometimes end up in > getting this error. > > Any ideas or solutions to avoid this will be highly appreciated. > > Both are using Committed Read Isolation level and lock > mode set to not wait. > > Here is the program > > select count(*) from table test1> if count(*) > 0 then > update test1 > else > insert into test1>