Informix 5 - LOCK TABLE during INSERT
Posted in 1997
Topics: Transactions, Locking & Isolation
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. Chuck England CEngland@MindSpring.Com