Re: row locking / repeatable read

Followup - closing that window of vulnerability.. Read on. Jacob Salomon wrote: > > 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. OK, I just thought of a way to narrow that window. Let the drone fetch all the rows in repeatable read mode, keeping them (and many other rows) locked while it gathers up it data. It sends the primay keys to the main program, which merely stores them in an array or a temp table. When the drone is done with all the rows for a state, it sends a message to the main program (GO FOR IT!) and rolls back its bogus transaction, releasing its lock. The main program operates under LOCK MODE WAIT so that it need not fear locking conflicts with the rows not yet unlocked by the drone. It gets the GO message, goes into repeatable read itself, and fetches all the rows whose primary keys is has been squirreling away until now. Still not perfect but with reduced margin of interference. -- -- Jake (Also not perfect, with reduced margin of belt notches) +------------------------------------------------------------+ | 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) | +------------------------------------------------------------+