在学习子表的外键列上是否添加索引这一块知识时,建议是要添加索引的,但是这里我想记录的是许多资料都提到如果不添加索引,在对主表进行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:
- 删除子表上的一行,其实做这步的目的就是为了一会阻塞主表上的这个Share锁,这一步是关键,否则会看不到这个Share锁,原因上面已经说过了:
- SQL> delete from emp where rownum=1;
- 1 row deleted.
查看会话中目前已经有的锁,当对子表进行INSERT、UPDATE、DELETE操作时,也会对主表加个3级的Row-X (SX)行级排它锁
- SQL> select * from v$lock where sid in (17,22) and type in ('TM','TX') order by sid,type;
- ADDR KADDR SID TY ID1 ID2 LMODE REQUEST CTIME BLOCK
- -------- -------- ---------- -- ---------- ---------- ---------- ---------- ---------- ----------
- 0D79C52C 0D79C55C 17 TM 79188 0 3 0 6 0
- 0D79C52C 0D79C55C 17 TM 80523 0 3 0 6 0
- 2DE15208 2DE15248 17 TX 196614 3830 6 0 6 0
SESSION 2:
- 我们对主表DELETE或UPDATE一条记录,这里要更新或删除子表中没有使用的记录:
- SQL> delete from dept where deptno=50;
- 你会发现上现这条语句hang住了
SESSION 1:
- 查看锁信息,你会发现22号会话想对80523对象(EMP表)加一个TYPE 4类型的锁,根据TM锁兼容的矩阵图,可以了解到3号与4号锁不兼容,所以22号会话被阻塞,需要等待:
- SQL> select * from v$lock where sid in (17,22) and type in ('TM','TX') order by sid,type;
- ADDR KADDR SID TY ID1 ID2 LMODE REQUEST CTIME BLOCK
- -------- -------- ---------- -- ---------- ---------- ---------- ---------- ---------- ----------
- 0D79C52C 0D79C55C 17 TM 79188 0 3 0 6 0
- 0D79C52C 0D79C55C 17 TM 80523 0 3 0 6 1
- 2DE15208 2DE15248 17 TX 196614 3830 6 0 6 0
- 0D79C52C 0D79C55C 22 TM 80523 0 0 4 3 0
- 0D79C52C 0D79C55C 22 TM 79188 0 3 0 3 0
- SQL> rollback;
- Rollback complete.
- 回滚后22话会话操作成功,再次查看锁信息,你会发现子表的4号锁已经没有了,所以验证了上面说的,只在语句操作期间加锁,操作完成后,就没了,不作用在整个事务上:
- SQL> select * from v$lock where sid in (17,22) and type in ('TM','TX') order by sid,type;
- ADDR KADDR SID TY ID1 ID2 LMODE REQUEST CTIME BLOCK
- -------- -------- ---------- -- ---------- ---------- ---------- ---------- ---------- ----------
- 0D79C52C 0D79C55C 22 TM 79188 0 3 0 724 0
- 2DDE31F4 2DDE3234 22 TX 589836 3809 6 0 47 0
上面我们还说过INSERT对主表操作,是不会对子表加锁的,可以按照上面的操作来一次,这里就不在演示,如果添加索引后,就不会发生这种阻塞,请自行验证。
再提一句,如果子表不能被加上类型4的锁,即上面SESSION 2被阻塞后,再有其它会话来更新子表(UPDATE/DELETE)也会被HANG住。
附上TM锁兼容矩阵图: