Home » SQL & PL/SQL » SQL & PL/SQL » Locking and foreign key indexes (9.2.0.8)
Locking and foreign key indexes [message #594665] Sun, 01 September 2013 04:44 Go to next message
Amine
Messages: 264
Registered: March 2010
Senior Member

Hi all,
Say we have an employee(id_emp) table with a primary key on id_emp.
We have also some history tables emp_stuff with columns say (id_emp, dat_event, some_stuff) with primary key id_emp, dat_event.
This means that we have a unique index on (id_emp,dat_event).
We also have a foreign key id_emp that references employee(id_emp).

When we update id_emp on employee, we still have a lock on emp_stuff.
According to this (end of the page) :

Quote:
So, in short, with releases prior to Oracle Database 11g Release 1, you will want an index on the foreign key of the child table if you do any of the following:

Update the parent table primary key
Delete from the parent table
Merge into the parent table


So is id_emp in emp_stuff considered as indexed (through the unique index of the primary key) or do we have to add an explicit index
like this
CREATE INDEX emp_stuff ON emp_stuff(id_emp)
to avoide child table locks?

Thanks in advance
Amine
Re: Locking and foreign key indexes [message #594666 is a reply to message #594665] Sun, 01 September 2013 04:46 Go to previous messageGo to next message
Michel Cadot
Messages: 59191
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Why don't you just try, see and tell us?

Regards
Michel
Re: Locking and foreign key indexes [message #594667 is a reply to message #594666] Sun, 01 September 2013 04:49 Go to previous messageGo to next message
Amine
Messages: 264
Registered: March 2010
Senior Member

I am in holidays Michel Smile I said that someone could know that

[Updated on: Sun, 01 September 2013 04:50]

Report message to a moderator

Re: Locking and foreign key indexes [message #594676 is a reply to message #594667] Sun, 01 September 2013 10:03 Go to previous messageGo to next message
BlackSwan
Messages: 22803
Registered: January 2009
Senior Member
Amine wrote on Sun, 01 September 2013 02:49
I am in holidays Michel Smile I said that someone could know that


We can wait until you return from holiday!

Nothing is impossible for the person who does not actually have to do it.


Re: Locking and foreign key indexes [message #594681 is a reply to message #594676] Sun, 01 September 2013 13:06 Go to previous messageGo to next message
Amine
Messages: 264
Registered: March 2010
Senior Member

drop table test_master cascade constraints
/
create table test_master as select object_id from all_objects
/

--alter table test_master drop constraint PK_test_master cascade;
alter table test_master add constraint PK_test_master primary key (object_id)
/

drop table test_detail cascade constraints
/
create table test_detail as
select *
from 
(
  select object_id, sysdate dat_evt
  from test_master
)
/
alter table test_detail add constraint PK_test_detail primary key (object_id, dat_evt)
/


Now we issue this :
SQL> update test_master set object_id = object_id *1000 where object_id = 1024;

1 row updated.

SQL>
SQL> column object_name format A13
SQL> column username format A13
SQL> select  distinct object_name, username, sid, locked_mode
  2  from v$locked_object l,dba_objects o,v$session s
  3  where o.object_id = l.object_id
  4  and l.session_id = s.sid
  5  ;

OBJECT_NAME   USERNAME             SID LOCKED_MODE
------------- ------------- ---------- -----------
TEST_MASTER   AMINE                136           3

SQL> rollback;

Rollback complete.



As you can see, nothing happened on test_detail cause no FK is declared on it.
Now let's do :
SQL> alter table test_detail add constraint FK_test_detail foreign key (object_i
d) references test_master(object_id)
  2  deferrable
  3  initially deferred
  4  --on delete cascade
  5  /

Table altered.

SQL> update test_master set object_id = object_id *1000 where object_id = 1024;

1 row updated.

SQL>
SQL> column object_name format A13
SQL> column username format A13
SQL> select  distinct object_name, username, sid, locked_mode
  2  from v$locked_object l,dba_objects o,v$session s
  3  where o.object_id = l.object_id
  4  and l.session_id = s.sid
  5  ;

OBJECT_NAME   USERNAME             SID LOCKED_MODE
------------- ------------- ---------- -----------
TEST_DETAIL   AMINE                136           3
TEST_MASTER   AMINE                136           3

SQL> rollback;

Rollback complete.


Now we have a lock on the child table even this one is indexed with the PK. Now let's issue an explicit index on object_id
SQL> create index x_detail on test_detail(object_id);

Index created.

SQL>
SQL> --update test_detail set object_id = object_id *1000 where object_id = 1024
;
SQL> update test_master set object_id = object_id *1000 where object_id = 1024;

1 row updated.

SQL>
SQL> column object_name format A13
SQL> column username format A13
SQL> select  distinct object_name, username, sid, locked_mode
  2  from v$locked_object l,dba_objects o,v$session s
  3  where o.object_id = l.object_id
  4  and l.session_id = s.sid
  5  ;

OBJECT_NAME   USERNAME             SID LOCKED_MODE
------------- ------------- ---------- -----------
TEST_DETAIL   AMINE                136           3
TEST_MASTER   AMINE                136           3

SQL> rollback;

Rollback complete.


Same results. Are locks on child tables inevitable ?

[Updated on: Sun, 01 September 2013 13:12]

Report message to a moderator

Re: Locking and foreign key indexes [message #594682 is a reply to message #594681] Sun, 01 September 2013 13:34 Go to previous messageGo to next message
BlackSwan
Messages: 22803
Registered: January 2009
Senior Member
http://docs.oracle.com/cd/B19306_01/server.102/b14237/dynviews_1147.htm

locked_mode=3 is a row level lock; which is reasonable & expected to ensure PK/FK data integrity.
Re: Locking and foreign key indexes [message #594686 is a reply to message #594665] Sun, 01 September 2013 14:06 Go to previous messageGo to next message
Lalit Kumar B
Messages: 2371
Registered: May 2013
Location: World Wide on the Web
Senior Member
Unindexed foreign key columns are the primary cause of TM lock contention in mode 3. However, this only applies to databases prior to Oracle9i Database. Depending on the operation, when foreign key columns are not indexed, Oracle either takes up a DML share lock (S mode 4) or share row exclusive lock (SRX mode 5) on the child table whenever the parent key or row is modified. (The share row exclusive lock is taken on the child table when the parent row is deleted and the foreign key constraint is created with the ON DELETE CASCADE option. Without this option, Oracle takes the share lock.) The share lock or share row exclusive lock on the child table prohibits other processes from getting a row exclusive lock (RX--mode 3) on the table. The waiting session will wait until the blocking session commits or rolls back its transaction.
Re: Locking and foreign key indexes [message #594687 is a reply to message #594686] Sun, 01 September 2013 14:22 Go to previous messageGo to next message
Michel Cadot
Messages: 59191
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Good, we now know you are able to copy and paste others work.

http://www.confio.com/logicalread/diagnose-oracle-wait-for-tm-enqueue-mode-3-mc01/

Quote:
This article is a complimentary excerpt from Oracle Wait Interface: A Pracitcal Guide to Performance Diagnostics & Tuning, published by McGraw- Hill.


Regards
Michel
Re: Locking and foreign key indexes [message #594688 is a reply to message #594687] Sun, 01 September 2013 14:28 Go to previous messageGo to next message
Lalit Kumar B
Messages: 2371
Registered: May 2013
Location: World Wide on the Web
Senior Member
Yes it is. And it only a part of the excerpt which is relevant to this thread. The McGraw-Hill book has information about Oracle Wait for TM Enqueue from various Oracle versions. Whatever was relevant I copy pasted here. T.Kyte provides a lot of stuff which is never documented, we use it the way we want(not to mention how important it is for all of us).

Does it again violate any of the forum rules Michel?
Re: Locking and foreign key indexes [message #594689 is a reply to message #594688] Sun, 01 September 2013 14:30 Go to previous messageGo to next message
Michel Cadot
Messages: 59191
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
Whatever was relevant I copy pasted here.


And you steal the work, the ONLY think to do is to post a link or, at least, to give the origin not to post it as if it was from you.
For me, you simply acted as a burglar.
And it is useless to answer to this post.

Regards
Michel

[Updated on: Sun, 01 September 2013 14:31]

Report message to a moderator

Re: Locking and foreign key indexes [message #594690 is a reply to message #594689] Sun, 01 September 2013 14:41 Go to previous messageGo to next message
Lalit Kumar B
Messages: 2371
Registered: May 2013
Location: World Wide on the Web
Senior Member
Michel Cadot wrote on Mon, 02 September 2013 01:00
Quote:
Whatever was relevant I copy pasted here.


And you steal the work, the ONLY think to do is to post a link or, at least, to give the origin not to post it as if it was from you.
For me, you simply acted as a burglar.
And it is useless to answer to this post.


I hope you understand the kinda disrespect you make by addressing someone with such harsh words.

It is YOU who thinks it is wrong, at least, it is better than just saying "LMGTFY". Anyway, I refrain myself from posting anything if you are replying.
Re: Locking and foreign key indexes [message #594691 is a reply to message #594690] Sun, 01 September 2013 15:12 Go to previous messageGo to next message
pablolee
Messages: 2625
Registered: May 2007
Location: Scotland
Senior Member
I have to agree with Michel here, maybe not with the way that he wrote it (a little blunt) but you basically passed off someone else's words as your own. That is, put simply, plagiarism. If you are going to use another person's words, have the courtesy to cite them.
Re: Locking and foreign key indexes [message #594692 is a reply to message #594691] Sun, 01 September 2013 15:31 Go to previous messageGo to next message
Amine
Messages: 264
Registered: March 2010
Senior Member

Let's back to the topic please.
I do not see the benefit of the index, we have the same locks with or without it ? Why add an index then ?
Re: Locking and foreign key indexes [message #594693 is a reply to message #594692] Sun, 01 September 2013 16:03 Go to previous messageGo to next message
John Watson
Messages: 4591
Registered: January 2010
Location: Global Village
Senior Member
Amine, you have the locking problem the wrong way round. See this:
orclz> update test_detail set object_id = object_id *1000 where object_id = 1000;

1 row updated.

orclz> select  distinct object_name, username, sid, locked_mode
  2      from v$locked_object l,dba_objects o,v$session s
  3      where o.object_id = l.object_id
  4      and l.session_id = s.sid
  5  /

OBJECT_NAME   USERNAME             SID LOCKED_MODE
------------- ------------- ---------- -----------
TEST_DETAIL   JW                   127           3
TEST_MASTER   JW                   127           3

orclz> rollback;

Rollback complete.

orclz> create index x_detail on test_detail(object_id);

Index created.

orclz> update test_detail set object_id = object_id *1000 where object_id = 1000;

1 row updated.

orclz> select  distinct object_name, username, sid, locked_mode
  2    from v$locked_object l,dba_objects o,v$session s
  3    where o.object_id = l.object_id
  4    and l.session_id = s.sid;

OBJECT_NAME   USERNAME             SID LOCKED_MODE
------------- ------------- ---------- -----------
TEST_DETAIL   JW                   127           3

orclz>
The index means that DML on the child does not require locks on the parent.
Re: Locking and foreign key indexes [message #594694 is a reply to message #594693] Sun, 01 September 2013 16:33 Go to previous messageGo to next message
Amine
Messages: 264
Registered: March 2010
Senior Member

I do not understand why you've turned the update statement over the child table. The problem is when we update a parent table according to what I put in the first post.
Re: Locking and foreign key indexes [message #594695 is a reply to message #594694] Sun, 01 September 2013 16:37 Go to previous messageGo to next message
John Watson
Messages: 4591
Registered: January 2010
Location: Global Village
Senior Member
You've already demonstrated that there is no problem with updating the parent table. I have demonstrated that there is a problem with updating the child table. You can believe these demonstrations, or you can believe the article you referenced.
Re: Locking and foreign key indexes [message #594697 is a reply to message #594695] Sun, 01 September 2013 22:02 Go to previous messageGo to next message
BlackSwan
Messages: 22803
Registered: January 2009
Senior Member
>The problem is when we update a parent table according to what I put in the first post.
The problem is that when you don't know where you are going, then any road you take can get you lost.
Re: Locking and foreign key indexes [message #594700 is a reply to message #594690] Mon, 02 September 2013 00:21 Go to previous messageGo to next message
Michel Cadot
Messages: 59191
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
pablolee wrote on Sun, 01 September 2013 22:12
I have to agree with Michel here, maybe not with the way that he wrote it (a little blunt) but you basically passed off someone else's words as your own. That is, put simply, plagiarism. If you are going to use another person's words, have the courtesy to cite them.


In addition, this is copyrighted materials and so illegal to post it without the authorization of the owners (here the authors and the publisher).

Regards
Michel

[Updated on: Mon, 02 September 2013 00:24]

Report message to a moderator

Re: Locking and foreign key indexes [message #594716 is a reply to message #594700] Mon, 02 September 2013 04:10 Go to previous messageGo to next message
Amine
Messages: 264
Registered: March 2010
Senior Member

Quote:
The problem is that when you don't know where you are going, then any road you take can get you lost.

Sorry BlackSwan, but sometimes my brain cannot as far as your thinking goes. I did not understand your reply.
Re: Locking and foreign key indexes [message #594718 is a reply to message #594716] Mon, 02 September 2013 04:15 Go to previous messageGo to next message
cookiemonster
Messages: 10961
Registered: September 2008
Location: Rainy Manchester
Senior Member
In other words - if you set out to prove the wrong thing you won't get very far.
As John pointed out the problem comes with modifications to the parent locking the child, not the other way round.
Re: Locking and foreign key indexes [message #594720 is a reply to message #594718] Mon, 02 September 2013 04:24 Go to previous messageGo to next message
Amine
Messages: 264
Registered: March 2010
Senior Member

So i've misunderstood what Tom Kyte said ? When he says :
Quote:
So, in short, with releases prior to Oracle Database 11g Release 1, you will want an index on the foreign key of the child table if you do any of the following:

Update the parent table primary key
Delete from the parent table
Merge into the parent table


"Update the parent table primary key" meaning update the columns on the child table that point to the parent table ?

Is that what Tom Kyte meant ?
Re: Locking and foreign key indexes [message #594722 is a reply to message #594720] Mon, 02 September 2013 04:35 Go to previous messageGo to next message
cookiemonster
Messages: 10961
Registered: September 2008
Location: Rainy Manchester
Senior Member
No "Update the parent table primary key" means update the primary key column(s) on the parent table.
Exactly what it says.
Re: Locking and foreign key indexes [message #594723 is a reply to message #594722] Mon, 02 September 2013 04:42 Go to previous messageGo to next message
Amine
Messages: 264
Registered: March 2010
Senior Member

Ok. So why John Watson is updating the child table (master_detail) ?
Ok, he presented a demonstration over an update on the child table : it won't lock the parent table if it has an index on the FK.

But according to Kyte, the purpose of the index is to avoid the lock on the parent table when an update is done on the parent table.

Is my understanding right ?
Re: Locking and foreign key indexes [message #594725 is a reply to message #594723] Mon, 02 September 2013 04:48 Go to previous messageGo to next message
John Watson
Messages: 4591
Registered: January 2010
Location: Global Village
Senior Member
Look, Amine, I would never say that Tom is wrong. But on this occasion, he has missed the point, and you haven't read the article correctly.
I think he is referring to the need for a very short lock, that persists only for the duration of the statement. Your queries will never show that. I am talking about a much more important issue, that Tom doesn't mention.

Re: Locking and foreign key indexes [message #594726 is a reply to message #594723] Mon, 02 September 2013 04:50 Go to previous messageGo to next message
cookiemonster
Messages: 10961
Registered: September 2008
Location: Rainy Manchester
Senior Member
To quote a bit from the Tom Kyte article:
"Delete from the parent table then you should probably index the foreign key in the child table, or a full table lock will be placed on the child table for the duration of the transaction. "

The index is to avoid a table lock on the child when the parent pk/uk columns are updated or the parent is deleted.
The records in the child table that point to the parent row being updated must be locked - that is correct and normal behaviour regardless of whether you have an index or not.
What shouldn't happen (but does in older versions if you don't have the index) is all the records in the child table that don't point to the parent being modified getting locked.
Re: Locking and foreign key indexes [message #594728 is a reply to message #594726] Mon, 02 September 2013 04:57 Go to previous messageGo to next message
cookiemonster
Messages: 10961
Registered: September 2008
Location: Rainy Manchester
Senior Member
And I've now read John's example properly, which shows the locking going the other way, which is news to me.
The problem I was always aware of was mods on the parent causing full table locks on the child. Which was a huge problem and is pretty much fixed in later versions.
Re: Locking and foreign key indexes [message #594729 is a reply to message #594728] Mon, 02 September 2013 04:59 Go to previous messageGo to next message
cookiemonster
Messages: 10961
Registered: September 2008
Location: Rainy Manchester
Senior Member
@John - which version are you doing your tests on?
Re: Locking and foreign key indexes [message #594730 is a reply to message #594725] Mon, 02 September 2013 05:00 Go to previous messageGo to next message
Amine
Messages: 264
Registered: March 2010
Senior Member

Ok, now it's clear !

[Updated on: Mon, 02 September 2013 05:02]

Report message to a moderator

Re: Locking and foreign key indexes [message #594735 is a reply to message #594728] Mon, 02 September 2013 05:07 Go to previous messageGo to next message
John Watson
Messages: 4591
Registered: January 2010
Location: Global Village
Senior Member
cookiemonster wrote on Mon, 02 September 2013 10:57
And I've now read John's example properly, which shows the locking going the other way, which is news to me.
The problem I was always aware of was mods on the parent causing full table locks on the child. Which was a huge problem and is pretty much fixed in later versions.
This was the change that occurred in 9.x. The way I think of it (which may not be perfect) is:

Before 9.x, DML on the child was no problem, but DML on the parent locked the child table (if it was unindexed). Why? Because the transaction on the parent had to prevent an incompatible transaction from starting on the child.
From 9.x, the responsibility was reversed. The transaction on the parent just goes ahead, and then any transaction on the child now takes responsibility for preventing an incompatible transaction starting on the parent. But if there is an index, it doesn't need to - because the transaction that starts on the parent can make the check of whether any child rows are locked.

Does that make sense? If so, I think it could be worded better.

--
update: that was 12.1.0.1, CM, but it really doesn't matter.

[Updated on: Mon, 02 September 2013 05:09]

Report message to a moderator

Re: Locking and foreign key indexes [message #594740 is a reply to message #594735] Mon, 02 September 2013 05:27 Go to previous messageGo to next message
cookiemonster
Messages: 10961
Registered: September 2008
Location: Rainy Manchester
Senior Member
John Watson wrote on Mon, 02 September 2013 11:07

Before 9.x, DML on the child was no problem, but DML on the parent locked the child table (if it was unindexed). Why? Because the transaction on the parent had to prevent an incompatible transaction from starting on the child.

Fair enough

John Watson wrote on Mon, 02 September 2013 11:07

From 9.x, the responsibility was reversed. The transaction on the parent just goes ahead, and then any transaction on the child now takes responsibility for preventing an incompatible transaction starting on the parent.

And you lost me there. Do you mean that mods to the child lock the parent or something else?
If you do mean that then I would have thought that would happen pre 9.x anyway.


John Watson wrote on Mon, 02 September 2013 11:07

update: that was 12.1.0.1, CM, but it really doesn't matter.

I think it does matter cause I can't replicate it on 11.1.0.7.0 or 11.2.0.1.0 and I suspect the case where the parent doesn't get locked is a bug. Otherwise, as far as I can see you end up with the ability to do some modifications to a parent and not others if the child pk has been modified, which would be rather odd.
Re: Locking and foreign key indexes [message #594752 is a reply to message #594740] Mon, 02 September 2013 07:21 Go to previous messageGo to next message
John Watson
Messages: 4591
Registered: January 2010
Location: Global Village
Senior Member
Thanks for making me think about it a bit more. Today is a holiday in the US, so I'm not as busy as usual Smile
Let me try again.
Pre 9.x, Oracle took a pessimistic view, locking just-in-case. If you lock a parent row, you immediately lock all relevant child rows, or the entire table in the absence of an index. So in the SCOTT schema, deleting deptno 40 would lock emp. Or to put it another way, the first transaction ensures that a second incompatible transaction cannot start.
Post 9.x, Oracle takes an optimistic view, minimising locking. So if you delete dept 40, you lock nothing in emp. Then any transaction that starts on emp checks the parent table to see if the parent row is locked. This means that you no longer need to index the foreign key. But the price you pay is that DML on the child row now has to lock the parent row: if I insert an employee for deptno 40, I don't want you deleting 40 before I commit. But if the FK is indexed, that becomes unnecessary, because when you try to delete deptno 40, you can use the FK index to check whether an inciompatible transaction is already running.
Does that make sense? I remember that when the change came in, I worked it out (or so I thought) and wiondered "why didn't they do it that way from the beginning?"
Re: Locking and foreign key indexes [message #594753 is a reply to message #594752] Mon, 02 September 2013 07:30 Go to previous messageGo to next message
Michel Cadot
Messages: 59191
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Because database engine was designed to get the best possible results for the benchmarks that exist at this time. Wink

Regards
Michel
Re: Locking and foreign key indexes [message #594755 is a reply to message #594752] Mon, 02 September 2013 08:06 Go to previous message
cookiemonster
Messages: 10961
Registered: September 2008
Location: Rainy Manchester
Senior Member
John Watson wrote on Mon, 02 September 2013 13:21

Post 9.x, Oracle takes an optimistic view, minimising locking. So if you delete dept 40, you lock nothing in emp.

That's not true.
You can only delete parents if you have either:
a) delete on cascade, in which case the delete will be propogated to the child and the child rows will be locked.
b) deferable constraints, in which case it'll check the child at commit and either throw an error or immediately delete the children based on the presence of cascade. And if it does get to delete the children it will lock them, however the lock will be so fleeting you'll only able to notice it if deleting a truely large number of children.

My understanding of the change between 8 and 9 is that if you don't have an index then the full table lock applies for the length of the transaction in 8 and is only for the length of the statement against the parent in 9+

John Watson wrote on Mon, 02 September 2013 13:21

Then any transaction that starts on emp checks the parent table to see if the parent row is locked.

Don't think it does. If you update a non pk/uk column on the parent then you can also update a non fk column on the child from a different session. If you try to update fk on the child it'll fail because the parent is locked, but that's because it is trying to lock the parent.


John Watson wrote on Mon, 02 September 2013 13:21

This means that you no longer need to index the foreign key. But the price you pay is that DML on the child row now has to lock the parent row: if I insert an employee for deptno 40, I don't want you deleting 40 before I commit. But if the FK is indexed, that becomes unnecessary, because when you try to delete deptno 40, you can use the FK index to check whether an inciompatible transaction is already running.

If I run the following:
SELECT * FROM v$version;

CREATE TABLE par (a NUMBER PRIMARY KEY, c NUMBER);

CREATE TABLE chl (a NUMBER, b NUMBER PRIMARY KEY);

ALTER TABLE chl ADD CONSTRAINT chl_fk_par FOREIGN KEY (a) REFERENCES par (a) ON DELETE CASCADE;

INSERT INTO par (SELECT ROWNUM, ROWNUM FROM dual CONNECT BY LEVEL < 10);
INSERT INTO chl (SELECT ROWNUM, ROWNUM FROM dual CONNECT BY LEVEL < 10);

COMMIT;

UPDATE chl SET a = 2 WHERE a = 1;

select  distinct object_name, username, sid, locked_mode
from v$locked_object l,dba_objects o,v$session s
where o.object_id = l.object_id
and l.session_id = s.sid;

ROLLBACK;

CREATE INDEX chl1 ON chl(a);

UPDATE chl SET a = 2 WHERE a = 1;

select  distinct object_name, username, sid, locked_mode
from v$locked_object l,dba_objects o,v$session s
where o.object_id = l.object_id
and l.session_id = s.sid;

I get two rows from the query (1 for each table obviously) in all cases on 10.2.0.2.0, 10.2.0.4, 11.1.0.7.0 and 11.2.0.1.0.
So if you're getting different results in 12 then that's either a new feature or a bug in 12.
You can replace the updates with inserts, you'll get the same results.


Previous Topic: Advice for procedure
Next Topic: Simple example of (not) using an index
Goto Forum:
  


Current Time: Tue Sep 23 08:09:57 CDT 2014

Total time taken to generate the page: 0.09725 seconds