Referential Integrity 引用完整性

In higher versions, as the locks are not held for the entire duration of the transaction 事务中其他操作不会锁定主、子表 , but only during the actual execution of the UPDATE/DELETE, you may not be able to "catch" them.

创建外键index 避免长时间锁

Purpose

This Document explains what referential integrity means and how locking takes place with tables joined by the referential integrity rule. In addition, the Document explains how inserting/updating/deleting one table can cause another table to become locked.

Questions and Answers

What is Referential Integrity?

Referential Integrity is a rule defined on a key (a column or set of columns) in one table that guarantees that the values in that key match the values in a key in a related table (the referenced value). Referential integrity includes the rules that dictate what types of data manipulation are allowed on referenced values and how these actions affect dependent values. For more information on referential integrity (including information about Primary and foreign keys), see:

Example of Referential Integrity

CREATE TABLE DEPT
  (
    deptno NUMBER CONSTRAINT pk_dept PRIMARY KEY,
    dname  VARCHAR2(10)
  );

CREATE TABLE EMP
  (
    deptno NUMBER(2) CONSTRAINT fk_deptno REFERENCES dept(deptno),
    ename  VARCHAR2(20)
  );

DEPT is the parent table having a primary key constraint 'pk_dept' on the 'deptno' column.
EMP   is the child table having a foreign key constraint 'fk_deptno' on the 'deptno' column.

The foreign key constraint 'fk_deptno' on table EMP references the 'deptno' column of the parent table DEPT thus enforcing the referential integrity rule so that you cannot add an employee into a department number that does not exist in the DEPT table.
 

In the above example we see that an error occurs when trying to insert a non-existent DEPTNO  into table EMP.

Data Dictionary Views and Script

The data dictionary contains the following views of interest relating to integrity constraints:

• ALL_CONSTRAINTS
• ALL_CONS_COLUMNS
• USER_CONSTRAINTS
• USER_CONS_COLUMNS
• DBA_CONSTRAINTS
• DBA_CONS_COLUMNS

column owner_name format a10 
column table_name format a25 
column key_name format a15 
column referencing_table format a15 
column foreign_key_name format a15 

SELECT a.owner owner_name,
  a.table_name table_name,
  a.constraint_name key_name,
  b.table_name referencing_table,
  b.constraint_name foreign_key_name
FROM user_constraints a,
  user_constraints b
WHERE a.constraint_name = b.r_constraint_name
AND b.constraint_type   = 'R'
ORDER BY 1,  2,  3,  4;

OWNER_NAME TABLE_NAME            KEY_NAME        REFERENCING_TAB FOREIGN_KEY_NAME 
---------- --------------------- --------------- --------------- ---------------- 
     SCOTT                  DEPT         PK_DEPT             EMP        FK_DEPTNO

CONSTRAINT_TYPE = 'R' : stands for the foreign key constraint
CONSTRAINT_TYPE = 'P' : stands for the primary key constraint

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 V$LOCK. This view gives information about the different types of locks held. In order to fully understand the output of this view, See the following:

EXAMPLES

column table_name format a25
SELECT object_id,
  object_name
FROM dba_objects
WHERE object_name IN ('EMP','DEPT')
AND owner          = 'SCOTT';

  OBJECT_ID OBJECT_NAME
---------- -------------------------
     13033 DEPT
     13035 EMP

WITHOUT INDEXES

1. Insert/delete/update into child table

SQL> INSERT INTO DEPT VALUES (1, 'COSTCENTER');
SQL> COMMIT; 
SQL> INSERT INTO EMP VALUES (1, 'SCOTT'); 
SQL> SELECT sid,  type,  id1,  id2,
  lmode,  request,  block
FROM v$lock
WHERE sid IN
  (SELECT sid FROM v$session WHERE audsid = userenv('SESSIONID')
  );

SID        TY ID1        ID2        LMODE      REQUEST    BLOCK 
---------- -- ---------- ---------- ---------- ---------- ---------- 
        15 TX     589839      56552          6          0          0
        15 TM      13035          0          3          0          0
        15 TM      13033          0          3          0          0

A row exclusive lock(LMODE=3) is needed on both parent and child table

2. Insert/delete/update into parent table

SQL> update dept set deptno = 1; <
SQL> SELECT SID,TYPE,ID1,ID2,LMODE,REQUEST,BLOCK FROM v$LOCK 
where sid in (select sid from v$session where audsid = userenv('SESSIONID')); 
SID        TY ID1        ID2        LMODE      REQUEST    BLOCK
---------- -- ---------- ---------- ---------- ---------- ----------
        15 TX     589839      56552          6          0          0
        15 TM      13033          0          3          0          0

Row exclusive lock (LMODE=3) taken out on parent table and no lock on child table;
WITH INDEXES

