REFERENTIAL INTEGRITY AND LOCKING [ID 33453.1]
Applies to:
Oracle Server - Personal Edition
Oracle Server - Standard Edition
Information in this document applies to any platform.
Purpose
This note explains what referential integrity means and how locking takes place with tables joined by the referential integrity rule. In addition, the note explains how inserting/updating/deleting one table can cause another table to get 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.
Example of Referential Integrity
SQL> create table EMP (deptno number(2) constraint fk_deptno references dept(deptno), ename varchar2(20));
DEPT is the parent table having primary key constraint 'pk_dept' on the 'deptno' column.
EMP is the child table having 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.
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
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, below is a description of V$LOCK.
KADDR Address of lock
SID Identifier for session holding or acquiring the lock
TYPE Type of user or system lock
Examples:
TM - DML enqueue
TX - Transaction enqueue
For a full list see: Oracle Enqueue Names
ID1 Lock identifier #1 (depends on type)
ID2 Lock identifier #2 (depends on type)
LMODE Lock mode in which the session holds the lock:
0 - none
1 - null (NULL)
2 - row-S (SS)
3 - row-X (SX)
4 - share (S)
5 - S/Row-X (SSX)
6 - exclusive (X)
REQUEST Lock mode in which the process requests the lock:
0 - none
1 - null (NULL)
2 - row-S (SS)
3 - row-X (SX)
4 - share (S)
5 - S/Row-X (SSX)
6 - exclusive (X)
CTIME Time since current mode was granted
BLOCK Value of either 0 or 1, depending on whether blocker or waiter
Note:
In all the examples given below, the object_id for the DEPT and the EMP tables are 374762 and 374764 respectively.
For TM enqueue, ID1 column from the V$LOCK corresponds to the OBJECT_ID column from the DBA_OBJECTS view:
select object_id,object_name from dba_objects where object_name in ('EMP','DEPT')
and wner= 'SCOTT';
OBJECT_ID OBJECT_NAME
---------- -------------------------
13033 DEPT
13035 EMP
WITHOUT INDEXES
1. Insert/delete/update into child table
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'));
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> 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
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 exclusive lock is needed on the child table.(LMODE=2)
Changes in behaviour 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.
When indexes are added on foreign keys columns of the child table, Oracle will only require Mode 3 Row-X (SX) locks on the modified table (parent or child) in Oracle 8.1.7 and 9.0.1. In Oracle9.2 onwards, 'mode 2 Row-S (SS)' locks are required on the linked table (child table when modifying the parent table, or parent table when modifying the child table).
When indexes are not present on child table foreign keys columns, on top of the previous locking situation Oracle will 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 help 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.
References
NOTE:1039297.6 - Script. To list Foreign Key Constraints
NOTE:15476.1 - FAQ: Detecting and Resolving Locking Conflicts and Ora-00060 errors
|
|
- Oracle Database Products > Oracle Database > Oracle Database > Oracle Server - Enterprise Edition > RDBMS > Locking Issues
- Oracle Database Products > Oracle Database > Oracle Database > Oracle Server - Personal Edition > Generic RDBMS > Locking Issues
- Oracle Database Products > Oracle Database > Oracle Database > Oracle Server - Standard Edition > Generic RDBMS > Locking Issues
|
來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/17252115/viewspace-742165/,如需轉載,請註明出處,否則將追究法律責任。
相關文章
- Referential Integrity (324)
- Subresource Integrity 介紹
- How Oracle Locking WorksOracle
- Modes of Locking (334)
- Script to Check for Foreign Key Locking Issues [ID 1019527.6]
- 淺談mysql innodb lockingMySql
- Explicit (Manual) Data Locking (364)
- 安裝grid時,檢查task resolv.conf integrity警告
- Overview of Locking Mechanisms (310)View
- 淺析query_rewrite_integrity引數
- OCR integrity results are inconsistent amongst the nodes.
- MySQL 5.5 -- Metadata Locking Within TransactionsMySql
- oracle RAC Task resolv.conf IntegrityOracle
- 如何查詢 library locking 問題
- Mysql next-locking 間隙鎖MySql
- Integrity plus for mac(網站連結檢查工具)Mac網站
- Oracle 19c Concepts(05):Data IntegrityOracle
- FAQ: Detecting and Resolving Locking Conflicts and Ora-00060 errors [ID 15476.1]Error
- /usr/bin/xauth: error in locking authority file錯誤Error
- mac網站連結檢測軟體——Integrity for macMac網站
- 清理網站死連結優化工具:Integrity Pro for Mac網站優化Mac
- Default Value Insertion and Integrity Constraint Checking (164)AI
- Default Locking for INSERT, UPDATE, DELETE, and SELECT ... FOR UPDATE (351)delete
- 使用 Play Integrity API 來保護您的應用和遊戲API遊戲
- 完整性約束的型別(Types of Integrity Constraints)型別AI
- PostgreSQL 原始碼解讀(223)- Locks(Fast Path Locking)SQL原始碼AST
- MySQL innodb 的間隙鎖定(next-key locking)MySql
- MySQL 8.0 Reference Manual(讀書筆記63節--InnoDB Locking)MySql筆記
- Integrity plus for mac網站死鏈檢查最佳化軟體Mac網站
- PostgreSQL 原始碼解讀(222)- Locks(Lock Manager Internal Locking)SQL原始碼
- singleton模式的一種變體:double-checked locking (轉)模式
- ES叢集搭建問題:memory locking requested for elasticsearch process but memory is not lockedElasticsearch
- 漏洞反饋,使用者授權報錯Integrity constraint violationAI
- 最新Integrity plus for Mac,網站死連結檢查最佳化工具Mac網站
- 【微信公眾號】【深入解析】DRM和read-mostly locking
- Oracle Database Transactions and Locking Revealed.pdf 英文原版 免費下載OracleDatabase
- [重慶思莊每日技術分享]-安裝GI時報錯“Oracle Restart Integrity”OracleREST
- Linux記憶體子系統——Locking Pages(記憶體鎖定)Linux記憶體