Re: Index/Lock problem

What lock level are the tables set at? If at page level(this is the default) you may ne able to avoid the problem by changing the tables to row level locking. -Bill } } } } 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 } -- Bill Ennis Voice: 312-474-7516 SSA Fax: 312-474-7460 500 W. Madison email: ennis@ssax.com ennis@accesschicago.net