主要描述外鍵未加索引和加索引的區別：
首先建相關表：
create table dept (dept_id number(2), name varchar2(20));
alter table dept add (constraint pk_dept primary key (dept_id));
--在外鍵上沒有建索引的子表
CREATE TABLE emp
(emp_id number(10),
name varchar2(20) ,
dept_id number(2) CONSTRAINT fk_emp
references dept(dept_id)
ON DELETE CASCADE
);
alter table emp add (constraint pk_emp primary key (emp_id));
insert into dept values (10,'ACCOUNTING');
insert into dept values (11,'SALES');
insert into dept values (12,'RESEARCH');
insert into dept values (14,'TT');
insert into emp values (2001,'Jason',10);
insert into emp values (2002,'Mike',11);
insert into emp values (2003,'Green',12);
insert into emp values (2004,'Grey',10);
外鍵未加索引情況，實驗如下：
實驗1、
Session1：對子表進行插入操作：
insert into emp values(2005,'dai',14);
session0：此時加鎖的信息都是如下：
SQL> @c:\showlock;
O_NAME SID LOCK_TYPE OBJECT_NAME XIDUSN XIDSLOT XIDSQN
---------- ---------- -------------------- --------------- ---------- ---------- ----------
DAIMIN 142 Row share DEPT 11 12 503
DAIMIN 142 Row Exclusive EMP 11 12 503
SQL> @c:\showalllock;
SID TYPE ID1 ID2 LOCK_TYPE REQUEST CTIME BLOCK
---------- ---- ---------- ---------- -------------------- ---------- ---------- ----------
142 TM 55606 0 Row share 0 6 0
142 TX 720908 503 Exclusive 0 6 0
142 TM 55612 0 Row Exclusive 0 6 0
分析：對子表進行插入操作時，此時盡管只是對子表操作，但是需要驗證被插入的記錄中的外鍵值是否在父表中是否存在，不存在，則會報錯；存在的話，則允許插入。此時需要對父表加RS鎖，鎖住外鍵值所對應父表被引用鍵值的行；除了對父表加RS鎖之外，對子表還要加RX鎖和TX鎖。
實驗2、
Session2：對子表EMP進行更新操作，并且所更新的行在子表中實際不存在或者存在：
update emp set name=name where dept_id=15；
或者update emp set name=name where dept_id=11；
Session0：此時加鎖的信息都是如下：
SQL> @c:\showlock;
O_NAME SID LOCK_TYPE OBJECT_NAME XIDUSN XIDSLOT XIDSQN
---------- ---------- -------------------- --------------- ---------- ---------- ----------
DAIMIN 142 Row Exclusive EMP 14 28 499
SQL> @c:\showalllock;
SID TYPE ID1 ID2 LOCK_TYPE REQUEST CTIME BLOCK
---------- ---- ---------- ---------- -------------------- ---------- ---------- ----------
142 TX 917532 499 Exclusive 0 87 0
142 TM 55612 0 Row Exclusive 0 87 0
分析：僅對子表中除外鍵以外的值進行更新，此時不需要牽涉到父表，所以此時的更新操作不需要對父表加任何的鎖，只需要對子表加RX鎖和TX鎖。
實驗3、
Session3：對子表進行刪除操作，并且由刪除條件查詢出來的記錄為空或者不為空時：
delete from emp where 1=0；
或者 delete from emp ；
Session0：此時加鎖的信息都是如下：
SQL> @c:\showlock;
O_NAME SID LOCK_TYPE OBJECT_NAME XIDUSN XIDSLOT XIDSQN
---------- ---------- -------------------- --------------- ---------- ---------- ----------
DAIMIN 142 Row share DEPT 17 28 502
DAIMIN 142 Row Exclusive EMP 17 28 502
SQL> @c:\showalllock;
SID TYPE ID1 ID2 LOCK_TYPE REQUEST CTIME BLOCK
---------- ---- ---------- ---------- -------------------- ---------- ---------- ----------
142 TM 55606 0 Row share 0 6 0—DEPT表上的RS鎖
142 TX 1114140 502 Exclusive 0 6 0 --EMP表上的TX鎖
142 TM 55612 0 Row Exclusive 0 6 0 –EMP表上的RX鎖
注：BLOCK=1 ：表示鎖被阻塞；BLOCK=0，表示未阻塞。
分析：對子表進行刪除操作，也需要對父表加RS鎖，猜想是因為需要驗證被刪除的這些記錄中的外鍵值是否在父表還存在，所以需要在父表中加RS鎖。然后對子表本身需要加RX鎖和TX鎖。
實驗4、
Session4：更新子表的外鍵列：
update emp set dept_id= 12 where dept_id=11；
Session0：此時加鎖的信息都是如下：
SQL> @c:\showlock;
O_NAME SID LOCK_TYPE OBJECT_NAME XIDUSN XIDSLOT XIDSQN
---------- ---------- -------------------- --------------- ---------- ---------- ----------
DAIMIN 142 Row share DEPT 12 17 661
DAIMIN 142 Row Exclusive EMP 12 17 661
SQL> @c:\showalllock;
SID TYPE ID1 ID2 LOCK_TYPE REQUEST CTIME BLOCK
---------- ---- ---------- ---------- -------------------- ---------- ---------- ----------
142 TM 55606 0 Row share 0 6 0
142 TX 786449 661 Exclusive 0 6 0
142 TM 55612 0 Row Exclusive 0 6 0
分析：和實驗2比較，都是對子表進行更新操作，但是該實驗是對外鍵值進行的更新，牽涉到父表，所以需要對父表DEPT加RS鎖，子表EMP本身需要加RX鎖和TX鎖。
實驗5、
Session5：對父表進行插入操作：
insert into dept values(15,'daimin');
Session0：此時加鎖的信息都是如下：
SQL> @c:\showlock;
O_NAME SID LOCK_TYPE OBJECT_NAME XIDUSN XIDSLOT XIDSQN
---------- ---------- -------------------- --------------- ---------- ---------- ----------
DAIMIN 142 Row Exclusive DEPT 13 36 671
DAIMIN 142 Row share EMP 13 36 671
SQL> @c:\showalllock;
SID TYPE ID1 ID2 LOCK_TYPE REQUEST CTIME BLOCK
---------- ---- ---------- ---------- -------------------- ---------- ---------- ----------
142 TM 55606 0 Row Exclusive 0 6 0
142 TX 852004 671 Exclusive 0 3 0
142 TM 55612 0 Row share 0 6 0
分析：對父表進行插入操作，此時需要對父表DEPT加RX鎖和TX鎖，同時需要對子表EMP加RS鎖。
實驗6、
Session4：對父表DEPT進行更新操作，并且所更新的行在父表中實際不存在或者不存在：
update dept set name=name where dept_id=16；
或者 update dept set name=name where dept_id=12；
Session0：此時加鎖的信息都是如下：
SQL> @c:\showlock;
O_NAME SID LOCK_TYPE OBJECT_NAME XIDUSN XIDSLOT XIDSQN
---------- ---------- -------------------- --------------- ---------- ---------- ----------
DAIMIN 142 Row Exclusive DEPT 15 18 668
SQL> @c:\showalllock;
SID TYPE ID1 ID2 LOCK_TYPE REQUEST CTIME BLOCK
---------- ---- ---------- ---------- -------------------- ---------- ---------- ----------
142 TX 983058 668 Exclusive 0 6 0
142 TM 55606 0 Row Exclusive 0 6 0
分析：僅對父表中的記錄進行更新，此時不需要牽涉到子表，所以此時的更新操作不需要對子表加任何的鎖，只需要對父表加RX鎖和TX鎖。
實驗7、
Session7: 對父表DEPT進行刪除操作，并且所更新的行在父表中實際不存在或者實際存在：
delete from dept where 1=0；
或者 delete from dept where dept_id=12；
Session0：此時加鎖的信息都是如下：
SQL> @c:\showlock;
O_NAME SID LOCK_TYPE OBJECT_NAME XIDUSN XIDSLOT XIDSQN
---------- ---------- -------------------- --------------- ---------- ---------- ----------
DAIMIN 142 Row Exclusive DEPT 15 28 671
DAIMIN 142 Row Exclusive EMP 15 28 671
SQL> @c:\showalllock;
SID TYPE ID1 ID2 LOCK_TYPE REQUEST CTIME BLOCK
---------- ---- ---------- ---------- -------------------- ---------- ---------- ----------
142 TM 55606 0 Row Exclusive 0 6 0
142 TX 983068 671 Exclusive 0 6 0
142 TM 55612 0 Row Exclusive 0 6 0
分析：對父表DEPT與子表EMP都加了RS鎖，并且由于是對DEPT父表進行刪除行記錄，所以為父表加了TX鎖。有點疑問：我在創建子表時使用了ON DELETE CASCADE語句，為什么我在刪除父表的記錄時沒有造成子表的相應記錄刪除呢？猜想應該在子表EMP上也應該有TX鎖。
外鍵加索引情況，實驗如下：
給表EMP的外鍵添加索引如下：
-- Create/Recreate indexes on the Foreign Key
create index EMP_DEPT_ID on EMP (DEPT_ID);
實驗1、
Session1：對子表進行插入操作：
insert into emp values(2005,'dai',14);
session0：此時加鎖的信息都是如下：
SQL> @c:\showlock;
O_NAME SID LOCK_TYPE OBJECT_NAME XIDUSN XIDSLOT XIDSQN
---------- ---------- -------------------- --------------- ---------- ---------- ----------
DAIMIN 142 Row share DEPT 19 47 673
DAIMIN 142 Row Exclusive EMP 19 47 673
SQL> @c:\showalllock;
SID TYPE ID1 ID2 LOCK_TYPE REQUEST CTIME BLOCK
---------- ---- ---------- ---------- -------------------- ---------- ---------- ----------
142 TM 55606 0 Row share 0 6 0
142 TX 1245231 673 Exclusive 0 6 0
142 TM 55612 0 Row Exclusive 0 6 0
分析：與外鍵沒有加索引的情況中的實驗1所加的鎖一樣，沒有區別。
實驗2、
Session2：對子表EMP進行更新操作，并且所更新的行在子表中實際不存在或者存在：
update emp set name=name where dept_id=15；
或者update emp set name=name where dept_id=11；
Session0：此時加鎖的信息都是如下：
SQL> @c:\showlock;
O_NAME SID LOCK_TYPE OBJECT_NAME XIDUSN XIDSLOT XIDSQN
---------- ---------- -------------------- --------------- ---------- ---------- ----------
DAIMIN 142 Row Exclusive EMP 13 13 676
SQL> @c:\showalllock;
SID TYPE ID1 ID2 LOCK_TYPE REQUEST CTIME BLOCK
---------- ---- ---------- ---------- -------------------- ---------- ---------- ----------
142 TX 851981 676 Exclusive 0 6 0
142 TM 55612 0 Row Exclusive 0 6 0
分析：與外鍵沒有加索引的情況中的實驗2所加的鎖一樣，沒有區別。
實驗3、
Session3：對子表進行刪除操作，并且由刪除條件查詢出來的記錄為空或者不為空時：
delete from emp where 1=0；
或者 delete from emp ；
Session0：此時加鎖的信息都是如下：
SQL> @c:\showlock;
O_NAME SID LOCK_TYPE OBJECT_NAME XIDUSN XIDSLOT XIDSQN
---------- ---------- -------------------- --------------- ---------- ---------- ----------
DAIMIN 142 Row share DEPT 17 28 502
DAIMIN 142 Row Exclusive EMP 17 28 502
SQL> @c:\showalllock;
SID TYPE ID1 ID2 LOCK_TYPE REQUEST CTIME BLOCK
---------- ---- ---------- ---------- -------------------- ---------- ---------- ----------
142 TM 55606 0 Row share 0 6 0—DEPT表上的RS鎖
142 TX 1114140 502 Exclusive 0 6 0 --EMP表上的TX鎖
142 TM 55612 0 Row Exclusive 0 6 0 –EMP表上的RX鎖
注：BLOCK=1 ：表示鎖被阻塞；BLOCK=0，表示未阻塞。
分析：與外鍵沒有加索引的情況中的實驗3所加的鎖一樣，沒有區別。
實驗4、
Session4：更新子表的外鍵列：
update emp set dept_id= 12 where dept_id=11；
Session0：此時加鎖的信息都是如下：
SQL> @c:\showlock;
O_NAME SID LOCK_TYPE OBJECT_NAME XIDUSN XIDSLOT XIDSQN
---------- ---------- -------------------- --------------- ---------- ---------- ----------
DAIMIN 142 Row share DEPT 12 17 661
DAIMIN 142 Row Exclusive EMP 12 17 661
SQL> @c:\showalllock;
SID TYPE ID1 ID2 LOCK_TYPE REQUEST CTIME BLOCK
---------- ---- ---------- ---------- -------------------- ---------- ---------- ----------
142 TM 55606 0 Row share 0 6 0
142 TX 786449 661 Exclusive 0 6 0
142 TM 55612 0 Row Exclusive 0 6 0
分析：與外鍵沒有加索引的情況中的實驗4所加的鎖一樣，沒有區別。
實驗5、
Session5：對父表進行插入操作：
insert into dept values(15,'daimin');
Session0：此時加鎖的信息都是如下：
SQL> @c:\showlock;
O_NAME SID LOCK_TYPE OBJECT_NAME XIDUSN XIDSLOT XIDSQN
---------- ---------- -------------------- --------------- ---------- ---------- ----------
DAIMIN 142 Row Exclusive DEPT 13 36 671
DAIMIN 142 Row share EMP 13 36 671
SQL> @c:\showalllock;
SID TYPE ID1 ID2 LOCK_TYPE REQUEST CTIME BLOCK
---------- ---- ---------- ---------- -------------------- ---------- ---------- ----------
142 TM 55606 0 Row Exclusive 0 6 0
142 TX 852004 671 Exclusive 0 3 0
142 TM 55612 0 Row share 0 6 0
分析：與外鍵沒有加索引的情況中的實驗5所加的鎖一樣，沒有區別。
實驗6、
Session4：對父表DEPT進行更新操作，并且所更新的行在父表中實際不存在或者不存在：
update dept set name=name where dept_id=16；
或者 update dept set name=name where dept_id=12；
Session0：此時加鎖的信息都是如下：
SQL> @c:\showlock;
O_NAME SID LOCK_TYPE OBJECT_NAME XIDUSN XIDSLOT XIDSQN
---------- ---------- -------------------- --------------- ---------- ---------- ----------
DAIMIN 142 Row Exclusive DEPT 15 18 668
SQL> @c:\showalllock;
SID TYPE ID1 ID2 LOCK_TYPE REQUEST CTIME BLOCK
---------- ---- ---------- ---------- -------------------- ---------- ---------- ----------
142 TX 983058 668 Exclusive 0 6 0
142 TM 55606 0 Row Exclusive 0 6 0
分析：與外鍵沒有加索引的情況中的實驗6所加的鎖一樣，沒有區別。
實驗7、
Session7: 對父表DEPT進行刪除操作，并且所更新的行在父表中實際不存在：
delete from dept where 1=0；
Session0：此時加鎖的信息都是如下：
SQL> @c:\showlock;
O_NAME SID LOCK_TYPE OBJECT_NAME XIDUSN XIDSLOT XIDSQN
---------- ---------- -------------------- --------------- ---------- ---------- ----------
DAIMIN 142 Row Exclusive DEPT 13 38 676
DAIMIN 142 Row share EMP 13 38 676
SQL> @c:\showalllock;
SID TYPE ID1 ID2 LOCK_TYPE REQUEST CTIME BLOCK
---------- ---- ---------- ---------- -------------------- ---------- ---------- ----------
142 TM 55606 0 Row Exclusive 0 5 0
142 TX 852006 676 Exclusive 0 5 0
142 TM 55612 0 Row share 0 5 0
分析：猜測可能是由于在父表中根本沒有查找到記錄，所以就在子表中不需要對子表EMP行進行刪除，所以對子表EMP加的是RS鎖。這里和外鍵沒有加索引的情況中的實驗7所加的鎖一樣有鎖不一樣，在子表中所加的鎖是RS鎖，而不是RX鎖。
實驗8、
Session7: 對父表DEPT進行刪除操作，并且所更新的行在父表中實際存在：
delete from dept where dept_id=12；
Session0：此時加鎖的信息都是如下：
SQL> @c:\showlock;
O_NAME SID LOCK_TYPE OBJECT_NAME XIDUSN XIDSLOT XIDSQN
---------- ---------- -------------------- --------------- ---------- ---------- ----------
DAIMIN 142 Row Exclusive DEPT 15 28 671
DAIMIN 142 Row Exclusive EMP 15 28 671
SQL> @c:\showalllock;
SID TYPE ID1 ID2 LOCK_TYPE REQUEST CTIME BLOCK
---------- ---- ---------- ---------- -------------------- ---------- ---------- ----------
142 TM 55606 0 Row Exclusive 0 6 0
142 TX 983068 671 Exclusive 0 6 0
142 TM 55612 0 Row Exclusive 0 6 0
分析：與外鍵沒有加索引的情況中的實驗7所加的鎖一樣，沒有區別。