Re: row locking / repeatable read

Andrew Simpson wrote: > A PowerBuilder program I am developing reads in data > a state at a time. I am using repeatable read to prevent > other users from accessing the state. > > The problem: the select statement always reads one > more row than necessary and locks it, thus locking the > next state in the table. > > One solution I found was to use rowid's to retrieve the > rows for the state, but this requires retrieving all the row > id's at a lesser isolation level before switching to > repeatable read and using rowid's for retrieve of rows for > state. > > Does anyone have a better solution? Drew, Selecting row-id's is frowned on by Informix purists. You could have accomplished the same result by fetching the primary key values. And yes, of course your objection remains unchanged. The problem is, of course, that as soon as you read n the next row during your first pass, the current row gets unlocked and may change before your second pass gets on it and sets isolation to repeatable read. I have an idea you are guaranteed to dislike: Using C functions, have your main program spawn off a drone program and establish some kind of IPC mechanism between your main program (message queue?) and the drone. The drone decides on a current state and goes about fetching all rows for that state. When it has fetched a row for a state not wanted, it stops fetching. So, what does the drone do with every row it fetches? It sends the primary key info to the main program via the IPC. Your main program, running under repeatable read, would immediately fetch the row with that primary key value, imposing the lock. When the drone notes a state change (presumably doing an ORDER BY state) it sends the main program a message that says "NO MORE" and simply does not send it. When your main program is all done with all the rows for that state, it sends another message to the drone requesting the next batch of rows - those from the next state. Yes, there is a short window of vulnerability between the time the drone fetches the primary key columns of a row and the time that the main program gets to locking that row. We *are* talking multi-user here! To avoid too many layers of locking problems, the drone should operate under dirty read, letting the main program worry about fetched rows being locked by other user procs (as it surely does now, the way you are currently running it). I know I could do this in 4GL. As to how to implement this under Power- Builder? Friend, that is out of my league. -- -- Jake (Under the influence of Art K. ;-) +------------------------------------------------------------+ | The expedient performance of a task with excessive concern | | regarding its duration-to-completion engenders a virtual | | certainty of diminished benefit therefrom. | | -- Benjamin Franklin (but he said it in 3 words) | +------------------------------------------------------------+