REFERENTIAL INTEGRITY and LOCKING

最新推荐文章于 2024-01-24 11:21:41 发布
cni78203
最新推荐文章于 2024-01-24 11:21:41 发布
阅读量132
点赞数
文章标签：数据库
copy from http://www.myoracleguide.com/xl/REFERENTIAL_INTEGRITY_and_LOCKING.htm[@more@]
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:

a) ALL_CONSTRAINTS
b) ALL_CONS_CONSTRAINTS
c) CONSTRAINT_COLUMNS
d) CONSTRAINT_DEFS
e) USER_CONSTRAINTS
f) USER_CONS_COLUMNS
g) USER_CROSS_REFS
h) DBA_CONSTRAINTS
i) DBA_CONS_COLUMNS
j) DBA_CROSS_REFS


LOCKING:   Indexes play an important part when dealing with referential
integrity and locking. The existence of an index determines the type of lock
necessary, if any. Below are examples that will describe this locking
phenomenon.

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
-------------------------------------------------------------------------------
DEPT

SQL> select object_name from sys.dba_objects where object_id = 2991;

OBJECT_NAME
-------------------------------------------------------------------------------
EMP


                           **** WITHOUT INDEXES  ****


1) AN INSERT/DELETE/UPDATE INTO THE CHILD TABLE CAUSES THE PARENT TABLE TO GET
   LOCKED. Notice that a share lock (LMODE=4) of the entire parent table is
   required until the transaction containing the insert/delete/update statement
   for the child table is committed, thus preventing any modifications to the
   parent table.

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 anymore, 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
where sid in (select sid from v$session where audsid = userenv('SESSIONID'));

ADDR     KADDR           SID TY        ID1        ID2      LMODE    REQUEST
-------- -------- ---------- -- ---------- ---------- ---------- ----------
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. It even can be a SSX (LMODE=5) lock when deleting from the 
   parent table with a delete cascade constraint.

NOTE:  In 7.1.6 and higher, INSERT into the parent table do not lock the child
table. In Oracle 9.0.1 or higher, those looks became temporal: they are only needed
during the execution time of the UPDATE/DELETE statements. Those locks are 
downgraded to 'mode 3 Row-X (SX)' locks when the execution is finished. 
In 9.2.0, the downgraded 'mode 3 Row-X (SX)' locks are no longer required 
except when deleting from a parent table with a 'delete cascade' constraint.

SQL> update dept set deptno = 1;

SQL> select * from v$lock
where sid in (select sid from v$session where audsid = userenv('SESSIONID'));

ADDR     KADDR           SID TY        ID1        ID2      LMODE    REQUEST
-------- -------- ---------- -- ---------- ---------- ---------- ----------
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  ****


1) AN INSERT/DELETE/UPDATE ON THE CHILD TABLE DOES NOT PLACE LOCKS OF ANY KIND
   ON THE PARENT TABLE IF THERE IS AN INDEX ON THE FOREIGN KEY OF THE CHILD
   TABLE.  Therefore, any type of DML statement can be issued on the parent
   table, including inserts, updates, deletes and queries.

NOTE:  In 9.2.0 onwards, Oracle requires 'mode 2 Row-S (SS)' locks on the 
parent table (see                                                                                                  ).

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
where sid in (select sid from v$session where audsid = userenv('SESSIONID'));

ADDR     KADDR           SID TY        ID1        ID2      LMODE    REQUEST
-------- -------- ---------- -- ---------- ---------- ---------- ----------
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. In 9.2.0 onwards, Oracle requires 'mode 2 Row-S (SS)' locks on the 
child table (see                                                                                                  ).

SQL> update DEPT set deptno = 1;

SQL> select * from v$lock
where sid in (select sid from v$session where audsid = userenv('SESSIONID'));

ADDR     KADDR    SID        TY ID1        ID2        LMODE      REQUEST
-------- -------- ---------- -- ---------- ---------- ---------- ----------
40078664 40078678         15 TX     196667         54          6          0
4007AD74 4007AE08         15 TM       2989          0          3          0