Re: record locks and timeouts

Mike Cabaniss wrote: > > Two questions on IDS: Good clear newbee questions. > 1. when doing an update a lock placed on the individual record or table > level? Depending on the table's lock mode, row or page (default page), a lock is taken on the data row or the page on which it lives and on the index nodes that need to be updated with the new key values. You can force a lock at the table or database level yourself with a separate command: LOCK TABLE mytable IN EXCLUSIVE MODE; LOCK DATABASE mydatabase IN EXCLUSIVE MODE; > 2. if a user leaves a transaction open, is there or can there be a time > out feature that cancels the transaction ( does not commit it)? Not really at the engine level. It will eventually become classified, by the engine, as a Long Transaction and be forced to rollback but depending on the number and size of the logfiles that could take hours or weeks. You should never use SELECT .... FOR UPDATE for interactive programs simply because of the problem you are intimating about. Your user could tie up the row and go out to lunch or off to Tahiti on a honeymoon. It is best to use a timestamp scheme, where every interactively updated table contains a timestamp column that is inserted by a default constraint and updated by an update trigger. Then you FETCH the row with no lock and let the user update the screen form. When the form is committed refetch the row, or at least the timestamp column, FOR UPDATE and if the timestamp has changed then another user has updated the row. Then you release the lock you are holding and you need to notify the user and do something intelligent (redisplay the new version of the row, show both versions for comparison by the user, follow a fixed merge algorithm to merge the separate changes, whatever). If the timestamp is not changed you then UPDATE...WHERE CURRENT OF cursor and commit instantly so you never hold a lock for more than a fraction of a second and every app that is lock sensitive can use SET LOCK MODE TO WAIT 5 to wait for such quick locks to clear. Art S. Kagel