Help: Informix lock manager

From sford Sat Oct 19 02:19:14 1991 From: sford@oregon.wvus.org (Scott F. Ford) X-Mailer: SCO System V Mail (version 3.2) To: sford@oregon Subject: Help: Informix lock manager Date: Fri, 18 Oct 91 19:19:11 PDT Message-ID: <9110190219.aa13725@oregon.wvus.org> Hey anybody -- this is my first time writing out here so please be gentle. (I've been reading awhile, building up my confidence...). Anyway, we are running on 4.0UH tools via online engine on a Unisys 6000/70. We are having response time issues with a new application that seem to be pointing towards the lock manager. Any thoughts you may have would be greatly appreciated, so here goes -- The application is a phone center with about 60 people. The code involved is compiled 4GL. At some point, each operator will access a table (via the program) by declaring a cursor using some criteria. The trick is to find just one row that a single process should make "reserved" while other processes find other "available" rows for themselves. Of course, many rows could meet the standard criteria and all processes will be running at essentially the same time. Here is the basic order of events in the *current* method of coding: prepare select (primary key in select clause) begin work declare cursor1 open cursor1 fetch declare cursor2 for update (now getting all columns req'd for update) open cursor2 fetch update & set where current of (if row still "available") close cursor2 commit work We have tried this in various forms, including switching it around just a bit so that "cursor1" includes the "for update" "with hold" and the second is no longer a cursor but just an update. Of course, the program has its intricacies and all that with varying types of criteria and what-not, this is the simplest case. In testing it, however, what is most interesting to us is that a single user running a single process takes 7 seconds. Without any locks it takes 1 second. This is what shot the theory that the *real* problem was process contention and not the overhead to acquire any lock in general. As stated at the outset, any and all responses are appreciated. Scott Ford, DBA | sford@wvus.org | World Vision USA/ISD | elroy.jpl.nasa.gov!wvus!sford ___|___ 919 W. Huntington Dr. | Voice: 818/357-1111 x3333 | Monrovia, CA 91016 | FAX: 818/303-6212 | | "but bugs may appear eventually, potentially, perhaps even exponentially"