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

On Mon, 31 Aug 1998 14:21:10 +0200, "Mario Garc'a" <mgarcia@vlc.centrisa.es> 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. I gave a suggestion as late as yesterday in another post on how to do such things: What we do is to create a separate table with nothing but a serial column. We do an insert in that and optain the serial value from that insert in the regular way (sqlca.sqlerrd[x] x beeing 1 for ESQL/C and 2 for 4GL or using dbinfo in a select statement). Then we use this value to insert in whatever table that needs a modified serial value (in our case simply a check digit, but it could be anything of course). You can delete all rows from this extra table at any time to keep it from growing. The next serial value is maintained outside the table data as you asume, so there is no need for even a single row to exist in the table between inserts. >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. May be the isolation level of the spesific select statements these users are executing is dirty read? >Do you know why the select for update statement doesn't lock the row? I am quite sure it does but that doesn't help against "dirty readers". >Anyone know if there is another way (or the best way) to simulate serial >fields? See above. >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. In each program? Nils Myklebust NM Data AS Norway E-mail: Nils.Myklebust@nmdata.com FAQ at: http://www.iiug.org/techinfo/faq/faq_top.html (Now with ODBC info under "Third party products".)