RE: unique indexes
FYI . . .. it 'works' the same in 7.30.uc7
John Carlson
Informix Database Administrator
EDS - WHSmith USA
3200 Windy Hill Road, Suite 1500 West
Atlanta, GA 30330
-----Original Message-----
From: Madison Pruet [mailto:mpruet@home.com]
Sent: Tuesday, January 23, 2001 9:42 PM
To: informix-list@iiug.org
Subject: Re: unique indexes
>
>
> But what about locking? Even with row key locking, locks are applied
> to indices. Remember Online v7 uses 'key value' locking. i.e. it locks
> the whole list!. This means no other rows on the same list can have
> their indices updated. i.e. all rows with the same index key are also
> locked! Imagine every update locking several hundred rows per index!
> ^^^^^^^^^
Huh???. I just ran the following test on one window
create database cmpdb with log;
create table tab1 (
col1 int,
col2 char(20)) lock mode row;
create index idx1 on tab1 (col1);
begin work;
insert into tab1 values (1, "test1");
insert into tab1 values (1, "test1a")
And then while I was still in transaction from another window I did...
insert into tab1 values (1,"test1b")
Had no problems. The open transaction still had the key locks and the
second
window transaction completed without any problems. This is on 9.3 pre-beta.
Based on your argument, I would have expected the second window to have
locked
on the open transaction that I had in the first window. Can you get me a
test
example of what you are seeing?
>
> Of course each index will probably cause a different group of rows
> to be locked. So updating one row with thirty indexes could lock
> say 30x2,000 = 60,000 rows for each row updated!
>
> This is much worse then the row vs page level locking question!
>
> Try to explain to the user which 'other rows' get locked when
> updating one row when several composite indexes are involved!
> And of course you need to updating this information for users
> whenever you change the indexes on a table
> e.g. adding new columns which are then indexed!
>
> --
> David Williams