1. Insert/delete/update into child table

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 SID,TYPE,ID1,ID2,LMODE,REQUEST,BLOCK FROM v$LOCK
where sid in (select sid from v$session where audsid = userenv('SESSIONID'));

       SID TY        ID1        ID2      LMODE    REQUEST      BLOCK      
---------- -- ---------- ---------- ---------- ---------- ----------       
       15 TX     393232      54853          6          0          0       
       15 TM      13035          0          3          0          0
       15 TM      13033          0          2          0          0

Row share lock (LMODE=2) is taken out parent table ,whereas a row exclusive lock is needed on the child table.(LMODE=3)

2. Insert/delete/update into parent table

SQL> update DEPT set deptno = 1; 

1 row updated. 

SQL> SELECT SID,TYPE,ID1,ID2,LMODE,REQUEST,BLOCK FROM v$LOCK
where sid in (select sid from v$session where audsid = userenv('SESSIONID'));

SID        TY ID1        ID2        LMODE      REQUEST    BLOCK
---------- -- ---------- ---------- ---------- ---------- ----------
        15 TX     589839      56552          6          0          0 
        15 TM      13035          0          2          0          0 
        15 TM      13033          0          3          0          0

Exclusive share lock (LMODE=3) is taken out parent table ,whereas a row share lock is needed on the child table.(LMODE=2)

Changes in behavior in different versions

When your application has referential integrity and attempts to modify the child/parent table, Oracle will get additional locking on the parent/child table when there is no index on the foreign key. It is therefore recommended to add indexes for all foreign key defined in the database.

Table Lock (TM) behavior is different for different versions of Oracle:

Oracle 8.1.7 and 9.0.1
Mode 3 (SX) lock on the table being modified

Oracle 9.2, 10.1 and 10.2
As well as the mode 3(SX) lock on table being modified, a mode 2 (SS) lock is required on the linked table (child table when modifying the parent table, or parent table when modifying the child table)

From Oracle 11.1
As a result of bug 5909305, this behavior has changed an a mode 3 (SX) lock is required on the linked table.
Provided fix for bug 6117274 is present then the above change can be disabled by setting the parameter  "_fix_control" = '5909305:OFF'.

When indexes are not present on child table foreign keys columns, on top of the previous locking situation Oracle will also require:

a) In 8.1.7, 'mode 4 Share' locks on the child table when updating/deleting from the parent table. The lock mode even becomes a 'mode 5 S/Row-X (SSX)' lock when deleting from the parent table with a 'delete cascade' foreign key constraint.Those locks can't be disabled (ORA-00069) and are held during the full transaction time.

b) In 9.0.1, Oracle only need those additional locks during the execution time of the UPDATE or DELETE. Those locks are downgraded to 'mode 3 Row-X (SX)' locks when the execution is finished. It is thus an improvement compared to Oracle 8.1.7.

c) 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.

In higher versions, as the locks are not held for the entire duration of the transaction, but only during the actual execution of the UPDATE/DELETE, you may not be able to "catch" them.

Purpose

Provide a script to check for Foreign Key locking issues for a specific user.

Troubleshooting Steps

Requirements

Oracle 7 or later

Configuring

Access Privileges:

If run as owner of objects no special privileges required

Usage:

sqlplus / @[SCRIPTFILE]

Instructions

PROOFREAD THIS SCRIPT BEFORE USING IT!
Due to differences in the way text editors, e-mail packages, and operating systems handle text formatting (spaces, tabs, and carriage returns), this script may not be in an executable state when you first receive it. Check over the script to ensure that errors of this type are corrected. The script will produce an output file named [outputfile].
This file can be viewed in a browser or uploaded for support analysis.

NOTE: This script may miss composite indexes, so run the following script in the Document 16428.1 to check for composite indexes:

Document 16428.1  Script to check for Missing Indexes for Foreign Keys

This script checks the current users Foreign Keys to make sure of the following:

1) All the FK columns have indexes to prevent possible locking issues

2) Checks the order of the indexed columns. To prevent locking problems, columns MUST be indexed in the same order as the FK is defined.

3) If the script finds a mismatch, the script reports the correct order of columns to prevent the locking problem.

IMPORTANT, PLEASE NOTE : Locking problems due to a FK column not being indexed, are discussed in the manual here:

Oracle Database Online Documentation 12c Release 1 (12.1) / Database Administration
Database Concepts
Chapter 9 Data Concurrency and Consistency
Locks and Foreign Keys
http://docs.oracle.com/database/121/CNCPT/consist.htm#CNCPT1340

Legacy information: Starting with Oracle9i, Release 1 (9.0.1), Oracle no longer requires a share lock on unindexed foreign keys when doing an update or delete on the primary key. It still obtains the table-level share lock, but then releases it immediately after obtaining it. If multiple primary keys are update or deleted, the lock is obtained and released once for each row.

