FK+INDEX+LOCK的关系实验

最新推荐文章于 2025-07-02 23:52:11 发布

原创最新推荐文章于 2025-07-02 23:52:11 发布 · 924 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#table #insert #transactions #locking #object #delete

本文详细解析了数据库中父表与子表在进行DML操作时的锁机制，特别是针对外键与索引的影响。通过具体实验，展示了不同情况下锁的模式与使用情况，揭示了索引对外键锁机制的优化作用。

问题：

1、父表和子表（之间存在外键的关系）在进行一些DML操作时锁机制是如何运做的？

2、如果子表的外键上有没有索引，会对锁机制的运做会有什么影响？

3、FK+INDEX+LOCK的关系实验如何？

简要介绍下关于一些系统视图所存储的信息和锁的模式：

表1 Oracle的TM锁类型

锁模式锁描述解释 SQL操作

0 none

1 NULL 空 Select

2 SS(Row-S) 行级共享锁，其他对象只能查询这些数据行 Select for update、Lock for update、Lock row share

3 SX(Row-X) 行级排它锁，在提交前不允许做DML操作 Insert、Update、Delete、Lock row share

4 S(Share) 共享锁 Create index、Lock share

5 SSX(S/Row-X) 共享行级排它锁 Lock share row exclusive

6 X(Exclusive) 排它锁 Alter table、Drop able、Drop index、Truncate table 、Lock exclusive

表2 数据字典视图说明

视图名描述主要字段说明

v$session 查询会话的信息和锁的信息。 sid,serial#：表示会话信息。

program：表示会话的应用程序信息。

row_wait_obj#：表示等待的对象。

和dba_objects中的object_id相对应。

v$session_wait 查询等待的会话信息。 sid：表示持有锁的会话信息。

Seconds_in_wait：表示等待持续的时间信息

Event：表示会话等待的事件。

v$lock 列出系统中的所有的锁。 Sid：表示持有锁的会话信息。

Type：表示锁的类型。值包括TM和TX等

ID1：表示锁的对象标识。

lmode,request：表示会话等待的锁模式的信息。用数字0－6表示，和表1相对应。

dba_locks 对v$lock的格式化视图。 Session_id：和v$lock中的Sid对应。

Lock_type：和v$lock中的type对应。

Lock_ID1：和v$lock中的ID1对应。

Mode_held,mode_requested：和v$lock中的lmode,request相对应。

v$locked_object 只包含DML的锁信息，包括回滚段和会话信息。

Xidusn,xidslot,xidsqn：表示回滚段信息。和v$transaction相关联。

Object_id：表示被锁对象标识。

Session_id：表示持有锁的会话信息。

Locked_mode：表示会话等待的锁模式的信息，和v$lock中的lmode一致。

SQL> select * from v$version;

BANNER

----------------------------------------------------------------

Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod

PL/SQL Release 10.2.0.1.0 - Production

CORE 10.2.0.1.0 Production

TNS for 32-bit Windows: Version 10.2.0.1.0 - Production

NLSRTL Version 10.2.0.1.0 - Production

WindowXP+Oracle Release 10.2.0.1.0

建父子表，并且建立相应的外键，主键和索引

--父表

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

);

--在外键上有建索引的子表

CREATE TABLE emp1

(emp_id number(10),

name varchar2(20) ,

dept_id number(2) CONSTRAINT fk_emp1

references dept(dept_id)

ON DELETE CASCADE

);

-- Create/Recreate indexes on the Foreign Key

create index EMP1_DEPT_ID on EMP1 (DEPT_ID);

alter table emp add (constraint pk_emp primary key (emp_id));

alter table emp1 add (constraint pk_emp1 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);

--insert into emp values (2005,'daimin',14);

insert into emp1 values (2001,'Jason',10);

insert into emp1 values (2002,'Mike',11);

insert into emp1 values (2003,'Green',12);

insert into emp1 values (2004,'Grey',10);

--insert into emp1 values (2005,'daimin',14);

原文：

No Index on the Foreign Key

Figure 21-8 illustrates the locking mechanisms used by Oracle when no index is defined on the foreign key and when rows are being updated or

