Re: Confusing blocking issue - More Details

You've come across an interesting anomaly here that reproduces in 9.2.0.2

If you support a primary key through an index that has extra columns, then it is not just an update to the primary key that can cause foreign key locking problems - an update to any of the extra columns in the index can ALSO cause foreign key problems.

Since you are using sysdate in your testing, it is possible that the failure to reproduce came about because you were writing scripts to do the testing, and the update was occurring in the same second as the original PK insert - so the update became a no-change update, and did not have the same effect. You could try again, but inject a 1.5 second sleep with dbms_lock.sleep()

--
Regards

Jonathan Lewis
http://www.jlcomp.demon.co.uk

Coming soon a new one-day tutorial:
Cost Based Optimisation
(see http://www.jlcomp.demon.co.uk/tutorial.html )

____UK_______March 19th
____USA_(FL)_May 2nd


Next Seminar dates:
(see http://www.jlcomp.demon.co.uk/seminar.html )

____USA_(CA, TX)_August


The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html


Tim Callaghan wrote in message ...


>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);

>