Mystique: Locks, Constraints and Indexes

From: Pauli Salmu <p.salmu_at_mn.medstroms.se>
Date: 8 Mar 1995 19:40:03 GMT
Message-ID: <3jl16j$qig_at_zorn.mnet.medstroms.se>

We were getting strange deadlocks in our production DB and finally happened to discover that they had something to do with constraints. Then we got some help from the local Oracle representatives who said that you probably must index every column that has a foreign key constraint. Seems crazy, but we tried and here's a simple test. DB version is 7.0.15.5.1 in Netware.

CREATE TABLE parent (
    parent_id NUMBER(1),
    data      NUMBER(1),

    CONSTRAINT pk_parent PRIMARY KEY(parent_id) );

CREATE TABLE child (

    child_id NUMBER(1),
    parent_id NUMBER(1)

        CONSTRAINT fk_child_to_parent REFERENCES parent(parent_id),
    data      NUMBER(1),

    CONSTRAINT pk_child PRIMARY KEY(child_id) );

INSERT INTO parent VALUES (1, 0);
INSERT INTO parent VALUES (2, 0);
COMMIT; User 1:
update parent set data=1 where parent_id=1;

User 2:
insert into child values (1,2,0);
- waits until user 1 commits. Why?

Then we created an index:
create index child_1 on child (parent_id);

and repeated the test, user 2 wrote
insert into child values (2,2,0);
- no waiting

Just to be sure we dropped the index and repeated, user2: insert into child values (3,2,0);
- waits again

I can't find anything about this in any manual. Am I dyslexic? Can anyone give a logical explanation and/or reasonable solution ("get Sybase...")? Received on Wed Mar 08 1995 - 20:40:03 CET