Re[2]: Cursor Stability Isolation Level

I'd like to add my recent experiences with cursor stability, not regarding locks, but from a performance perspective. We have an application written in PowerBuilder 4/5 running on OnLine 7.11, HP-UX 10.01, in the U.S. (It also ran on a Sequent with OnLine 6.0. for the history of this problem.) While local performance was acceptable, performance to places like Australia, Singapore, etc. was not. We ran sniffer tests on the network and found that the packets were not being filled. The size available was 4096 bytes, but we often saw only 100 bytes (or so) in the packets. Blame was placed on INET while others suspected the application. Finally, Informix let us use their SQLIDEBUG tool which gathers much more information than a sniffer can. I was able to see that for every query that returned multiple rows of data, only one row was being sent at a time. No wonder the packets weren't filling up! But why? I saw this at the beginning of my SQLIDEBUG output: set isolation to cursor stability So I started reading up on cursors. The books mainly just talk about locks, but reading between the lines (because it said only one lock at a time) I guessed that cursor stability was the reason we were getting only one row at a time. So we changed the statement to: set isolation to committed read and now all multiple row queries are filling the packets and performance went through the roof! We realize that there were reasons that the developers put cursor stability in the code, so much analysis is now needed to determine the proper way to modify the code and still maintain integrity, etc. But the results were dramatic, a 125 row result set that took 63 seconds to return to the user went down to 24 seconds. Dianne ______________________________ Reply Separator _________________________________ Subject: Re: Cursor Stability Isolation Level Author: Stefan <stefan@weideneder.de> at INTERNET Date: 6/15/97 11:14 AM Hi Bill, thanks for this information. I checked the behaviour on Version 5.x. It's the behaviour you described below. And I agree with your words, this is a problem. It looks like isolation level "COMMITTED READ" if you use CURSOR STABILITY outside an explicit transaction. Bye Stefan PS: Additional information. I used an ESQL/C program and set the isolation level before the OPEN CURSOR statement. Bill Ennis wrote: > > Hi, > > I have a question regarding the use of an isolation level of > Cursor Stability and the use of a normal cursor. > > Reading from the Informix Guide to SQL:Tutorial version 7.2 p.7-14 > "When Cursor Stability is in effect, the database server places a lock > on the latest row fetched. It places a shared lock for an ordinary cursor > and a promotable lock for an update cursor. Only one row is locked at a time; > that is, each time the row is fetched, the lock on the previous row is released > (unless the row is updated, in which case the lock holds until the end of the > transaction). Cursor Stability ensures that a row does not change while the > program examines it." > > I have a program that creates a normal cursor (not an update cursor). When > I set an isolation level of Cursor Stability and fetch a row I do NOT > see a shared lock on the fetched row! > > Now if I fetch the row from within a transaction I do see a shared lock > on the row. In the definition given in he manual there is no mention of > a transaction being available at the time of the fetch. Was this just > an oversight? Is the behavior I'm seeing a problem? Judging from the > sentence "Cursor Stability ensures that a row does not change while the > program examines it." I would think it is a problem. > > Thanks, > Bill > > -- > Bill Ennis Voice: 312-474-7516 > SSA Fax: 312-474-7460 > 500 W. Madison email: ennis@ssax.com ennis@accesschicago.net