Re: C-ISAM error -144

>From: uunet!lucas.mrec.ar!javier (Francisco Javier Lopez) >Message-Id: <9202111807.AA02898@lucas.MREC.AR> >Subject: C-ISAM error -144 >To: informix-list@rmy.emory.edu >Date: Tue, 11 Feb 92 15:07:45 ARG >X-Informix-List-Id: <list.842> > >Hi > We are in the process of converting old applications code >(informix 1.10.03F/2.10.03F & xenix 2.3.2) to on line (informix 4.00.UD2 and >sco unix 3.2). > >After having some troubles of concurrency with a large application we decided >to test them with a smaller one. > > ... > >The database originally has transactions so we modified the 4gl source to >support transactions too. > > ... > >When at least four users are trying to insert new rows at the same time >some of them get: >sqlcode: -271 (could not insert new row in the table) >c-isam error: -144 (key value locked) > >The same application in xenix works fine. Using SE, not OnLine. The locking behaviours of SE and OnLine are different. >Are we doing something wrong ? No, and Yes. This is quite a common problem, and it is also quite complex to explain. The manual notes that the correct behaviour in response to error -144 is to wait and retry. It also notes that it only occurs with Online. This implies that the correct solution is to code the application something like the (untested) code below: BEGIN WORK LET my_status = -1 WHILE my_status != 0 WHENEVER ERROR CONTINUE INSERT INTO dummy VALUES (w_record.*) LET my_status = STATUS WHENEVER ERROR STOP IF my_status != 0 THEN IF sqlca.sqlerrd[2] = -144 THEN SLEEP 1 CONTINUE WHILE END IF DISPLAY sqlca.sqlcode AT 23,1 DISPLAY sqlca.sqlerrd[2] AT 23,20 SLEEP 10 CALL err_print(sqlca.sqlcode) ROLLBACK WORK RETURN END IF LET w_rowid = SQLCA.sqlerrd[6] END WHILE SLEEP 10 COMMIT WORK RETURN The cause of the problem requires a fairly deep understanding of how OnLine handles locking, but basically, OnLine locks parts of the index as well as the data row. (You have row-level locking, which reduces contention and is therefore a good idea). Therefore, when two users try to do things, they can run into the index lock, which means that they get the -144 (key value locked) error. The key lock is necessary for OnLine to work correctly (honest, guv!). This and related problems is an area which we are looking at, but the manual does indicate what the problem is and how to solve it. The 5.00 Error Messages manual explains in more detail what the trouble is, and gives a more rigorous solution to the problem, namely rollback the transaction and retry the whole transaction. In your code, this amounts to the same as the solution I outlined -- there are no other operations to rollback, so the rollback is redundant. It also says "This error is only seen when the lock mode is set to NOT WAIT". Treat this with (and any other such statement with a pinch of salt -- this particular pinch of salt: if the lock mode is "SET LOCK MODE TO WAIT 45" (or any other number), and the timeout expires, the error returned may be -154 (deadlock timeout expired), or it could be an error like -144. You might also see -143 (deadlock detected) in a longer transaction with multiple inserts into multiple tables. This means that you must do the rollback and retry the transaction -- simply sleeping and retrying will probably only detect the deadlock again. Yours sincerely, Jonathan Leffler (johnl@obelix.informix.com)