Friendly lock mode

Hi all! The question is this: when some transaction locks a row (as for an update) other tasks can't modify that row until the modifying transaction has been closed with a commit or rollback statement. So, any transaction coming after that update and before the transaction owing the update has benne closed must (or may) wait for the first transaction complection. Informix does this using the SET LOCK MODE TO WAIT, optionally using a timeout in secs. The matter is that a program, and the operator behind it, does not know it is waiting for a lock due to a transaction completion. So I resolved that situation using a SET LOCK MODE TO NOT WAIT and trapping the SQL and ISAM error showing a wait lock, popping a dialog to the operator and letting him choose: leave his transaction o retry the locked operation. For instance: DECLARE cursor for update OPEN cursor FETCH cursor <------------------------------- Retry---| locked: what do I do now ? abort --> close - rollback - exit The problem is that with IDS rel 9.21 when the first transaction (the lockinig) finishes, a retry by the second transaction results in a SQLNOTFOUND ( 100 ) error! Why ? Is something changed in the lock mechanism ? How can I trap locks without using SET LOCK MODE TO WAIT ? Thanks in advance Peter -------------------------------------------------------- Peter Komanns E-Mail: p.komanns@sindata.it hiroshima 45; chernobyl 86; windows 95 !!!!!!

Peter Komanns wrote: > > Hi all! > The question is this: when some transaction locks a row (as for an update) > other tasks can't modify that row until the modifying transaction has been > closed with a commit or rollback statement. > So, any transaction coming after that update and before the transaction > owing the update has benne closed must (or may) wait for the first > transaction complection. > Informix does this using the SET LOCK MODE TO WAIT, optionally using a > timeout in secs. > The matter is that a program, and the operator behind it, does not know it > is waiting for a lock due to a transaction completion. > So I resolved that situation using a SET LOCK MODE TO NOT WAIT and trapping > the SQL and ISAM error showing a wait lock, popping a dialog to the operator > and letting him choose: leave his transaction o retry the locked operation. > For instance: > DECLARE cursor for update > > OPEN cursor > > FETCH cursor <------------------------------- > Retry---| > locked: what do I do now ? > abort --> close - > rollback - exit > > The problem is that with IDS rel 9.21 when the first transaction (the > lockinig) finishes, a retry by the second transaction results in a > SQLNOTFOUND ( 100 ) error! > > Why ? Is something changed in the lock mechanism ? > How can I trap locks without using SET LOCK MODE TO WAIT ? Are you closing and reopening the CURSOR? You have to do so to reinitialize the query, just fetching again, even though the previous fetch encountered a lock condition, will not refetch the offending row but try to fetch the NEXT row in the existing cursor. If there were only one, or that last one was indeed the last in the result set, then SQLNOTFOUND is the correct response to another FETCH. The real problem, I believe, is that your applications design is holding locks too long. Think about recoding to something like: 1) SELECT (nolock) 2) user interaction, saving an unmodified copy of the row, on user save then 3) RE-SELECT same row 4) compare new version to original unmodified row (using a timestamp or the data columns). If unchanged then update Else notify the user someone else has updated the row while he worked and ask for instructions options ( a) overwrite - update anyway, goto 5 b) re-edit new version - go to 1) c) merge diffs - create a netted row assuming no overlapping mods then update, goto 5 ) 5) Update row by key. This will limit locking to the fraction of a second when the record is finally updated with whatever version and any WAIT MODE delay is instantaneous. Art S. Kagel