Re: PB app, locking in Informix 5

Lana wrote: > > Hi, > I am developing a multiuser PowerBuilder 4.0.03 application with Informix > OnLine 5 as the back end database. > > In the application, one user will need to exclusively read/update a set of > rows in several tables at a time. Other users should be able to > read/update the other rows at the same time. I am wondering if anyone > knows how to lock a set of rows in several tables at a time. I tried ALTER > TABLE tablename LOCK MODE (ROW) but still unsuccessful. > > I am also wondering if 'repeatable read' will lock the whole table. > > Answers or pointers will be very much appreciated. Thank you. Lana, I begin with a disclaimer: I know zilch about PowerBuilder. The easiest way to keep several rows of a table locked for a duration is, of course, to begin a new transaction, and set your isolation level to repeatable read. Every row you read, from any table, will be locked (shared lock) until the transaction ends. However, this has the side effect that every row the engine examines in the course of the search will also remain locked for the duration of the transaction. (This prevents a row you already rejected from becoming acceptible to the query. It's a separate can of worms I don't want to open until I get to the lake.) So if your query skips an index, this will impose alotta locks. The engine is clever enough (in some cases) to see this coming and will lock the entire table. So your concern is justified. In many cases, all users access the table via the same or similar applications. If this your situation, you can use the following kluge: Special treatment for the first row in the active set - the first row you fetched. In most queries, this contains data from several tables but who cares? For that first row, issue a fetch via a "for update" cursor. This will place an update lock on that first row [of each table in the query]. By proposition, all users access the tables basically the same way. Thus, that first fetched row will serve as a roadblock protecting the other rows in that query's active set. This will not even block someone else's read, because everyone can remain in "committed read" mode. If it happens to be in the way of somone else's update, well, at least I have a reason to have that roadblock up there. And, most important, far fewer rows will be "locked" with this method than with repeatable read. I used quotes because the non-first rows of each user's active set will not really get locked (unless modified); only the cooperative behavior among the user applications will protect your active sets. Of course, if a rogue application runs, it may very well ignore the conventions and blow your active set to a shallow grave in the cornfield. So how good are your coding controls? Good luck! -- -- Jake Salomon +-----------------------------------------------------------+ | Diplomacy: The art of getting something off your | | chest without losing your shirt | +------------------------Alfred E. Neuman-------------------+ _..-'( )`-.._ ./'. '||\\\\. (\\_/) .//||` .`\\. ./'.|'.'||||\\\\|.. )o o( ..|//||||`.`|.`\\. ./'..|'.|| |||||\\`````` '`"'` ''''''/||||| ||.`|..`\\. ./'.||'.|||| ||||||||||||. .|||||||||||| ||||.`||.`\\. /'|||'.|||||| ||||||||||||{ }|||||||||||| ||||||.`|||`\\ '.|||'.||||||| ||||||||||||{ }|||||||||||| |||||||.`|||.` '.||| ||||||||| |/' ``\\||`` ''||/'' `\\| ||||||||| |||.` |/' \\./' `\\./ \\!|\\ /|!/ \\./' `\\./ `\\| V V V }' `\\ /' `{ V V V ` ` ` V ' ' ' (from:http://www.info.polymtl.ca/ada2/tranf/www/ascii/animal/bats.html)