Re: Multiple Update Problem

Jonathan Leffler wrote: > > Victor M. Castillo wrote: > > Hi I am new to the Informix user group and I thought maybe some one > > out there can help me. I am having a problem with updating using > > Informix 4GL. The problem goes like this, whenever a user holds a > > record for update, say record 100, nobody can update records with a > > record number greater than 100, however records with a record number > > less than 100 can be updated. > > This is probably a problem with the table design (OK, index design), > and perhaps a function of how the UPDATE statement is written. > > Are you using SE, perchance, as your database engine? > > Anyway, if you are updating records without using an index, then your > changes can cause the table to appear locked to other users. Usually, > if you are using an index, then this is not a problem. > > Also, if either UPDATE is scanning the table, then your locks are > going to cause problems for the process which scans the table. Also if the table has page level locking updating a row will lock users out of other rows on the same page. In addition, you do not state what engine version you are accessing. Earlier versions locked the keys before and after the row being updated in each index which can lock out several seemingly unrelated rows including any rows with the same key in any indexes that allow duplicate keys. You can reduce your exposure to these concurrency issues in interactive programs by FETCHING the row(s) to be updated without the FOR UPDATE clause and after acquiring change information from the user, refetch the original row, FOR UPDATE, to lock it and verify that it has not been modified in the interim by another user. Then update the row(s) if verified and release the locks quickly. In each application SET LOCK MODE TO WAIT 5 so that these instantaneous locks do not cause any updates to fail. The verification can be made against all columns or just against a timestamp column maintained by an update trigger and can be very fast indeed. Art S. Kagel