Re: Index/Lock problem

Try fragmenting the table by expression by dividing the customers into four groups (perhaps by customer_id...if its an integer do a mod(cust_id,4)) and putting each group in a different dbspace (on its own disk, of course). You can also detach the indexes to other disks if you have enough spindles. HTH, Herb ------------------------------------------------------------------------- Herb Blacker Cimarron, Inc. Database Administrator - DOL Job Corps San Marcos, Texas ------------------------------------------------------------------------ On Tue, 25 Nov 1997, David Sherrick wrote: } } } 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 }