Un-indexed foreign keys had some locking implication,
specifically if you:
- Update the parent table primary key (which does happen, when some ‘framework’ update every column even if the value does not change)
- Delete from parent.
Sometime during 9i – yet another modification to above rule.
The rule in 9i has to include:
- If you merge into parent table (only the update part)
Sometime during 10g – yet another modification to above
rule. The rule in 10g has to include
- If you merge into parent table (including both update and delete part)
rajesh@ORA10G> set feedback off
rajesh@ORA10G>
rajesh@ORA10G> create table p(x int primary key, y int);
rajesh@ORA10G> create table c(x references p);
rajesh@ORA10G> insert into p values(1,null);
rajesh@ORA10G> insert into p values(2,null);
rajesh@ORA10G> set feedback on
rajesh@ORA10G>
rajesh@ORA10G>
rajesh@ORA10G> create or replace procedure modify_data(p_what
varchar2)
2 as
3 deadlock exception;
4 pragma exception_init(deadlock,-60);
5 pragma autonomous_transaction;
6 begin
7 if p_what ='DELETE1' then
8 delete from p where x = 2;
9 elsif p_what='UPDATE' then
10 update p set x = 2 where x = 2;
11 elsif p_what = 'MERGE' then
12 merge into p using (select 2 x from
dual) t
13 on (p.x = t.x)
14 when matched then
15 update set p.y = t.x
16 when not matched then
17 insert values(t.x,null) ;
18 elsif p_what='DELETE2' then
19 merge into p using (select 2 x from
dual) t
20 on (p.x = t.x)
21 when matched then
22 update set p.y = t.x where p.y = 55
23 delete where p.x = t.x
24 when not matched then
25 insert values(t.x,null) ;
26 end if;
27 rollback;
28 dbms_output.put_line(p_what||':
Sucessfull..');
29
30 exception
31 when deadlock then
32 dbms_output.put_line(p_what||':we deadlocked,
need full table lock..');
33 end;
34 /
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. 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 point to row
x=1 in parent (we'll never touch that row in the parent table) and then try the
four DML operations:
rajesh@ORA10G> insert into c
values(1);
1 row created.
rajesh@ORA10G> exec
modify_data('DELETE1');
DELETE1:we deadlocked, need full table lock..
PL/SQL procedure successfully completed.
rajesh@ORA10G> rollback;
Rollback complete.
rajesh@ORA10G>
rajesh@ORA10G> insert into c
values(1);
1 row created.
rajesh@ORA10G> exec
modify_data('DELETE2');
DELETE2:we deadlocked, need full table lock..
PL/SQL procedure successfully completed.
rajesh@ORA10G> rollback;
Rollback complete.
rajesh@ORA10G>
rajesh@ORA10G> insert into c
values(1);
1 row created.
rajesh@ORA10G> exec
modify_data('UPDATE');
UPDATE:we deadlocked, need full table lock..
PL/SQL procedure successfully completed.
rajesh@ORA10G> rollback;
Rollback complete.
rajesh@ORA10G>
rajesh@ORA10G> insert into c
values(1);
1 row created.
rajesh@ORA10G> exec
modify_data('MERGE');
MERGE:we deadlocked, need full table lock..
PL/SQL procedure successfully completed.
rajesh@ORA10G> rollback;
Rollback complete.
rajesh@ORA10G>
rajesh@ORA10G>
There you go, you can see it deadlocked on all three - they all needed
to lock the child table before doing their work. However starting with Oracle
11g – you’ll see this
rajesh@ORA11G>
rajesh@ORA11G> insert into c
values(1);
1 row created.
rajesh@ORA11G> exec
modify_data('DELETE1');
DELETE1:we deadlocked, need full table lock..
PL/SQL procedure successfully completed.
rajesh@ORA11G> rollback;
Rollback complete.
rajesh@ORA11G>
rajesh@ORA11G> insert into c
values(1);
1 row created.
rajesh@ORA11G> exec
modify_data('DELETE2');
DELETE2:we deadlocked, need full table lock..
PL/SQL procedure successfully completed.
rajesh@ORA11G> rollback;
Rollback complete.
rajesh@ORA11G>
rajesh@ORA11G> insert into c
values(1);
1 row created.
rajesh@ORA11G> exec
modify_data('UPDATE');
UPDATE:we deadlocked, need full table lock..
PL/SQL procedure successfully completed.
rajesh@ORA11G> rollback;
Rollback complete.
rajesh@ORA11G>
rajesh@ORA11G> insert into c
values(1);
1 row created.
rajesh@ORA11G> exec
modify_data('MERGE');
MERGE: Sucessfull..
PL/SQL procedure successfully completed.
rajesh@ORA11G> rollback;
Rollback complete.
rajesh@ORA11G>
rajesh@ORA11G>
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 the merge was,
11 elsif p_what = 'MERGE' then
12 merge into p using (select 2 x from
dual) t
13 on (p.y =
t.x)
14 when matched then
15 update set
p.x = t.x
16 when not matched then
17 insert values(t.x,null) ;
Then you would see
rajesh@ORA11G> insert into c values(1);
1 row created.
rajesh@ORA11G> exec modify_data('MERGE');
MERGE:we deadlocked, need full table lock..
PL/SQL procedure successfully completed.
rajesh@ORA11G> rollback;
Rollback complete.
rajesh@ORA11G>
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
No comments:
Post a Comment