Re: Confusing blocking issue - More Details

Your schema description in the first part of your message does not match the DDL at the end of your message.

"Tim Callaghan" <timc_at_channel1.com> wrote in message news:eflf3vg914v7o1etrgeda289te55p8rpsa_at_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)
>
>
> ** I realize that I should have an index on table_b.location_id_2 but
> that currently is not causing my blocking issue.
>
>
> 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).
>
> --------------------------- BEGIN SCRIPT ---------------------------
> drop table table_b;
> drop table table_a;
>
> 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;
>
>
> -- session 1
> update table_a set any_column_1 = 'ROW 2 UPDATED' where location_id =
> 2;
>
> -- session 2
> insert into table_b values (1,2,sysdate);
>
Received on Wed Jan 29 2003 - 16:51:30 CET