Script

SET ECHO off
REM NAME: TFSFKCHLK.SQL
REM USAGE:"@path/tfsfkchk"
REM --------------------------------------------------------------------------
REM REQUIREMENTS:
REM None -- checks only the USER_ views
REM --------------------------------------------------------------------------
REM This file checks the current users Foreign Keys to make sure of the
REM following:
REM
REM 1) All the FK columns are have indexes to prevent a possible locking
REM problem that can slow down the database.
REM
REM 2) Checks the ORDER OF THE INDEXED COLUMNS. To prevent the locking
REM problem the columns MUST be index in the same order as the FK is
REM defined.
REM
REM 3) If the script finds and miss match the script reports the correct
REM order of columns that need to be added to prevent the locking
REM problem.
REM
REM
REM
REM -------------------------------------------------------------------------
REM Main text of script follows:

drop table ck_log;

create table ck_log (
LineNum number,
LineMsg varchar2(2000));

declare
t_CONSTRAINT_TYPE user_constraints.CONSTRAINT_TYPE%type;
t_CONSTRAINT_NAME USER_CONSTRAINTS.CONSTRAINT_NAME%type;
t_TABLE_NAME USER_CONSTRAINTS.TABLE_NAME%type;
t_R_CONSTRAINT_NAME USER_CONSTRAINTS.R_CONSTRAINT_NAME%type;
tt_CONSTRAINT_NAME USER_CONS_COLUMNS.CONSTRAINT_NAME%type;
tt_TABLE_NAME USER_CONS_COLUMNS.TABLE_NAME%type;
tt_COLUMN_NAME USER_CONS_COLUMNS.COLUMN_NAME%type;
tt_POSITION USER_CONS_COLUMNS.POSITION%type;
tt_Dummy number;
tt_dummyChar varchar2(2000);
l_Cons_Found_Flag VarChar2(1);
Err_TABLE_NAME USER_CONSTRAINTS.TABLE_NAME%type;
Err_COLUMN_NAME USER_CONS_COLUMNS.COLUMN_NAME%type;
Err_POSITION USER_CONS_COLUMNS.POSITION%type;

tLineNum number;

cursor UserTabs is
select table_name
from user_tables
order by table_name;

cursor TableCons is
select CONSTRAINT_TYPE,
CONSTRAINT_NAME,
R_CONSTRAINT_NAME
from user_constraints
where OWNER = USER
and table_name = t_Table_Name
and CONSTRAINT_TYPE = 'R'
order by TABLE_NAME, CONSTRAINT_NAME;

cursor ConColumns is
select CONSTRAINT_NAME,
TABLE_NAME,
COLUMN_NAME,
POSITION
from user_cons_columns
where OWNER = USER
and CONSTRAINT_NAME = t_CONSTRAINT_NAME
order by POSITION;

cursor IndexColumns is
select TABLE_NAME,
COLUMN_NAME,
POSITION
from user_cons_columns
where OWNER = USER
and CONSTRAINT_NAME = t_CONSTRAINT_NAME
order by POSITION;

