Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Confusing blocking issue - More Details

Confusing blocking issue - More Details

From: Tim Callaghan <timc_at_channel1.com>
Date: Wed, 29 Jan 2003 08:50:53 -0500
Message-ID: <eflf3vg914v7o1etrgeda289te55p8rpsa@4ax.com>


I posted a question yesterday and have put in 4 more hours of research. My situation is now as follows:

table_a
  location_id
  any_column_1
  last_touch_date

  PK = location_id
  Unique Index on location_id + last_touch_date

table_b
  location_id_1
  location_id_2
  last_touch_date

  PK = location_id_1 + location_id_2
  Index on location_id_1 + last_touch_date   FK location_id_1 to table_a (location_id)   FK location_id_2 to table_a (location_id)

Here's my problem:

SQL Session 1:
update table_a set last_touch_date = sysdate where location_id = 1;
<1 row updated>

SQL Session 2:
insert into table_b values (1,1,sysdate);
<blocked by SQL Session 1>

If I remove the Unique Index on table_a the second session is not blocked.

NOW IT GETS STRANGE. If I drop and recreate the index on table_a (all these tests are being performed on an existing schema that I imported, my results are the same against my production instances) I no longer have the blocking.

I've checked everything I possibly can about the structure of the index before and after my drop/create and it's exactly the same. I've even done a rebuild to see if that makes a difference and it did not.

Any information you can provide would be extremely helpful. I'm at my wits end on this one. Even pointing me in the direction of some locking views or similar to do more research would be appreciated.

Tim Callaghan

In case you want more information I've produced the following script which (should have) duplicated my results. Unfortunately the sessions do not block (just like after I drop and recreate my unique index).

create table table_a

 (location_id      NUMBER(12,0) NOT NULL,
  any_column_1     VARCHAR2(40) NOT NULL,
  last_touch_date DATE DEFAULT SYSDATE NOT NULL,   constraint pk_table_a PRIMARY KEY (location_id));   

create unique index idx_table_a on table_a (location_id asc, last_touch_date asc);

create table table_b
  (location_id_1 NUMBER(12,0) NOT NULL,    location_id_2 NUMBER(12,0) NOT NULL,    last_touch_date DATE DEFAULT SYSDATE NOT NULL,    constraint pk_table_b PRIMARY KEY (location_id_1, location_id_2),    constraint fk_b_a_1 FOREIGN KEY (location_id_1) references table_a (location_id),

   constraint fk_b_a_2 FOREIGN KEY (location_id_2) references table_a (location_id));    

create index idx_table_b on table_b (location_id_1, last_touch_date);

insert into table_a values (1,'ROW 1',sysdate); insert into table_a values (2,'ROW 2',sysdate);

insert into table_b values (1,1,sysdate); insert into table_b values (2,1,sysdate); commit;

Received on Wed Jan 29 2003 - 07:50:53 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US