Re: Index/Lock problem

Try to use one expression based table fragment for each thread. 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