Skip to Main Content
  • Questions
  • 10gR2 block with update table set row=.... eand Foreign Ky on from the table to the table

Breadcrumb

May 4th

Question and Answer

Tom Kyte

Thanks for the question, Mathias.

Asked: February 09, 2007 - 9:27 am UTC

Last updated: May 11, 2007 - 3:44 pm UTC

Version: 10.2.0

Viewed 1000+ times

You Asked

Hi,

today I encountered a strange behaviour.
Details:

Session 1 : select null from test where ID = 1 for update nowait;
Session 2 : select null from test where ID = 2 for update nowait;
Session 1 : declare R test%rowtype;
begin
select * into R from test where ID = 1;
update test set test.col1=R.col1 where ID = 1;
end;
/

==> no block but surprisingly
------------------------------------
Session 1 : select null from test where ID = 1 for update nowait;
Session 2 : select null from test where ID = 2 for update nowait;
Session 1 : declare R test%rowtype;
begin
select * into R from test where ID = 1;
update test set row=R where ID = 1;
end;
/
==> Deadlock
If Session 2 : rollback;
then Session 1 : PlsqlProcedure successfully Completed

Any ideas?

Thanks in advance

Mathias
-------------------------------------------------------
folowwing sktipt gives this Block, the when I disable the Foreign Key there is no block but with the foreign key i have a block.


drop table t;
create table t ( id number primary key, y number,z number );
ALTER TABLE t ADD (
CONSTRAINT FK1
FOREIGN KEY (z)
REFERENCES t (id));
insert into t values ( 1, 1 ,null);
insert into t values ( 2, 2 ,null);
commit;

select null from t where id = 1 for update nowait;

prompt in another session run:
prompt select null from t where id = 2 for update nowait;
pause

declare
r t%rowtype;
begin
select * into r from t where id = 1;
update t set row = R where id = 1;
end;
/



Any ideas?

Thanks in advance

Mathias & Luc



and Tom said...

Oh, if you update a parent tables primary key OR you delete from the parent table - you get a full table lock on any child table that has an unindexed foreign key.

In 9i and above, this lock is for the duration of the DML.
In 8i and before, this lock is for the duration of the transaction.


so, this is just classic "unindexed foreign key"

The last update blocks because it tries to update the primary key - must get full table lock to do that (Z is not indexed).

http://asktom.oracle.com/tkyte/unindex/unindex.sql

Rating

  (5 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

But what if the primary key is not changed?

Mike, May 11, 2007 - 12:27 pm UTC

Yes, it is possible for UPDATE ... SET ROW = to change the primary key. But if the primary key is not changed, does it have the same performance impact as if it did change?

I am particularly concerned about the foreign-key validation that is implied by changing the primary key.

We have been encouraging our developers to use this syntax, as it produces much more maintainable code. We may have chosen not to index the child tables because 'the parent will never be updated or deleted' - but if this is the behavior then we will have to be much more careful.
Tom Kyte
May 11, 2007 - 1:40 pm UTC

you are updating the primary key there. you are updating every column.

ops$tkyte%ORA10GR2> create table p ( x int primary key, y int );

Table created.

ops$tkyte%ORA10GR2> create table c ( x references p, y int );

Table created.

ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> insert into p values ( 1, 1 );

1 row created.

ops$tkyte%ORA10GR2> insert into p values ( 2, 1 );

1 row created.

ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> insert into c values ( 1, 1 );

1 row created.

ops$tkyte%ORA10GR2> commit;

Commit complete.

ops$tkyte%ORA10GR2> insert into c values ( 1, 1 );

1 row created.

ops$tkyte%ORA10GR2> declare
  2          pragma autonomous_transaction;
  3          l_rec p%rowtype;
  4  begin
  5          select * into l_rec from p where x = 2;
  6          update p set row = l_rec where x = 2;
  7          commit;
  8  end;
  9  /
declare
*
ERROR at line 1:
ORA-00060: deadlock detected while waiting for resource
ORA-06512: at line 6



you will want to index those foreign keys to prevent the full table lock on the child.

Why deadlock ?

Jay, May 11, 2007 - 2:34 pm UTC

Tom - In your example above, how come a deadlock occurs ?
Tom Kyte
May 11, 2007 - 3:44 pm UTC

because I used a single session via the autonomous transaction to demonstrate there is a lock on the table.

if it did not deadlock, it would just sit there forever.

Horrid syntax

Jonathan Lewis, May 11, 2007 - 3:37 pm UTC

Mike,

Personally I think this 'set row' syntax is one of the worst features available in pl/sql. Apart from the problem of forcing the database to have redundant indexes to handle locking problems, it also means that every little change to a row now turns into a whole row update with the extra undo and redo now being generated for EVERY SINGLE COLUMN. Tell your developers to forget they ever heard about this feature.

UPDATE SET ROW= syntax

Mike, May 11, 2007 - 4:13 pm UTC

I disagree about the syntax being an abomination. Perhaps the implementation is a problem. Could Oracle be smarter about detecting which columns actually changed, and avoid expensive processing that will not really accomplish anything? Maybe in 12g . . . :-)

The INSERT and UPDATE using rowtypes have made great improvements in productivity and maintainability for us. Coding an insert statement that names 100+ columns, and provides the necessary values in the correct order, is an exercise we can now avoid with that syntax. I just attended a code review today that removed hundreds of lines of code by using this technique.

(5* because my question got answered - not because I got the answer I wanted to hear - thank you very much for such a valuable service.)

SET ROW with no PK update...

A reader, May 14, 2007 - 7:30 am UTC

I agree about the limitations of the SET ROW "feature" but we can work around it using in-line views and updating the projection only. See following from 10g (WARNING: this will dump you out with ORA-07445/ORA-03113 on 9i).

Whether we choose to use this workaround is another matter entirely, however...

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

Table created.

SQL>
SQL> insert into t values (10,10,10);

1 row created.

SQL>
SQL> declare
  2
  3     type update_record is record ( y int, z int );
  4
  5     v_update update_record;
  6     v_key    t.x%type;
  7
  8  begin
  9
 10     --Dummy data...
 11     v_key := 10;
 12     v_update.y := 20;
 13     v_update.z := 30;
 14
 15     --Set row without PK...
 16     update (select y,z from t where x = v_key)
 17     set row = v_update;
 18
 19  end;
 20  /

PL/SQL procedure successfully completed.

SQL>
SQL> select * from t;

         X          Y          Z
---------- ---------- ----------
        10         20         30



In bulk...

SQL> insert into t select rownum, rownum, rownum from user_tables where rownum <= 5;

5 rows created.

SQL>
SQL> declare
  2
  3     type update_record is record ( y int, z int );
  4     type update_array is table of update_record index by pls_integer;
  5     type key_array is table of t.x%type index by pls_integer;
  6
  7     v_updates update_array;
  8     v_keys    key_array;
  9
 10  begin
 11
 12     --Dummy data...
 13     for r in (select * from t) loop
 14        v_keys(v_keys.count+1) := r.x;
 15        v_updates(v_updates.count+1).y := r.y+10;
 16        v_updates(v_updates.count).z := r.z+20;
 17     end loop;
 18
 19     --Set row without PK...
 20     forall i in 1 .. v_keys.count
 21        update (select y,z from t where x = v_keys(i))
 22        set row = v_updates(i);
 23
 24  end;
 25  /

PL/SQL procedure successfully completed.

SQL>
SQL> select * from t;

         X          Y          Z
---------- ---------- ----------
        10         30         50
         1         11         21
         2         12         22
         3         13         23
         4         14         24
         5         15         25

6 rows selected.


I've never been sold on this feature though.

More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library