Wednesday, April 21, 2010

Something I recently unlearned...

This is how many of the things I learn everyday come into being. They are actually things I have to "unlearn" because what used to be true has changed over time.

Once upon a time ago - I remember the day I learned this, it was during a benchmark in 1993, I learned that UNINDEXED foreign keys had some locking implications. Specifically if you:
  • update the parent primary key (which does happen, some 'frameworks' update every column even if the value did not change)
  • delete from parent
Then you should probably index the foreign key in the child table - else there will be a full table lock placed on the child table - for the duration of the transaction.

Then Oracle 9i was released and I had to relearn the rule. The rule in 9i was as above still - just modified as to the duration of the lock (many people think the restriction actually went away - but it did not, it was changed). In 9i and above, if you update the parent or delete from the parent with an unindexed foreign key - the child table is still locked - just for the duration of the update or delete! The lock is released after the statement processed - not when you commit. This was "better", but the lock still exists.

Sometime during 9i - I learned yet another modification to the rule above. The rule in 9i now has to include:
  • if you merge into the parent table
in addition to update and delete. As I was getting ready to add that to the 2nd Edition of Expert Oracle Database Architecture - I learned something new, the rule has changed again. The MERGE doesn't always lock the table anymore in 11g Release 1 and above - so we are back to just update and delete (sort of!).

Here is the small test case you can use to verify - the set up is:
ops$tkyte%ORA9IR2> create table p ( x int primary key, y int );
Table created.

ops$tkyte%ORA9IR2> insert into p values ( 1, null );
1 row created.

ops$tkyte%ORA9IR2> insert into p values ( 2, null );
1 row created.

ops$tkyte%ORA9IR2> create table c ( x references p );
Table created.

ops$tkyte%ORA9IR2> create or replace
procedure modify_p( p_what in varchar2 )
2 as
3 pragma autonomous_transaction;
4 deadlock exception;
5 pragma exception_init( deadlock, -60 );
6 begin
7 if ( p_what = 'DELETE' ) then delete from p where x = 2;
8 elsif ( p_what = 'UPDATE' ) then update p set x = 2 where x = 2;
9 elsif ( p_what = 'MERGE' ) then
10 merge into p using (select 2 x, 42 y from dual) d
11 on (p.x=d.x)
12 when matched then update set y = d.y
13 when not matched then insert(x,y) values (d.x,d.y);
14 end if;
15 rollback;
16 dbms_output.put_line( p_what || ': successful...' );
17 exception
18 when deadlock then
19 dbms_output.put_line( p_what ||
': we deadlocked, we needed full table lock');
20 rollback;
21 end;
22 /
Procedure created.
So, a parent table with two rows - 1 and 2. An empty child table with an unindexed foreign key. A stored procedure that runs as an autonomous transaction - so it cannot share the locks of the parent transaction, if the parent transaction has anything locked - the autonomous_transaction will NOT be able to also lock it. The autonomous transaction attempts to either
  • delete row X=2 from parent
  • update row X=2 in parent
  • merge into row x=2 in parent using when matched then update, when not matched then insert
and if it deadlocks - prints out a message telling us that and rolls back. If successful, prints out a message telling us that and likewise rolls back.

