Re: Index/Lock problem

In article <347adbce.1362563@nntp.a001.sprintmail.com>, David Sherrick <davidsherrick@sprintmail.com> writes > > >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. > Yes, but a solution DOES exist for Online 7.x. I'll skip Online 5.x because it has adjacent locking which will probably stop you inserting >1 row at a time. Online 7.x has key value locking. i.e. AN INDEX KEY VALUE IS LOCKED. First do a) alter table x lock mode (row) to get row rahter than page level locking. b) add column to your index such that the set of column in any one index provides a unique key for that row. e.g. create table t1 ( a int b int c int ) create index i1 on t1(a,b); insert into t1 values (1,1,1) AT THE SAME TIME insert into t1 values (1,1,2) FAILS because in the index key value (1,1) for columns (a,b) is locked by the first insert. however create index i1 on t1 (a,b,c) the first insert locks key value (1,1,1) for columns (a,b,c) hence the insert (1,1,2) works because it locks value (1,2,1) for columns (a,b,c), A DIFFERENT LOCK!! >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 -- David Williams