Re: Confusing blocking issue - More Details

Here's a little more information on from the view dba_dml_locks:

Prior to dropping the index here's the output:

1	17	DARDEN	T_REPLICATION_HISTORY	Row-X (SX)	None
49	Not Blocking
2	19	DARDEN	T_LOCATION			Row-X (SX)
None	49	Not Blocking


After dropping/recreating the index here's the output:
1	17	DARDEN	T_REPLICATION_HISTORY	Row-X (SX)	None
49	Not Blocking
2	19	DARDEN	T_LOCATION			Row-X (SX)
None	52	Not Blocking

Identical. HELP!!!

On Wed, 29 Jan 2003 08:50:53 -0500, Tim Callaghan <timc_at_channel1.com> wrote:

>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 - 15:56:48 CET