Re: Broken transaction

Nigel Gall wrote: > > Hi! > > Platform: Digital UNIX 3.0b > OnLine : 5.05.UC1 > 4GL : 4.13.UD1 Pcode Version 8 > > Is there any condition, aside from an error in programming, which would > cause half (or piece) of a transaction to occur and not the other, even > when both (or all) commands are within the begin work/commit work block? > > Around the time this happens, I get the following messages in the error log > for the user: > > SQL statement error number -271. > Could not insert new row into the table. > SYSTEM error number -154. > ISAM error: Lock Timeout Expired> > According to the documentation, if the deadlock condition has exceeded the > time specified by the tbconfig parameter DEADLOCK_TIMEOUT, then it is > supposed to rollback the transaction and try again after a delay. What I'm > experiencing is that the error is registered, and pieces of my transaction > are not rolled back. ISAM -154 is NOT the DEADLOCK_TIMEOUT which only goes into effect when you have a transaction across multiple instances so that the engine cannot detect deadlocks directly and can only infer them from the long delay. That error code (-154) indicates that the time limit to wait for a lock on a row/page/key in a SET LOCK MODE TO WAIT n; statement has been waiting MORE than n seconds and has timed out. This part of the transaction therefore has not happened and it is up to the program to decide how to handle the problem. Options are: 1) Make some notification and try to perform the update again. 2) Rollback the transaction and complain to the user about others locking him out. 3) Commit the transaction and notify the user that the transaction is only partially complete. Unless the timeout you set is VERY short or someone was performing a mass update this should only happen if some poorly designed application is holding locks. An example is a screen update program that FETCHes a row FOR UPDATE, effectively locking the record so noone else can change it, displays it and waits for the user to finish modifying the on screen record(s), then completes the update and releases the lock. The problem comes in if the user of that application gets called away or becomes busy with something else. The row can remain locked for hours! This LOCK-FETCH-MODIFY-UPDATE-COMMIT cycle should be replaced with an optimistic cycle: FETCH-MODIFY-LOCK-FETCH_AGAIN-VERIFY-COMMIT_OR_ROLLBACK The LOCK-FETCH_AGAIN-VERIFY would refetch the modified row FOR UPDATE and if it has not changed (compare to the originally FETCHed unmodified data, either the entire row or an automatically maintained timestamp) UPDATE and COMMIT otherwise tell the user that someone else has changed the row and ROLLBACK releasing the lock. In this schenario locks are only held for the fraction of a second it takes to verify the newly fetched copy of the row. Art S. Kagel