-
When a child table doesn't have its FK indexed, whether the lock will affect only the parent row or the whole parent table?
Suppose I have the following tables
PARENT ( colp number not null primary key,
col2 number );
CHILD ( COLc number not null Primary Key,
COLp number,
COLx number,
constraint fk_colp foreign key (colp) references PARENT(colp)
) ;
Assume that I do not have index defined on "colp" of CHILD.
Now when I delete/insert/update a record from CHILD table, what kind of Lock it acquires on the parent table?.
Will it acquire any lock, if the parent row is not at all afected?
(say insert or update on colx)
If it acquires any lock for any of the DML, will it just Lock the Parent record or it acquires a lock on the entire table?
What is the case when the operation is on the PARENT table?
Note: I went thru many of the documents (metalink) and couldn't decide whether the lock is just on the parent records or on the entire table?
-
What version of Oracle?
The behavior of this 'situation' (bug, in my humble opinion) is different in different versions, AFAIK.
- Chris
-
Well, I gotta run. Here is a link for the behaviour under 8i:
http://oradoc.photo.net/ora8doc/DOC/...1/ch6.htm#1574
The behaviour is that the entire child table will be locked when you delete a record from the parent or update the key field in the parent.
Unfortunately, what you are describing sounds like the behaviour under 7.x, where the parent table gets locked. Unfortunately, I don't remember the details under 7.x and don't have a reference handy.
Good Luck,
- Chris
-
Before I go thru the link, the version of my Oracle is 8.1.6
-
Then the link I gave you is correct, as well as the description I gave you. The questions you were asking reflected the behaviour under 7.x. As I said, it changed. I got caught by the same thing when I switched projects and, hence, versions last year.
The link I gave you is to a mirror site of the actual Oracle docs, you can find the same thing at the actual OTN site by serching for "Concurrency Control, Indexes, and Foreign Keys". This is Oracle's actual explanation of the scenario.
Also, here is a quick script that *should* find any non-indexed FK columns for you. Mind you, I'm about a week away from re-visiting this entire issue myself, so if there is a flaw in my script, please let me know.
SELECT
---*
FROM
---ALL_CONS_COLUMNS---ACC
WHERE
---EXISTS (
------------SELECT
---------------1
------------FROM
---------------ALL_CONSTRAINTS---AC
------------WHERE
---------------AC.CONSTRAINT_TYPE---=---'R'------------------AND
---------------AC.OWNER---------------=---(owner)------------AND
---------------AC.CONSTRAINT_NAME---=---ACC.CONSTRAINT_NAME
---------)---AND
---NOT EXISTS (
------------SELECT
---------------1
------------FROM
---------------ALL_IND_COLUMNS---AIC
------------WHERE
---------------AIC.INDEX_OWNER------=---(owner)------------AND
---------------AIC.TABLE_NAME---------=---ACC.TABLE_NAME---------AND
---------------AIC.COLUMN_NAME------=---ACC.COLUMN_NAME------AND
---------------AIC.COLUMN_POSITION---=---ACC.POSITION
------------)
Just replace '---' with ' ' or a tab char. Also, replace (owner) with the name of your schema owner.
Good Luck,
- Chris
[Edited by chrisrlong on 02-16-2001 at 10:27 AM]
-
Chris:
Thank you very much for your response. The link is the exact thing I was looking for.
Regards,
Prince.