DebugLevel number := 99; -- >>> 99 = dump all info`
DebugFlag varchar(1) := 'N'; -- Turn Debugging on
t_Error_Found varchar(1);

begin

tLineNum := 1000;
open UserTabs;
LOOP
Fetch UserTabs into t_TABLE_NAME;
t_Error_Found := 'N';
exit when UserTabs%NOTFOUND;

-- Log current table
tLineNum := tLineNum + 1;
insert into ck_log ( LineNum, LineMsg ) values
( tLineNum, NULL );

tLineNum := tLineNum + 1;
insert into ck_log ( LineNum, LineMsg ) values
( tLineNum, 'Checking Table '||t_Table_Name);

l_Cons_Found_Flag := 'N';
open TableCons;
LOOP
FETCH TableCons INTO t_CONSTRAINT_TYPE,
t_CONSTRAINT_NAME,
t_R_CONSTRAINT_NAME;
exit when TableCons%NOTFOUND;

if ( DebugFlag = 'Y' and DebugLevel >= 99 )
then
begin
tLineNum := tLineNum + 1;
insert into ck_log ( LineNum, LineMsg ) values
( tLineNum, 'Found CONSTRAINT_NAME = '|| t_CONSTRAINT_NAME);

tLineNum := tLineNum + 1;
insert into ck_log ( LineNum, LineMsg ) values
( tLineNum, 'Found CONSTRAINT_TYPE = '|| t_CONSTRAINT_TYPE);

tLineNum := tLineNum + 1;
insert into ck_log ( LineNum, LineMsg ) values
( tLineNum, 'Found R_CONSTRAINT_NAME = '|| t_R_CONSTRAINT_NAME);
commit;
end;
end if;

open ConColumns;
LOOP
FETCH ConColumns INTO
tt_CONSTRAINT_NAME,
tt_TABLE_NAME,
tt_COLUMN_NAME,
tt_POSITION;
exit when ConColumns%NOTFOUND;
if ( DebugFlag = 'Y' and DebugLevel >= 99 )
then
begin
tLineNum := tLineNum + 1;
insert into ck_log ( LineNum, LineMsg ) values
( tLineNum, NULL );

tLineNum := tLineNum + 1;
insert into ck_log ( LineNum, LineMsg ) values
( tLineNum, 'Found CONSTRAINT_NAME = '|| tt_CONSTRAINT_NAME);

tLineNum := tLineNum + 1;
insert into ck_log ( LineNum, LineMsg ) values
( tLineNum, 'Found TABLE_NAME = '|| tt_TABLE_NAME);

tLineNum := tLineNum + 1;
insert into ck_log ( LineNum, LineMsg ) values
( tLineNum, 'Found COLUMN_NAME = '|| tt_COLUMN_NAME);

tLineNum := tLineNum + 1;
insert into ck_log ( LineNum, LineMsg ) values
( tLineNum, 'Found POSITION = '|| tt_POSITION);
commit;
end;
end if;

begin
select 1 into tt_Dummy
from user_ind_columns
where TABLE_NAME = tt_TABLE_NAME
and COLUMN_NAME = tt_COLUMN_NAME
and COLUMN_POSITION = tt_POSITION;

if ( DebugFlag = 'Y' and DebugLevel >= 99 )
then
begin
tLineNum := tLineNum + 1;
insert into ck_log ( LineNum, LineMsg ) values
( tLineNum, 'Row Has matching Index' );
end;
end if;
exception
when Too_Many_Rows then
if ( DebugFlag = 'Y' and DebugLevel >= 99 )
then
begin
tLineNum := tLineNum + 1;
insert into ck_log ( LineNum, LineMsg ) values
( tLineNum, 'Row Has matching Index' );
end;
end if;

when no_data_found then
if ( DebugFlag = 'Y' and DebugLevel >= 99 )
then
begin
tLineNum := tLineNum + 1;
insert into ck_log ( LineNum, LineMsg ) values
( tLineNum, 'NO MATCH FOUND' );
commit;
end;
end if;

t_Error_Found := 'Y';

select distinct TABLE_NAME
into tt_dummyChar
from user_cons_columns
where OWNER = USER
and CONSTRAINT_NAME = t_R_CONSTRAINT_NAME;

tLineNum := tLineNum + 1;
insert into ck_log ( LineNum, LineMsg ) values
( tLineNum, 'Changing data in table '||tt_dummyChar
||' will lock table ' ||tt_TABLE_NAME);

commit;
tLineNum := tLineNum + 1;
insert into ck_log ( LineNum, LineMsg ) values
( tLineNum,'Create an index on table '||tt_TABLE_NAME
||' with the following columns to remove lock problem');

open IndexColumns ;
loop
Fetch IndexColumns into Err_TABLE_NAME,
Err_COLUMN_NAME,
Err_POSITION;
exit when IndexColumns%NotFound;
tLineNum := tLineNum + 1;
insert into ck_log ( LineNum, LineMsg ) values
( tLineNum,'Column = '||Err_COLUMN_NAME||' ('||Err_POSITION||')');
end loop;
close IndexColumns;
end;
end loop;
commit;
close ConColumns;
end loop;
if ( t_Error_Found = 'N' )
then
begin
tLineNum := tLineNum + 1;
insert into ck_log ( LineNum, LineMsg ) values
( tLineNum,'No foreign key errors found');
end;
end if;
commit;
close TableCons;
end loop;
commit;
end;
/

select LineMsg
from ck_log
where LineMsg NOT LIKE 'Checking%' AND
LineMsg NOT LIKE 'No foreign key%'
order by LineNum
/

Sample Output 

LINEMSG
--------------------------------------------------------------------------
Changing data in table EMP will lock table DEPT
Create an index on the following columns to remove lock
problem

Column = DEPTNO (1)


Changing data in table EMP will lock table EMP
Create an index on the following columns to remove lock
problem

Column = MGR (1)


Changing data in table ITEMS will lock table ITEM_CATEGORIES
Create an index on the following columns to remove lock
problem

Column = ITEM_CAT (1)
Column = ITEM_BUS_UNIT (2)


Changing data in table ITEMS will lock table ITEM_CATEGORIES
Create an index on the following columns to remove lock
problem

Column = ITEM_CAT (1)
Column = ITEM_BUS_UNIT (2)


Changing data in table CUSTOMER will lock table ORD
Create an index on the following columns to remove lock
problem

Column = CUSTID (1)