学步园

       在学习子表的外键列上是否添加索引这一块知识时，建议是要添加索引的，但是这里我想记录的是许多资料都提到如果不添加索引，在对主表进行UPDATE、DELETE操作时，会对子表加个全表锁－TM锁，但是没有提到是哪种TM锁，后来经过搜索资料后，并通过自己的实验，发现是添加的TM LOCK TYPE=4  Share类型的共享锁。

       这里提一下这个TM LOCK TYPE 4类型的锁只会作用在UPDATE、DELETE语句对主表进行操作时，执行完毕后，子表上的这个Share锁就会消失，而不是作用在整个事务上的锁，所以如果不进行特定的测试，是看不到该锁的，有请多同学对这一块理解的有误，以为是作用在整个事务上的，即以为UPDATE、DELETE语句执行完后，会在V$LOCK中看到这个TM LOCK TYPE 4类型的锁，但是当UPDATE、DELETE语句执行完后，子表上的这个锁就消失了。

      并且在主表上的INSERT语句是不会对子表产生该锁的。

      下面把如何看到该锁的实验贴出来，分享给大家：

实验环境：

数据库版本：11.2.0.1 

操作系统：Windows

下面是实验记录可以看到该锁：

SESSION 1：   select sid from v$mystat where rownum=1;    SID=17

SESSION 2：   select sid from v$mystat where rownum=1;    SID=22

使用SCOTT用户下的表： 主表：DEPT （主键DEPTNO，OBJECT_ID=79188）   子表：EMP（外键DEPTNO，OBJECT_ID=80523），这里同学们也可以自己来创建主表和子表。

在子表EMP的DEPTNO列上没有创建索引时：

SESSION 1:

[sql] view
plaincopy

1. 删除子表上的一行，其实做这步的目的就是为了一会阻塞主表上的这个Share锁，这一步是关键，否则会看不到这个Share锁，原因上面已经说过了：  
2. SQL> delete from emp where rownum=1;  
3.   
4. 1 row deleted.  

查看会话中目前已经有的锁，当对子表进行INSERT、UPDATE、DELETE操作时，也会对主表加个3级的Row-X (SX)行级排它锁

[sql] view
plaincopy

1. SQL> select * from v$lock where sid in (17,22) and type in ('TM','TX') order by sid,type;  
2.   
3. ADDR     KADDR           SID TY        ID1        ID2      LMODE    REQUEST      CTIME      BLOCK  
4. -------- -------- ---------- -- ---------- ---------- ---------- ---------- ---------- ----------  
5. 0D79C52C 0D79C55C         17 TM      79188          0          3          0          6          0  
6. 0D79C52C 0D79C55C         17 TM      80523          0          3          0          6          0  
7. 2DE15208 2DE15248         17 TX     196614       3830          6          0          6          0  

SESSION 2:

[sql] view
plaincopy

1. 我们对主表DELETE或UPDATE一条记录，这里要更新或删除子表中没有使用的记录：  
2. SQL> delete from dept where deptno=50;  
3. 你会发现上现这条语句hang住了  

SESSION 1:

[sql] view
plaincopy

1. 查看锁信息，你会发现22号会话想对80523对象(EMP表)加一个TYPE 4类型的锁，根据TM锁兼容的矩阵图，可以了解到3号与4号锁不兼容，所以22号会话被阻塞，需要等待：  
2. SQL> select * from v$lock where sid in (17,22) and type in ('TM','TX') order by sid,type;  
3.   
4.   
5. ADDR     KADDR           SID TY        ID1        ID2      LMODE    REQUEST      CTIME      BLOCK  
6. -------- -------- ---------- -- ---------- ---------- ---------- ---------- ---------- ----------  
7. 0D79C52C 0D79C55C         17 TM      79188          0          3          0          6          0  
8. 0D79C52C 0D79C55C         17 TM      80523          0          3          0          6          1  
9. 2DE15208 2DE15248         17 TX     196614       3830          6          0          6          0  
10. 0D79C52C 0D79C55C         22 TM      80523          0          0          4          3          0  
11. 0D79C52C 0D79C55C         22 TM      79188          0          3          0          3          0  
12.   
13. SQL> rollback;  
14.   
15. Rollback complete.  
16. 回滚后22话会话操作成功，再次查看锁信息，你会发现子表的4号锁已经没有了，所以验证了上面说的，只在语句操作期间加锁，操作完成后，就没了，不作用在整个事务上：  
17. SQL> select * from v$lock where sid in (17,22) and type in ('TM','TX') order by sid,type;  
18.   
19.   
20. ADDR     KADDR           SID TY        ID1        ID2      LMODE    REQUEST      CTIME      BLOCK  
21. -------- -------- ---------- -- ---------- ---------- ---------- ---------- ---------- ----------  
22. 0D79C52C 0D79C55C         22 TM      79188          0          3          0        724          0  
23. 2DDE31F4 2DDE3234         22 TX     589836       3809          6          0         47          0  

       上面我们还说过INSERT对主表操作，是不会对子表加锁的，可以按照上面的操作来一次，这里就不在演示，如果添加索引后，就不会发生这种阻塞，请自行验证。

       再提一句，如果子表不能被加上类型4的锁，即上面SESSION 2被阻塞后，再有其它会话来更新子表（UPDATE/DELETE）也会被HANG住。

附上TM锁兼容矩阵图：