deleted in the parent table. Inserts into the parent table do not require any locks on the child table.

Unindexed foreign keys cause DML on the primary key to get a share row exclusive table lock (also sometimes called a share-subexclusive table

lock, SSX) on the foreign key table. This prevents DML on the table by other transactions. The SSX lock is released immediately after it is

obtained. If multiple primary keys are updated or deleted, the lock is obtained and released once for each row.

Note:

Indexed foreign keys only cause a row share table lock (also sometimes called a subshare table lock, SS). This prevents other transactions

from exclusive locking the whole table, but it does not block DML on the parent or the child table.

测试：

1、"Inserts into the parent table do not require any locks on the child table.";

测试1、

insert into dept values (15,'xx');

查看锁使用情况如下：

SQL> select a.sid, a.type, a.LMODE, b.object_name

2 from v$lock a, dba_objects b

3 where a.id1 = b.object_id;

SID TYPE LMODE OBJECT_NAME

---------- ---- ---------- --------------------------------------------------------------------------------

164 TS 3 I_OBJ#

167 MR 4 I_OBJ#

165 RS 2 PROXY_ROLE_DATA$

167 MR 4 I_TS#

167 MR 4 C_TS#

167 MR 4 CLU$

167 MR 4 C_FILE#_BLOCK#

167 MR 4 C_OBJ#

167 MR 4 TAB$

165 XR 1 TAB$

167 MR 4 I_NTAB3

144 TO 3 PLAN_TABLE$

142 TO 3 PLAN_TABLE$

143 TM 3 DEPT

143 TM 2 EMP1

143 TM 2 EMP

16 rows selected

DEPT(父表)使用了锁类型是TM锁，锁模式是3(行级排它锁);

EMP1表(带有外键索引的子表)使用了锁类型是TM锁，锁模式是2(行级共享锁);

EMP表(不带有外键索引的子表)使用了锁类型是TM锁，锁模式是2(行级共享锁);

从查询结果来看尽管父表做插入操作，但是子表也使用了锁，这样与文档讲的是不是相矛盾？还是我理解错误

2、"Unindexed foreign keys cause DML on the primary key to get a share row exclusive table lock (also sometimes called a share-subexclusive table

lock, SSX) on the foreign key table";

测试2、

delete from dept where dept_id=12;

查看锁使用情况如下：

SQL> select a.sid, a.type, a.LMODE, b.object_name

2 from v$lock a, dba_objects b

3 where a.id1 = b.object_id;

SID TYPE LMODE OBJECT_NAME

---------- ---- ---------- --------------------------------------------------------------------------------

164 TS 3 I_OBJ#

167 MR 4 I_OBJ#

165 RS 2 PROXY_ROLE_DATA$

167 MR 4 I_TS#

167 MR 4 C_TS#

167 MR 4 CLU$

167 MR 4 C_FILE#_BLOCK#

167 MR 4 C_OBJ#

167 MR 4 TAB$

165 XR 1 TAB$

167 MR 4 I_NTAB3

144 TO 3 PLAN_TABLE$

142 TO 3 PLAN_TABLE$

143 TM 3 DEPT

143 TM 3 EMP1

143 TM 3 EMP

16 rows selected

DEPT(父表)使用了锁类型是TM锁，锁模式是3(行级排它锁);

EMP1表(带有外键索引的子表)使用了锁类型是TM锁，锁模式是3(行级排它锁);

EMP表(不带有外键索引的子表)使用了锁类型是TM锁，锁模式是3(行级排它锁);

从锁信息中看出父表DEPT确实使用了行级排它锁，符合文档所说。

3、"This prevents DML on the table by other transactions";

测试3、

另打开一个会话，在测试2的基础上对DEPT(父表)进行更新操作如下：

update DEPT

set DEPT_ID=15

where DEPT_ID=12;

更新处于等待状态

另打开一个会话，在测试2的基础上对DEPT(父表)进行更新操作如下：

update DEPT

set DEPT_ID=15

where DEPT_ID=11;

也等待，确实阻止了其他事务对该行的修改操作。

Index on the Foreign Key

