问题：
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(
行级共享锁
);
对父表做插入和做更新操作对子表所加的锁是有影响的
，不过这里只对在外键键上未加索引的表有影响，而对在外键键上加索引的表没有影响。
扫一扫
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
