Re: Confusing blocking issue - More Details

I've looked it over twice and don't see what you mean. Where do you see the two being different?

-Tim

On Wed, 29 Jan 2003 15:51:30 GMT, "Brian E Dick" <bdick_at_cox.net> wrote:

>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 - 09:55:48 CST