Re: Informix 5 - LOCK TABLE during INSERT

Chuck England - MVP wrote: > > We are using Informix 5 on Intergraph System. We have run into a > locking problem which is causing us to exceed the number of locks for > the system. Note: The database uses its on Informix file system, and > hence Unix locks are not the culprit. Anyway, we went around the > problem. We re-wrote the code to use lock table. The code first > deletes all the records in the table, then using a select, repopulates > the table. Now that we are using LOCK TABLE in EXCLUSIVE mode the > delete now only creates a single lock. However, the INSERT still > generates bucket loads of them. Any ideas on how to keep Informix from > generating all these locks? Having the whole table locked is not a > problem. In fact, having all tables involved is not a problem. We have > run this sequence many times. Sometimes only a single lock gets > generated, other times, we get bucket fulls. Of course we are tweeking > the SQL, so there is probaly some comfortable rational for this. At > least I hope so. Anybody else had these type of locking problems? Any > ideas as to why LOCK TABLE does not keep from creating multiple locks > per row? The database is in ANSI mode. Any help would be greatly > appreciated. Perhaps the LOCK TABLE is only being applied to the DELETE. The end of a transaction releases the lock so if you have a separate transaction for the INSERT it will need its own LOCK TABLE. Ian