Help: Informix lock manager

Scott Ford writes: > > 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" > > Scott - Offhand, I don't see where the slowness (7 seconds) is coming from, but my intuition tells me it isn't from locking overhead. Have you tried monitoring Online while this is going on? In any case, the contribution I wanted to make here was to share my experiences with locking in the context of interactive user update screens. Although you mention that contention for a locked resource is not causing this immediate problem, it still is something to plan for - particularly in an application such as you describe: lots of users contending for the same pool of resources (rows). One of the most effective things you can do to minimize contention problems is to minimize the time window during which a resource is locked. One of the worst abuses of the "time window" is a user who pulls up a row (obtaining a lock) and takes his time about releasing the row (and the lock). A good strategy for eliminating this problem is as follows (stated for worst case: a single update accessing multiple rows from multiple tables): LET UPDATE_DONE = FALSE WHILE NOT UPDATE_DONE FOR EACH RECORD TO BE UPDATED RETRIEVE THE RECORD (not for update - no locks here) KEEP A COPY OF THE RECORD IN MEMORY FOR LATER DISPLAY & EDIT THE RECORD WHEN ALL EDITS ARE COMPLETE (ie. the user hits "THE BUTTON") BEGIN WORK ( IF SYMBOLIC LOCKING - ie. locking handled by the application - IS IN USE, OBTAIN THE SYMBOLIC LOCK HERE, otherwise locks will be held on each record as it is obtained for update) LET UPDATE_DONE = TRUE FOR EACH RECORD IN THE GROUP OF EDITED RECORDS RETRIEVE THE RECORD FOR UPDATE IF (RETRIEVED RECORD != KEPT ORIGINAL COPY) THEN ROLLBACK WORK NOTIFY THE USER LET UPDATE_DONE = FALSE (go back to edit loop) EXIT FOR EACH ELSE UPDATE THE RECORD AS EDITED END IF END FOR EACH COMMIT WORK END WHILE ------------ DHL WORLDWIDE EXPRESS ------------------------------------------- Greg Bryan gbryan@ssf-sys.dhl.com DHL Systems Data Administrator uunet!ssf-sys.dhl.com!gbryan San Francisco -------------------------------------------------------------------------------