TM锁的理解

关于TM锁
平台：WINDOWS NT 32bit
数据库：Oracle 10.2.0.1

TM enqueue队列锁在进行DML操作前获得，以阻止对正在操作的数据表进行任何DDL操作(在DML操作一个数据表时，其结构不能被更改)。
例如一个DML事务在对一张表操作，还没有commit或rollback，这时用DDL修改表的结构时，可能会遇到类似下面的错误：
ORA-00054: 资源正忙, 但指定以 NOWAIT 方式获取资源

但在实际系统中，该等待事件通常与应用有关，特别是由于外键约束没有索引。
下面用例子列举几种发生TM等待的情况，大致分为两种，没索引和有索引。这里的索引是指在一对有外键关联的父子表中，子表上有外键的列是否有索引。关于DDL的等待就不再给

出例子：

 

建立两张表，他们有外键关联：
SQL> create table DEPT (deptno number constraint pk_dept primary key,
     dname varchar2(10));

SQL> create table EMP (deptno number(2) constraint fk_deptno references
     dept(deptno), ename varchar2(20));

外键没有 ON DELETE项
各插入2条记录：

SQL> insert into DEPT values (1, 'COSTCENTER');
SQL> insert into DEPT values (2, 'MONITOR');

SQL> insert into EMP values (1, 'SCOTT');
SQL> insert into EMP values (2, 'Harry');

DEPT的对象号：54526
EMP的对象号：54528
TM类型的LOCK在V$LOCK中的ID1表示OBJECT_ID

无索引

对子表操作造成父表锁：
INSERT:
insert into emp values(1,'Peter');
只看TM锁：
ADDR     KADDR           SID TY        ID1        ID2      LMODE    REQUEST      CTIME      BLOCK
-------- -------- ---------- -- ---------- ---------- ---------- ---------- ---------- ----------
333415A4 333415BC        141 TM      54526          0          2          0          9          0
33341668 33341680        141 TM      54528          0          3          0          9          0

在子表上的一条INSERT语句除了会引起子表自己的一个行排他锁(LMODE=3 row-X (SX))，还会引起父表的一个行共享锁(LMODE=2 row-S (SS))

UPDATE:
update emp set deptno=2 where ename='SCOTT';
update emp set deptno=2 where deptno=1;
以上2句UPDATE语句引起子表自己的一个行排他锁(LMODE=3 row-X (SX))，还会引起父表的一个行共享锁(LMODE=2 row-S (SS))

update emp set ename='Alex' where deptno=2;
update emp set ename='Alex' where ename='SCOTT';
以上2句UPDATE语句只引起子表自己的一个行排他锁(LMODE=3 row-X (SX))

DELETE:
delete emp where deptno=2;
delete emp where ename='Harry';
在子表上的一条DELETE语句除了会引起子表自己的一个行排他锁(LMODE=3 row-X (SX))，还会引起父表的一个行共享锁(LMODE=2 row-S (SS))

可以看出，对子表操作，只要涉及到外键列，就会在父表上产生一个行共享锁(LMODE=2 row-S (SS))。

对父表操作造成子表锁：

INSERT:
insert into dept values(3,'Sales');
只看TM锁：
ADDR     KADDR           SID TY        ID1        ID2      LMODE    REQUEST      CTIME      BLOCK
-------- -------- ---------- -- ---------- ---------- ---------- ---------- ---------- ----------
333415A4 333415BC        155 TM      54526          0          3          0          3          0
33341668 33341680        155 TM      54528          0          2          0          3          0

在父表上的一条INSERT语句除了会引起父表自己的一个行排他锁(LMODE=3 row-X (SX))，还会引起子表的一个行共享锁(LMODE=2 row-S (SS))

UPDATE:
update dept set deptno=3 where dname='COSTCENTER';
update dept set deptno=3 where deptno=1;
注意：修改dept表的deptno时除了需要考虑主键唯一性索引外，还需要考虑是否有存在子集，先将EMP表的deptno=1的记录删除，再UPDATE dept表。

update dept set dname='Sales' where deptno=2;
update dept set dname='Sales' where dname='MONITOR';
以上UPDATE语句只引起子表自己的一个行排他锁(LMODE=3 row-X (SX))
因为UPDATE的都没有影响到与子表相关的内容。

DELETE:
delete dept where deptno=1;
delete dept where dname='COSTCENTER';
在父表上的一条DELETE语句的情况与UPDATE相同。

以上只是看到的现象，实际中在子表上INSERT/DELETE/UPDATE引起父表获得锁。在整个父表存在共享锁(LMODE=4)直到在子表上的增删改事务提交，从而防止父表被修改。
例如
sess#1:  insert into emp values(1,'Peter');
sess#2:  delete dept where deptno=1;
这时SESS#2会被hang住，从V$LOCK上看到：
ADDR     KADDR           SID TY        ID1        ID2      LMODE    REQUEST      CTIME      BLOCK
-------- -------- ---------- -- ---------- ---------- ---------- ---------- ---------- ----------
333415A4 333415BC        141 TM      54526          0          2          0        114          0
33341668 33341680        141 TM      54528          0          3          0        114          1
3334172C 33341744        155 TM      54526          0          3          0         36          0
333417F0 33341808        155 TM      54528          0          0          4         36          0

SESS                                                    ID1        ID2      LMODE    REQUEST TY
------------------------------------------------ ---------- ---------- ---------- ---------- --
Holder: 141                                           54528          0          3          0 TM
Waiter: 155                                           54528          0          0          4 TM

但是在父表上执行insert可以顺利执行，或者UPDATE不相关外键的内容，但是DELETE都会hang住，尽管是删除子表中没有关联信息的行，都会出现以上的情况。

由于前面的例子没有ON DELETE选项，所以只有INSERT会引起子表的锁,但是不会产生TM锁。
增加ON DELETE选项：
alter table emp drop constraint fk_deptno;
alter table emp add constraint fk_deptno foreign key (deptno) references dept(deptno) on delete cascade;

这种情况下的在父表上执行DELETE语句会在父表和子表上各产生一个行排他锁(LMODE=3 row-X (SX))。

ADDR     KADDR           SID TY        ID1        ID2      LMODE    REQUEST      CTIME      BLOCK
-------- -------- ---------- -- ---------- ---------- ---------- ---------- ---------- ----------
333415A4 333415BC        155 TM      54526          0          3          0          6          0
33341668 33341680        155 TM      54528          0          3          0          6          0

但当删除父表记录时，INSERT/UPDATE/DELETE子表只会产生TX锁等待，没有TM锁等待。

由上可知，TM锁等待最常发生的情况是，在子表的DML未提交，引起父表的TM锁等待。

有索引

在emp的deptno字段上建立索引：
create index emp_idx on emp(deptno);

对比以下情况：
sess#1:  update emp set ename='Alex' where deptno=1;
sess#2:  delete dept where deptno=2;

无索引的，产生的是TM锁等待。
SESS                                                    ID1        ID2      LMODE    REQUEST TY
------------------------------------------------ ---------- ---------- ---------- ---------- --
Holder: 141                                           54528          0          3          0 TM
Waiter: 155                                           54528          0          0          5 TM

当删除父表时，由于级联删除约束会获得一个SSX (LMODE=5)锁。

有索引时，没有锁等待的发生，也就减少了这个SSX (LMODE=5)锁。

 

没有索引时，当删除主表的一条记录时，需要检查现有的子表记录(级联删除或没有影响)。导致子表全扫描。从性能角度来看不推荐这样做。
最好在外键上总是使用索引，除非主表是静态的,几乎不对其操作，这种情况下，可以使用没有索引的外键。