Figure 21-9 illustrates the locking mechanisms used when an index is defined on the foreign key, and new rows are inserted, updated, or deleted

in the child table.

Indexed foreign keys cause a row share table lock (also sometimes called a subshare table lock, SS). This prevents other transactions from

exclusive locking the whole table, but it does not block DML on the parent or the child table.

This situation is preferable if there is any update or delete activity on the parent table while update activity is taking place on the child

table. 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 on the indexes of the child table to clear.

If the child table specifies ON DELETE CASCADE, then deletes from the parent table can result in deletes from the child table. In this case,

waiting and locking rules are the same as if you deleted yourself from the child table after performing the delete from the parent table.

测试1：

1、"Indexed foreign keys cause a row share table lock";

insert into emp1 values (2005,'Green',10);

查看锁使用情况如下：

SQL> select a.sid, a.type, a.LMODE, b.object_name

2 from v$lock a, dba_objects b

3 where a.id1 = b.object_id;

SID TYPE LMODE OBJECT_NAME

---------- ---- ---------- --------------------------------------------------------------------------------

164 TS 3 I_OBJ#

167 MR 4 I_OBJ#

165 RS 2 PROXY_ROLE_DATA$

167 MR 4 I_TS#

167 MR 4 C_TS#

167 MR 4 CLU$

167 MR 4 C_FILE#_BLOCK#

167 MR 4 C_OBJ#

167 MR 4 TAB$

165 XR 1 TAB$

167 MR 4 I_NTAB3

144 TO 3 PLAN_TABLE$

142 TO 3 PLAN_TABLE$

143 TM 2 DEPT

143 TM 3 EMP1

15 rows selected

DEPT(父表)使用了锁类型是TM锁，锁模式是2(行级共享锁);

EMP1表(带有外键索引的子表)使用了锁类型是TM锁，锁模式是3(行级排它锁);

update emp1

set emp1.dept_id=12

where emp1.dept_id=11;

查看锁使用情况如下：

SQL> select a.sid, a.type, a.LMODE, b.object_name

2 from v$lock a, dba_objects b

3 where a.id1 = b.object_id;

SID TYPE LMODE OBJECT_NAME

---------- ---- ---------- --------------------------------------------------------------------------------

164 TS 3 I_OBJ#

167 MR 4 I_OBJ#

165 RS 2 PROXY_ROLE_DATA$

167 MR 4 I_TS#

167 MR 4 C_TS#

167 MR 4 CLU$

167 MR 4 C_FILE#_BLOCK#

167 MR 4 C_OBJ#

167 MR 4 TAB$

165 XR 1 TAB$

167 MR 4 I_NTAB3

144 TO 3 PLAN_TABLE$

142 TO 3 PLAN_TABLE$

143 TM 2 DEPT

143 TM 3 EMP1

15 rows selected

DEPT(父表)使用了锁类型是TM锁，锁模式是2(行级共享锁);

EMP1表(带有外键索引的子表)使用了锁类型是TM锁，锁模式是3(行级排它锁);

有个疑问：这里所讲的"Indexed foreign keys cause a row share table lock"从测试角度来看好象是指对父表来说的，子表还是使用的是行级排它锁

2、"This prevents other transactions from exclusive locking the whole table, but it does not block DML on the parent or the child table.";

测试2

在测试1的基础上：

另开两个会话，对父表和子表分别做DML操作如下：

对子表做DML操作：

update emp1

set emp1.dept_id=11

where emp1.dept_id=10;

查看锁使用情况如下：

SQL> select a.sid, a.type, a.LMODE, b.object_name

2 from v$lock a, dba_objects b

3 where a.id1 = b.object_id;

SID TYPE LMODE OBJECT_NAME

---------- ---- ---------- --------------------------------------------------------------------------------

164 TS 3 I_OBJ#

167 MR 4 I_OBJ#

165 RS 2 PROXY_ROLE_DATA$

167 MR 4 I_TS#

167 MR 4 C_TS#

167 MR 4 CLU$

167 MR 4 C_FILE#_BLOCK#

167 MR 4 C_OBJ#

167 MR 4 TAB$