To test, we just insert into the child table a record that points to row x=1 in parent (we'll never touch that row in the parent table) and then try the three DML opertions:
ops$tkyte%ORA9IR2> insert into c values ( 1 );
1 row created.

ops$tkyte%ORA9IR2> exec modify_p( 'DELETE' );
DELETE: we deadlocked, we needed full table lock
PL/SQL procedure successfully completed.

ops$tkyte%ORA9IR2> rollback;
Rollback complete.

ops$tkyte%ORA9IR2>
ops$tkyte%ORA9IR2> insert into c values ( 1 );
1 row created.

ops$tkyte%ORA9IR2> exec modify_p( 'UPDATE' );
UPDATE: we deadlocked, we needed full table lock
PL/SQL procedure successfully completed.

ops$tkyte%ORA9IR2> rollback;
Rollback complete.

ops$tkyte%ORA9IR2> insert into c values ( 1 );
1 row created.

ops$tkyte%ORA9IR2> exec modify_p( 'MERGE' );
MERGE: we deadlocked, we needed full table lock
PL/SQL procedure successfully completed.

ops$tkyte%ORA9IR2> rollback;
Rollback complete.

There you go, you can see it deadlocked on all three - they all needed to lock the child table before doing their work.

If you run that in 10gr1 and 10gr2 - you'll see the same results - all three lock. However, starting in 11g Release 1 - you'll see this:
ops$tkyte%ORA11GR1> insert into c values ( 1 );
1 row created.

ops$tkyte%ORA11GR1> exec modify_p( 'DELETE' );
DELETE: we deadlocked, we needed full table lock
PL/SQL procedure successfully completed.

ops$tkyte%ORA11GR1> rollback;
Rollback complete.

ops$tkyte%ORA11GR1> insert into c values ( 1 );
1 row created.

ops$tkyte%ORA11GR1> exec modify_p( 'UPDATE' );
UPDATE: we deadlocked, we needed full table lock
PL/SQL procedure successfully completed.

ops$tkyte%ORA11GR1> rollback;
Rollback complete.

ops$tkyte%ORA11GR1> insert into c values ( 1 );
1 row created.

ops$tkyte%ORA11GR1> exec modify_p( 'MERGE' );
MERGE: successful...
PL/SQL procedure successfully completed.

ops$tkyte%ORA11GR1> rollback;
Rollback complete.


That merge no longer locks the child table. Curious as to this change - I wanted to see if it was official or not and found bug 5970280 - from which I learned that it was officially changed and that some of you running 10.2 might see a different result for this test case (the fix was backported and is available for 10.2).

The fix is more complex than appears (aren't they always?) It is not just "turn off lock for MERGE", it is "analyze the merge and
  • if the merge just inserts - treat as an insert
  • if merge does an update or update and insert (and we are NOT updating the primary key!) treat as an update to non-primary key columns
  • if merge does an update or update and insert (and we are updating the primary key) lock child table
  • if merge includes a delete - treat as a delete and lock child table

So, if your merge was:

10 merge into p using (select 2 x, 42 y from dual) d
11 on (p.y=d.x)
12 when matched then update set x = d.x
13 when not matched then insert(x,y) values (d.x,d.y);


then you would see:

ops$tkyte%ORA11GR1> exec modify_p( 'MERGE' );
MERGE: we deadlocked, we needed full table lock
PL/SQL procedure successfully completed.


or if your merge included a possible delete branch, you would see the same.

So, I guess the rule in 11gR1 and above is, if you
  • update parent primary key
  • delete from parent
  • use a merge that does either of the above
and you have an unindexed foreign key - you should expect a full table lock on the child table. If you index that foreign key - no untoward locking will take place.

Things change over time :)
POST A COMMENT

9 Comments:

Blogger Flado said....

Jonathan Lewis found some worrying details in this regard - you might want to look at his "Lock Horror" post:
http://jonathanlewis.wordpress.com/2010/02/15/lock-horror/

Wed Apr 21, 11:45:00 AM EDT  

Blogger Thomas Kyte said....

@Flado

I don't see them as related - one is a bug (jonathans), this is an enhancement

they are not really related at all. Other than both have the words "foreign key" in them :)

Wed Apr 21, 11:54:00 AM EDT  

Blogger Wayne said....

Tom , I just wish I could fix that stupid Forms Default "Update Changed Columns Only" that is "N".
Which basically means the Form will updates all columns (regardless of which actually changed), including the PK.

And ever so often, this will lead to a Child Table Lock, for the FK's that are UNINDEXED.

I know you covered this in your book and we have Finally done a Global change on all our Forms.
Halleluya - No more Table Locks!

But every once in a while, some form will still slip through the cracks.

Anyway, I suppose for Backwards Compatibility - that's not going to change ever, so we just have to stay vigilant.

Thanks again for this "Something I recently learnt/unlearnt/relearnt" theme.

Stimulating and challenging us all to go back and recheck, rethink, retest and redesign.

Thu Apr 22, 03:24:00 PM EDT  

Anonymous Raul said....

Hi Tom,

I am hoping you can provide some insight.

I have an interesting test case:

Oracle 10.2.0.3

Session 1:

SQL> create table p ( x int primary key, y int );

Table created.

SQL> create table c ( x references p, y int );

Table created.

SQL> create index cx on c(x);

Index created.

SQL> insert into p values (1,1);

1 row created.


Session 2:

SQL> alter table c add ( z int );
alter table c add ( z int )
*
ERROR at line 1:
ORA-00054: resource busy and acquire with NOWAIT specified

SQL> alter table c drop column y ;

Table altered.


With an uncommitted insert on the parent table, I am trying to add a column on the FK indexed child table. Is the ORA-00054 an expected feature or a bug? then I try do drop a column on the child table and that works fine??
How is it an add column does not go through but the drop column worked?


Thank you in advance

Sun Apr 25, 02:02:00 AM EDT  

Blogger Thomas Kyte said....

@Raul

it is designed to work that way - the referential integrity constraint ties those two tables together - it'll happen for other operations as well.

Reason #53214321 why doing DDL in a live system is a no-no (although - in 11g with edition based redefinition, DDL that can wait, more online operations - this is becoming less true)

Sun Apr 25, 08:23:00 AM EDT  

Blogger Timur Akhmadeev said....

Tom,

this change in behavior is a result of bug 5970280 and it is included in 10.2.0.5.

Wed May 05, 02:47:00 PM EDT  

Blogger Thomas Kyte said....

@Timur

yes, I mentioned that in the original post - thanks...

Wed May 05, 04:59:00 PM EDT  

Blogger surya said....

Hi TOM,

How index on foreign key avoids table lock

Thanks and regards
Surya

Fri May 25, 01:12:00 AM EDT  

Blogger Thomas Kyte said....

@Surya

see
http://docs.oracle.com/cd/E11882_01/server.112/e25789/consist.htm#BABCAHDJ

Fri May 25, 04:20:00 AM EDT  

POST A COMMENT

<< Home