外键未加索引和加索引的区别(一)

外键索引与锁行为

最新推荐文章于 2024-08-28 19:34:48 发布

原创最新推荐文章于 2024-08-28 19:34:48 发布 · 2.6k 阅读

1 ·

CC 4.0 BY-SA版权

文章标签：

#session #sql #insert #object #delete #c

本文通过实验对比分析了在外键未加索引和加索引两种情况下，对父表和子表进行增删改操作时，Oracle数据库中锁的行为差异。

主要描述外键未加索引和加索引的区别：

首先建相关表：

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所加的锁一样，没有区别。