先来看下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/，如需转载，请注明出处，否则将追究法律责任。
转载于:http://blog.itpub.net/8242091/viewspace-587000/
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
