Re: Simulating serial fields: locking a row with select for update statement

Mario Garc'a wrote: > > Hi, > > We need to simulate the functionality of serial fields, because for > various reasons we cannot use them. > > We have a field in a table that contains the number to be incremented - > the serial (in fact we have a row in that table for each serial to be > implemented) and we need to lock the row while the number is readed and > updated. > > We thought to do it using 'select for update' but it seems not to work > because other users can read the old value after the 'select for update' > statement and before the commit. > > Do you know why the select for update statement doesn't lock the row? > Anyone know if there is another way (or the best way) to simulate serial > fields? > > The version of informix on-line we have is 7.23.UC7 > The isolation level of the database is commited read ant the lock mode > is set to not wait. A select FOR UPDATE only takes an Intent lock on the rows it accesses and the row is not locked exclusively until the update statement is actually executed. However, it should not be possible for two users to each acquire an Intent Lock on a particular row, so there is your control. Keep in mind that this scheme single threads your updates/ inserts. It sounds like you want to SELECT the next serial at the beginning of the user session, let the user play with the data, then when he is finished, if the insert has not been canceled, update the serial number table and insert into the transaction table in a single transaction and then commit. This is not a good scheme. The best way to solve this is to NEVER select from the serial number table except select FOR UPDATE and then IMMEDIATELY update the row in a quick transaction which is IMMEDIATELY committed. Then use that serial number at your leisure. This permits a serial number to never actually be used if the transaction is canceled (there will be "holes" in the sequence of serial numbers) but that is no worse that the behavior of the built-in serial type. You can minimize this effect by delaying the serial acquisition transaction until the last possible moment, ie once you have the user's OK to insert the transaction. Art S. Kagel