| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> Re: Q about locks
This metalink article does a very good job of describing parent/child table locks.
Doc ID: Note:33453.1
Subject: (V7) REFERENTIAL INTEGRITY AND LOCKING
Type: FAQ
Status: PUBLISHED
Content Type: TEXT/PLAIN
Creation Date: 26-JAN-1996
Last Revision Date: 20-JUL-2000
Language: USAENG
Document ID: 106754.289
Title: (V7) REFERENTIAL INTEGRITY and LOCKING
Creation Date: 20-October-1994
Last Revision Date: 24-January-1996
Revision Number: 1
Product: RDBMS
Product Version: Oracle7
Platform: GENERIC
Information Type: SOLUTION
Impact: MEDIUM
Abstract: This bulletin explains what referential
integrity means and how locking takes place with
tables joined by the referential integrity rule.
Keywords: REFERENTIAL;INTEGRITY;LOCKING;CONSTRAINT;PRIMARY;FOREIGN
-------------------------------------------------------------------------------
REFERENTIAL INTEGRITY and LOCKING
This bulletin explains what referential integrity means and how locking takes place with tables joined by the referential integrity rule. In addition, this bulletin explains how inserting/updating/deleting one table can cause another table to get locked.
REFERENTIAL INTEGRITY: is a rule defined on a column (or set of columns) in one table that allows the insert or update of a row only if the value for the column or set of columns (in the child table) matches the value in a column of a related table (parent table).
Example 1:
SQL> create table DEPT (deptno number constraint pk_dept primary key,
dname varchar2(10))
SQL> create table EMP (deptno number(2) constraint fk_deptno references
dept(deptno), ename varchar2(20))
In the above example "DEPT" is the parent table having the primary key constraint 'pk_dept' on the 'deptno' column. Similarly "EMP" is the child table having the foreign key constraint 'fk_deptno' on the 'deptno' column. However, this foreign key constraint references the 'deptno' column of the parent table (DEPT) thus enforcing the referential integrity rule. Therefore you cannot add an employee into a department number that doesn't exist in the DEPT table.
Example 2:
SQL> insert into DEPT values (1, 'COSTCENTER');
1 row created.
SQL> insert into EMP values (1, 'SCOTT');
1 row created.
SQL> insert into EMP values (2, 'SCOTT'); insert into EMP values (2, 'SCOTT')
*
ERROR at line 1:
ORA-02291: integrity constraint (SCOTT.FK_DEPTNO) violated - parent key not
found
The query that can be issued to find out the primary and foreign key relation is as follows:
SQL> select a.owner for_owner, a.table_name for_table, a.constraint_name
for_constr, b.owner pri_owner, b.table_name pri_table, b.constraint_name
pri_constr from user_constraints a, user_constraints b
where a.r_constraint_name = b.constraint_name
and a.constraint_type = 'R'
and b.constraint_type = 'P';
FOR_OWNER FOR_TABLE
------------------------------ ------------------------------
FOR_CONSTR PRI_OWNER
------------------------------ ------------------------------
PRI_TABLE PRI_CONSTR
------------------------------ ------------------------------
SCOTT EMP
FK_DEPTNO SCOTT
DEPT PK_DEPT
where USER_CONSTRAINTS : data dictionary view
CONSTRAINT_TYPE = 'R' : stands for the foreign key constraint
CONSTRAINT_TYPE = 'P' : stands for the primary key constraint
The data dictionary contains the following views of interest with integrity constraints:
Each example displays output from a Data Dictionary object, V$LOCK. This view gives information about the different types of locks held within the In order to fully understand the output of this view, below is a description of this object.
SQL> desc v$lock;
Name Null? Type
------------------------------- -------- ----
ADDR RAW(4)
KADDR RAW(4)
SID NUMBER
TYPE VARCHAR2(2)
ID1 NUMBER
ID2 NUMBER
LMODE NUMBER
REQUEST NUMBER
where ADDR = address of lock state object
KADDR = address of lock
SID = identifier of process holding the lock
TYPE = resource type
ID1 = resource identifier #1
ID2 = resource identifier #2
LMODE = lock mode held: 1 (null), 2 (row share), 3 (row exclusive),
4 (share), 5 (share row exclusive),
6 (exclusive)
REQUEST = lock mode requested (same values as LMODE)
TYPE LOCK ID1 LOCK ID2
a) TX(transaction) Decimal representation of Decimal rrepresentation
rollback segment number of "wrap" number (number of
and slot number times the rollback slot has
been reused)
b) TM(table locks) Object id of table being Always 0
modified
c) UL(user supplied Please refer to Appendix B-81 of the Oracle7 Server
lock) Administrator's Guide.
Examples:
NOTE: In all the examples given below, the object_id for the DEPT and the EMP
tables are 2989 and 2991 respectively. The ID1 column from the V$LOCK data
dictionary object corresponds to the OBJECT_ID column from the DBA_OBJECTS
view.
SQL> select object_name from sys.dba_objects where object_id = 2989;
OBJECT_NAME
OBJECT_NAME
NOTE: In 7.1.6 and higher, an insert, update, and delete statement on the child table will not acquire any locks on the parent table, although insert and update statements will wait for a row-lock on the index of the parent table to clear.
SQL> insert into DEPT values (1, 'COSTCENTER');
SQL> commit;
SQL> insert into EMP values (1, 'SCOTT');
SQL> select * from v$lock;
ADDR KADDR SID TY ID1 ID2 LMODE REQUEST
-------- -------- ---------- -- ---------- ---------- ---------- ----------
4002FB14 4002FB24 2 MR 6 0 4 0 4002FB6C 4002FB7C 2 MR 5 0 4 0 4002FB98 4002FBA8 2 MR 4 0 4 0 4002FBC4 4002FBD4 2 MR 3 0 4 0 4002FC1C 4002FC2C 2 MR 1 0 4 0 4002FBF0 4002FC00 2 MR 2 0 4 0 4002FB40 4002FB50 3 RT 1 0 6 0 40078664 40078678 15 TM 2989 0 4 0 4007AD74 4007AE08 15 TX 196667 54 6 0 400786C8 400786DC 15 TM 2991 0 3 0
2) AN INSERT/DELETE/UPDATE ON THE PARENT TABLE CAUSES THE CHILD TABLE TO GET LOCKED. A share lock (LMODE=4) of the entire child table is required until the transaction containing the insert/delete/update statement for the parent table is committed, thus preventing any modifications to the child table.
NOTE: In 7.1.6 and higher, inserts into the parent table do not lock the child table.
SQL> update dept set deptno = 1;
SQL> select * from v$lock;
ADDR KADDR SID TY ID1 ID2 LMODE REQUEST
-------- -------- ---------- -- ---------- ---------- ---------- ----------
4002FB14 4002FB24 2 MR 6 0 4 0
4002FB6C 4002FB7C 2 MR 5 0 4 0
4002FB98 4002FBA8 2 MR 4 0 4 0
4002FBC4 4002FBD4 2 MR 3 0 4 0
4002FC1C 4002FC2C 2 MR 1 0 4 0
4002FBF0 4002FC00 2 MR 2 0 4 0
4002FB40 4002FB50 3 RT 1 0 6 0
40078664 40078678 15 TM 2991 0 4 0
4007AD74 4007AE08 15 TX 196667 54 6 0
400786C8 400786DC 15 TM 2989 0 3 0
**** WITH INDEXES ****
SQL> create index ind_emp on emp (deptno, ename);
SQL> insert into DEPT values (1, 'COSTCENTER');
SQL> commit;
SQL> insert into EMP values (1, 'SCOTT');
SQL> select * from v$lock;
ADDR KADDR SID TY ID1 ID2 LMODE REQUEST
-------- -------- ---------- -- ---------- ---------- ---------- ----------
4002FB14 4002FB24 2 MR 6 0 4 0 4002FB6C 4002FB7C 2 MR 5 0 4 0 4002FB98 4002FBA8 2 MR 4 0 4 0 4002FBC4 4002FBD4 2 MR 3 0 4 0 4002FC1C 4002FC2C 2 MR 1 0 4 0 4002FBF0 4002FC00 2 MR 2 0 4 0 40078664 4002FB50 3 RT 1 0 6 0 40078664 40078678 15 TX 196667 54 6 0 4007AD74 4007AE08 15 TM 2991 0 3 0
2) AN INSERT/DELETE/UPDATE ON THE PARENT TABLE WILL ONLY ACQUIRE A ROW LEVEL LOCK ON THE PARENT TABLE IF THERE IS AN INDEX ON THE FOREIGN KEY OF THE CHILD TABLE. The child table will have NO locks on it and so any type of modifications can be made to the child table.
NOTE: In v7.1.6 and higher, inserts, updates and deletes on the parent table do not require any locks on the child table, although updates and deletes will wait for row-level locks to clear on the child table index. If the child table specifies ON DELETE CASCADE, waiting and locking rules are the same as if you deleted from the child table after performing the delete from the parent.
SQL> update DEPT set deptno = 1;
SQL> select * from v$lock;
ADDR KADDR SID TY ID1 ID2 LMODE REQUEST
-------- -------- ---------- -- ---------- ---------- ---------- ----------
4002FB14 4002FB24 2 MR 6 0 4 0 4002FB6C 4002FB7C 2 MR 5 0 4 0 4002FB98 4002FBA8 2 MR 4 0 4 0 4002FBC4 4002FBD4 2 MR 3 0 4 0 4002FC1C 4002FC2C 2 MR 1 0 4 0 4002FBF0 4002FC00 2 MR 2 0 4 0 40078664 4002FB50 3 RT 1 0 6 0 40078664 40078678 15 TX 196667 54 6 0 4007AD74 4007AE08 15 TM 2989 0 3 0
Oracle Worldwide Customer Support
.
Copyright (c) 1995,2000 Oracle Corporation. All Rights Reserved. Legal Notices and Terms of Use.
> Hi DBAs > > Let say I have a child table A(id, b_id, c) and parent table B(id, d, e, > ...). The field A.b_id has foreign key to B.id and there is a concatened > index ind1(b_id, c). Does Oracle lock table A in share mode when I update PK > (id field) in table B ? Or is it possible that Oracle uses an index entries > to hold the lock? > > What about if the index has the same fields in reverse order (ind1(c, > b_id)) ? > > > Thanks in advance > > Ed > > Sorry my English > -- > Please see the official ORACLE-L FAQ: http://www.orafaq.com > -- > Author: Shevtsov, Eduard > INET: EShevtsov_at_flagship.ru > > Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051 > San Diego, California -- Public Internet access / Mailing Lists > --------------------------------------------------------------------Received on Tue Oct 24 2000 - 12:13:54 CDT
 |
 |