165 XR 1 TAB$

167 MR 4 I_NTAB3

144 TO 3 PLAN_TABLE$

142 TO 3 PLAN_TABLE$

133 TM 2 DEPT

143 TM 2 DEPT

133 TM 3 EMP1

143 TM 3 EMP1

17 rows selected

"Indexed foreign keys cause a row share table lock"从测试角度来看好象是指对父表来说的,按照这样推理，看出当对子表做DML操作时，仍然可以继续进行，

而不受阻塞，并且对父表所加的锁仍然是行级共享锁；

对父表做DML操作：

update dept

set dept.dept_id=15

where dept.dept_id=14

查看锁使用情况如下：同上

"Indexed foreign keys cause a row share table lock"从测试角度来看好象是指对父表来说的,按照这样推理，看出当对父表做DML操作时，仍然可以继续进行，

而不受阻塞，并且对父表所加的锁仍然是行级共享锁；

update dept

set dept.dept_id=15

where dept.dept_id=10

报错：ORA-02292:违反完整性约束条件(DAIMIN.FK_EMP)-已找到子记录

报错原因是dept_id=10的在子表中还有子记录，不允许更新

3、"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 on the indexes of the child table to clear.";

测试3、

delete from dept where dept_id=14;

查看锁使用情况如下：

SQL> select a.sid, a.type, a.LMODE, b.object_name

2 from v$lock a, dba_objects b

3 where a.id1 = b.object_id;

SID TYPE LMODE OBJECT_NAME

---------- ---- ---------- --------------------------------------------------------------------------------

164 TS 3 I_OBJ#

167 MR 4 I_OBJ#

165 RS 2 PROXY_ROLE_DATA$

167 MR 4 I_TS#

167 MR 4 C_TS#

167 MR 4 CLU$

167 MR 4 C_FILE#_BLOCK#

167 MR 4 C_OBJ#

167 MR 4 TAB$

165 XR 1 TAB$

167 MR 4 I_NTAB3

144 TO 3 PLAN_TABLE$

142 TO 3 PLAN_TABLE$

143 TM 3 DEPT

143 TM 2 EMP1

143 TM 3 EMP

16 rows selected

DEPT(父表)使用了锁类型是TM锁，锁模式是3(行级排它锁);

EMP1表(带有外键索引的子表)使用了锁类型是TM锁，锁模式是2(行级共享锁);

EMP表(不带有外键索引的子表)使用了锁类型是TM锁，锁模式是3(行级排它锁);

该结果与No Index on the Foreign Key 测试的结果不一样，对EMP1表和EMP表的所加的锁模式不一样。

并且有疑问：对父表的修改还是有对子表加了锁的，这个由是如何解释呢？

insert into dept values (15,'XX');

查看锁使用情况如下:

SQL> select a.sid, a.type, a.LMODE, b.object_name

2 from v$lock a, dba_objects b

3 where a.id1 = b.object_id;

SID TYPE LMODE OBJECT_NAME

---------- ---- ---------- --------------------------------------------------------------------------------

164 TS 3 I_OBJ#

167 MR 4 I_OBJ#

165 RS 2 PROXY_ROLE_DATA$

167 MR 4 I_TS#

167 MR 4 C_TS#

167 MR 4 CLU$

167 MR 4 C_FILE#_BLOCK#

167 MR 4 C_OBJ#

167 MR 4 TAB$

165 XR 1 TAB$

167 MR 4 I_NTAB3

144 TO 3 PLAN_TABLE$

142 TO 3 PLAN_TABLE$

143 TM 3 DEPT

143 TM 2 EMP1

143 TM 2 EMP

16 rows selected

DEPT(父表)使用了锁类型是TM锁，锁模式是3(行级排它锁);

EMP1表(带有外键索引的子表)使用了锁类型是TM锁，锁模式是2(行级共享锁);

EMP表(不带有外键索引的子表)使用了锁类型是TM锁，锁模式是2(行级共享锁);

对父表做插入和做更新操作对子表所加的锁是有影响的，不过这里只对在外键键上未加索引的表有影响，而对在外键键上加索引的表没有影响。