ITPub博客

首页 > Linux操作系统 > Linux操作系统 > 锁的兼容性与索引创建时的锁

锁的兼容性与索引创建时的锁

原创 Linux操作系统 作者:westzq1984 时间:2009-04-10 14:42:42 0 删除 编辑

先来看下METALINK上给出的锁的兼容性
          NULL-1  SS-2    SX-3    S-4     SSX-5   X-6
 -----------------------------------------------------
  NULL-1  YES     YES     YES     YES     YES     YES
    SS-2  YES     YES     YES     YES     YES     no   2行共享(RS)
    SX-3  YES     YES     YES     no      no      no   3行独占(RX)
     S-4  YES     YES     no      YES     no      no   4共享锁(S) 
   SSX-5  YES     YES     no      no      no      no   5共享行独占(SRX)
     X-6  YES     no      no      no      no      no   6独占(X)

如下是在进行操作是,需要的TM锁定的等级
  Operation                Lock Mode LMODE Lock Description
 ------------------------- --------- ----- ----------------
  Select                     NULL     1    null
  Select for update          SS       2    sub share
  Insert                     SX       3    sub exclusive
  Update                     SX       3    sub exclusive
  Delete                     SX       3    sub exclusive
  Lock For Update            SS       2    sub share
  Lock Share                 S        4    share
  Lock Exclusive             X        6    exclusive
  Lock Row Share             SS       2    sub share
  Lock Row Exclusive         SX       3    sub exclusive
  Lock Share Row Exclusive   SSX      5    share/sub exclusive
  Alter table                X        6    exclusive
  Drop table                 X        6    exclusive
  Create Index               S        4    share
  Drop Index                 X        6    exclusive
  Truncate table             X        6    exclusive

模式1,2的就忽略掉了,其也不能阻碍到什么其他操作。
模式3,4是互相阻碍的,也就是说,Create index 会阻碍DML,同时,DML也会阻碍Create index

1.create index id_test_id1 on test(id1) --创建过程中
ADDR     KADDR           SID TYPE        ID1        ID2      LMODE    REQUEST      CTIME      BLOCK
-------- -------- ---------- ---- ---------- ---------- ---------- ---------- ---------- ----------
2BC34504 2BC34518        145 DL        25900          0          3          0          8          0
2BC34A0C 2BC34A20        145 DL        25900          0          3          0          8          0
2B1C6550 2B1C6568        145 TM        25900          0          4          0          8          0  --TEST
2B1C65FC 2B1C6614        145 TM           18          0          3          0          6          0  --OBJ$
2B239924 2B239948        145 TX       262154       1860          6          0          8          0
 

2.delete test where rownum=1  (该进程挂起,直到索引创建完成)
ADDR     KADDR           SID TYPE        ID1        ID2      LMODE    REQUEST      CTIME      BLOCK
-------- -------- ---------- ---- ---------- ---------- ---------- ---------- ---------- ----------
2BC34504 2BC34518        145 DL        25900          0          3          0         45          0
2BC34A0C 2BC34A20        145 DL        25900          0          3          0         45          0
2B1C6550 2B1C6568        145 TM        25900          0          4          0         45          1   --CREATE INDEX持有模式4的TM
2B1C65FC 2B1C6614        145 TM           18          0          3          0         43          0
2B1C66A8 2B1C66C0        147 TM        25900          0          0          3          3          0  --DML语句请求模式3的TM,但被模式4阻塞
2B1C5A88 2B1C5AD0        145 TS            6   21059475          6          0          1          0
2B239924 2B239948        145 TX       262154       1860          6          0         45          0
 
--commit;
************************************************************************

3.delete test where rownum=1
ADDR     KADDR           SID TYPE        ID1        ID2      LMODE    REQUEST      CTIME      BLOCK
-------- -------- ---------- ---- ---------- ---------- ---------- ---------- ---------- ----------
2B1C6550 2B1C6568        147 TM        25900          0          3          0          6          0  --DML语句获得模式3的TM
2B216584 2B2165A8        147 TX       655406       1639          6          0          6          0
 

4.create index id_test_id1 on test(ID)

ORA-00054: resource busy and acquire with NOWAIT specified


5.create index id_test_id1 on test(ID) online 
ADDR     KADDR           SID TYPE        ID1        ID2      LMODE    REQUEST      CTIME      BLOCK
-------- -------- ---------- ---- ---------- ---------- ---------- ---------- ---------- ----------
2BC34504 2BC34518        145 DL        25900          0          3          0         12          0
2BC34A0C 2BC34A20        145 DL        25900          0          3          0         12          0
2B1C6550 2B1C6568        147 TM        25900          0          3          0         63          1  --DML语句持有模式3的TM
2B1C65FC 2B1C6614        145 TM        25900          0          2          4         12          0  --CREATE INDEX请求模式4的TM,但被模式3阻塞
2B1C66A8 2B1C66C0        145 TM        26036          0          4          0         10          0  --id_test_id1
2B216584 2B2165A8        147 TX       655406       1639          6          0         63          0
2B239924 2B239948        145 TX       196652       1856          6          0         12          0
 
