Index/Lock problem
Posted in 1997
Topics: Performance & Tuning, Server Administration, Triggers, Constraints & Referential Integrity, Transactions, Locking & Isolation
I am having a problem with locks when an index exists on a table. It appears that when a new row is inserted into a table that has an index defined, the index page that was modifed is exclusively locked. This prevents other processes from insert data into the same table until the first process has committed or rolledback. By the way, this is regardless of the isolation level set ( I tested this using dirty read). I have talked with Informix technical support and they have confirmed this. What I am actually trying to accomplish is to break up a large batch job into pieces and have different threads on a mutli-processor machine handle the work. So for example, I have a set of queries, updates and inserts to run on 10,000 customers. What I was trying to do was break that up so that 4 threads could handle 2500 customers each to improve performance. However, because all four process are inserting into the same tables, no real performance gain is realized because of the threads having to wait for locks to be released on the index pages. The only solution I have come up with is to remove the indexes before starting the batch job. However, this is not ideal because some of the indexes are there because of primary/foreign key constraints. Does anyone have a better/different solution to this problem? Is there a different approach I should be taking to speed up the batch processing to take advantage of the multi-processor system? Thanks, David Sherrick