Locking Question

Hi Informixers, we have a legacy application that generates unique keys according to the following schema: YYLLNNNN where YY is two-digit year, LL is location id and NNNN is sequential number for combination YYLL. The programmer implemented this function by doing "select max(key) from table where year = 'YY' and location = 'LL'" and incrementing the result by 1. No table or row locking is in use for this key generation, but the application is running with "set lock mode to wait 5". Not pretty, but it works as long as no two users are entering data for the same year and location concurrently. There is no way this key generation schema can be changed in this legacy application. Now I have to import data into the database from another source, so there will be concurrency issues to address. The data will be read from a source table, processed and then inserted into the database after a key has been generated. There is one master and two detail tables, and after successfully inserting the data with the key generated, the row is to be deleted from the source table. I need to keep the lock on the master table as short as possible. What is the best way to achieve this? My present concept in pseudo code is: declare cursor on source table with hold open cursor while not eoc fetch cursor begin work (process data from cursor) select max(key) from master table (increment key) insert into master table insert into detail table 1 insert into detail table 2 delete row from source table commit work end while Is that concept feasible? I'd appreciate any suggestions on how to implement this in a better fashion. Regards, Richard

Richard Spitz wrote: > Hi Informixers, > I need to keep the lock on the master table as short as possible. What is > the best way to achieve this? My present concept in pseudo code is: > > declare cursor on source table with hold > open cursor > while not eoc > fetch cursor > begin work > (process data from cursor) > select max(key) from master table > (increment key) > insert into master table > insert into detail table 1 > insert into detail table 2 > delete row from source table > commit work > end while> > Is that concept feasible? I'd appreciate any suggestions on how to implement > this in a better fashion. > > Regards, Richard > > I don't see how this will lock the master table... This allows for the legacy application to insert a registry equal to your process... I'd say you should: 1- create an unique index on the column if it doesn't have it already 2- do a "SELECT ... FOR UPDATE" so that you locked the registry with the max(value). 3- check the isolation level of your process and of the legacy application. Regards.