这下不报错了,但是该语句会阻碍,知道事务提交,因为创建索引开始时,会请求一个模式4的TM锁定

6.将DELETE语句提交
ADDR     KADDR           SID TYPE        ID1        ID2      LMODE    REQUEST      CTIME      BLOCK
-------- -------- ---------- ---- ---------- ---------- ---------- ---------- ---------- ----------
2BC34504 2BC34518        145 DL        25900          0          3          0         57          0
2BC34A0C 2BC34A20        145 DL        25900          0          3          0         57          0
2B1C65FC 2B1C6614        145 TM        25900          0          2          0          3          0  --CREATE INDEX获得模式2的锁
2B1C66A8 2B1C66C0        145 TM        26036          0          4          0         55          0  --id_test_id1
2B239924 2B239948        145 TX       196652       1856          6          0         57          0

create index 语句开始执行,其执行时,在对象上面加的锁为2

7.delete test where rownum=1;   正常执行
ADDR     KADDR           SID TYPE        ID1        ID2      LMODE    REQUEST      CTIME      BLOCK
-------- -------- ---------- ---- ---------- ---------- ---------- ---------- ---------- ----------
2BC34504 2BC34518        145 DL        25900          0          3          0          6          0
2BC34A0C 2BC34A20        145 DL        25900          0          3          0          6          0
2B1C6550 2B1C6568        145 TM        25900          0          2          0          4          0  --CREATE INDEX持有模式2的锁
2B1C65FC 2B1C6614        145 TM        26039          0          4          0          4          0  --id_test_id1
2B1C66A8 2B1C66C0        147 TM        25900          0          3          0          3          0  --DML获得模式3的锁定
2B216584 2B2165A8        147 TX        65568       1820          6          0          3          0
2B228488 2B2284AC        145 TX       524307       1826          6          0          6          0
  
8.但是注意,如果DELETE语句不提交,创建索引将无法完成,因为其提交是,需要请求模式4
ADDR     KADDR           SID TYPE        ID1        ID2      LMODE    REQUEST      CTIME      BLOCK
-------- -------- ---------- ---- ---------- ---------- ---------- ---------- ---------- ----------
2BC34504 2BC34518        145 DL        25900          0          3          0         19          0
2BC34A0C 2BC34A20        145 DL        25900          0          3          0         19          0
2B1C6550 2B1C6568        145 TM        25900          0          2          4         17          0  --CREATE INDEX持有模式2的锁,但是在请求模式4
2B1C65FC 2B1C6614        145 TM        26042          0          4          0         17          0 
2B1C66A8 2B1C66C0        147 TM        25900          0          3          0         16          1  --DML持有模式3的锁定
2B1C5A88 2B1C5AD0        145 TS            6   21101523          6          0          9          0
2B216584 2B2165A8        147 TX       131079       1835          6          0         16          0
2B228EE0 2B228F04        145 TX       458757       1787          6          0         19          0
 
9.在开一个会话运行
delete test where id=1000;  被阻塞,因为其要请求一个模式3的锁,而前面有个模式4的锁定在排队
 
ADDR     KADDR           SID TYPE        ID1        ID2      LMODE    REQUEST      CTIME      BLOCK
-------- -------- ---------- ---- ---------- ---------- ---------- ---------- ---------- ----------
2BC34504 2BC34518        145 DL        25900          0          3          0        109          0
2BC34A0C 2BC34A20        145 DL        25900          0          3          0        109          0
2B1C6550 2B1C6568        145 TM        25900          0          2          4        107          0  --CREATE INDEX持有模式2的锁,但是在请求模式4
2B1C65FC 2B1C6614        145 TM        26042          0          4          0        107          0
2B1C66A8 2B1C66C0        147 TM        25900          0          3          0        106          1  --DML持有模式3的锁定,阻塞CREATE INDEX的完成
2B1C6754 2B1C676C        143 TM        25900          0          0          3          3          0  --新的DML需要一个模式3的锁定,但是被前面的模式4阻碍
2B1C5A88 2B1C5AD0        145 TS            6   21101523          6          0         99          0
2B216584 2B2165A8        147 TX       131079       1835          6          0        106          0
2B228EE0 2B228F04        145 TX       458757       1787          6          0        109          0
 
10.将第一个DELETE提交,所有锁都释放

我就遇到过因为在一个运行缓慢的系统上创建一个索引导致数据库出现大量锁的时候,最好还是在业务不繁忙的时候做

CREATE INDEX ONLINE还是要慎用,可以见到,其创建开始和创建结束的时候,都将去请求模式4的TM锁定,
该请求可能由于表上的事务没提交,而被其他会话持有模式3的TM锁而阻塞,而其又将继续去阻塞后面的DML操作,
使数据库遭遇大量的ENQUEUE(TM)等待,数据库运行缓慢直到挂起

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/8242091/viewspace-587000/,如需转载,请注明出处,否则将追究法律责任。

下一篇: ora-12801错误
请登录后发表评论 登录
全部评论

注册时间:2009-04-06

  • 博文量
    251
  • 访问量
    953151