Search

OakieTags

Who's online

There are currently 0 users and 39 guests online.

Recent comments

Affiliations

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