Re: Help - Select returns Infinite # Rows

stevenp@haven.ios.com (Paul Stevens) writes: >I am running Informix Online Server 5.01.UD2 on a Pentium PC (EISA) under >SCO Unix 3.2.4. >I am having a problem executing a SELECT while another >long transaction is running. The SELECT returns an infinite number of rows >when it should return just a few. The scenario is as follows: > . > . > . It sounds like you're running into a problem with Bug #32390: SELECTS FROM A JOIN CAN LOOP RETURNING SAME ROW WHEN IT RUNS INTO A LOCK. My company just wasted over a month with Informix Technical Support trying to identify and resolve this very problem. What happens is that under some circumstances, the SELECT statement running into a lock does not abort with an SQL error, but instead loops, returning the same row over and over until the lock is released. It caused our applications to generate over five million dollars in billing errors and resulted in two weeks downtime for our production system, since my managers where unwilling to continue processing until this problem was resolved. Especially, since sometimes the rows that were repeated shouldn't even have appeared in the output in the first place. Contact Informix Technical Support and request information regarding Bug #32390. The information they emailed me included a replication script (much simpler than that in the previous post), and a suggested workaround of setting the application's isolation level to CURSOR STABILITY. This workaround was not acceptable to my management, since it would allow our reports to lock our database updates out of the tables. We upgraded to 5.04UC1, instead, and that seems to have fixed the problem. This is a very nasty problem. I would prefer that the application went down in flames, rather than quietly returning the wrong answer, over and over again. Although our platform is a little different, running HP/9000's, HP-UX and Online versions 5.02 and 5.03, it sounds exactly like the same problem. I would even go as far as suggesting someone from Informix post the replication script here, so that everyone with an Online version < 5.04 could check for themselves if they have the problem.