创建索引时的锁

创建索引时的锁

create index id_test_id1 on test(id1) --创建过程中
ADDR KADDR SID TYPE ID1 ID2 LMODE REQUEST CTIME BLOCK
---------------- ---------------- ---------- ---- ---------- ---------- ---------- ---------- ---------- ----------
0700000010B75788 0700000010B75900 96 TX 262153 11965 6 0 20 0
0700000010956B98 0700000010956BC0 96 TM 18 0 3 0 20 0 --OBJ$
0700000010956AC8 0700000010956AF0 96 TM 37564 0 4 0 20 0 --TEST 
070000000F259A70 070000000F259A90 96 DL 37564 0 3 0 20 0

delete test where rownum=1 (该进程挂起，直到索引创建完成)
ADDR KADDR SID TYPE ID1 ID2 LMODE REQUEST CTIME BLOCK
---------------- ---------------- ---------- ---- ---------- ---------- ---------- ---------- ---------- ----------
0700000010956C68 0700000010956C90 27 TM 37564 0 0 3 9 0
0700000010C81A38 0700000010C81BB0 96 TX 131119 10191 6 0 12 0
0700000010956B98 0700000010956BC0 96 TM 18 0 3 0 12 0
0700000010956AC8 0700000010956AF0 96 TM 37564 0 4 0 12 1
070000000F259A70 070000000F259A90 96 DL 37564 0 3 0 12 0

2 3 4 5 6 
NULL SS SX S SSX X 
----------------------------------------------------
NULL YES YES YES YES YES YES 
2 SS YES YES YES YES YES no 
3 SX YES YES YES no no no 
4 S YES YES no YES no no 
5 SSX YES YES no no no no 
6 X YES no no no no no 

create index上37564的模式4的TM锁定，柱塞了delete操作请求的37564的模式3的TM锁定

********************************************************************************************************************************

create index id_test_id1 on test(id1) online
ADDR KADDR SID TYPE ID1 ID2 LMODE REQUEST CTIME BLOCK
---------------- ---------------- ---------- ---- ---------- ---------- ---------- ---------- ---------- ----------
0700000010956B98 0700000010956BC0 96 TM 37568 0 4 0 3 0
0700000010956AC8 0700000010956AF0 96 TM 37564 0 2 0 3 0
070000000F259A70 070000000F259A90 96 DL 37564 0 3 0 3 0

delete test where rownum=1 (该进程未挂起)
ADDR KADDR SID TYPE ID1 ID2 LMODE REQUEST CTIME BLOCK
---------------- ---------------- ---------- ---- ---------- ---------- ---------- ---------- ---------- ----------
0700000010AEF980 0700000010AEFAF8 27 TX 131097 10193 6 0 2 0
0700000010956C68 0700000010956C90 27 TM 37564 0 3 0 2 0
0700000010956B98 0700000010956BC0 96 TM 37571 0 4 0 4 0 --SYS_JOURNAL_37570
0700000010956AC8 0700000010956AF0 96 TM 37564 0 2 0 4 0
070000000F259A70 070000000F259A90 96 DL 37564 0 3 0 5 0

如果使用online建立索引，create index将不在37564上建立模式4的TM锁定，而退化到模式2的TM锁定，其建立了一张日志表37571，用以存放CREATE期间的数据变化，在37571上持有模式4的TM锁定。而delete操作请求的模式3的TM锁和create操作持有的模式2的TM锁是兼容的，不会柱塞。

********************************************************************************************************************************

delete test where rownum=1
ADDR KADDR SID TYPE ID1 ID2 LMODE REQUEST CTIME BLOCK
---------------- ---------------- ---------- ---- ---------- ---------- ---------- ---------- ---------- ----------
0700000010AEF980 0700000010AEFAF8 27 TX 524321 12284 6 0 21 0
0700000010956AC8 0700000010956AF0 27 TM 37564 0 3 0 21 0

create index id_test_id2 on test(id2) (失败，报ora-00054)
这是因为,create请求的模式4的TM锁定与DELETE持有的模式3的TM锁定不兼容
create index id_test_id2 on test(id2) online 
ADDR KADDR SID TYPE ID1 ID2 LMODE REQUEST CTIME BLOCK
---------------- ---------------- ---------- ---- ---------- ---------- ---------- ---------- ---------- ----------
0700000010C81A38 0700000010C81BB0 27 TX 589859 12765 6 0 43 0
0700000010956AC8 0700000010956AF0 27 TM 37564 0 3 0 43 1
0700000010956C68 0700000010956C90 96 TM 37575 0 4 0 2 0
0700000010956B98 0700000010956BC0 96 TM 37564 0 2 4 3 0
070000000F259A70 070000000F259A90 96 DL 37564 0 3 0 3 0

CREATE语句将被阻塞，因为其将在37564上短暂的请求一个模式4的TM锁定，但是和delete持有的模式3的TM锁不兼容
如果delete语句rollback或者commit后，CREATE将获得模式4的TM锁定

delete test where id1=1000 (与rownum=1不是同一行)
ADDR KADDR SID TYPE ID1 ID2 LMODE REQUEST CTIME BLOCK
---------------- ---------------- ---------- ---- ---------- ---------- ---------- ---------- ---------- ----------
0700000010C81A38 0700000010C81BB0 27 TX 589859 12765 6 0 192 0
0700000010956AC8 0700000010956AF0 27 TM 37564 0 3 0 192 1
0700000010956C68 0700000010956C90 96 TM 37575 0 4 0 151 0
0700000010956B98 0700000010956BC0 96 TM 37564 0 2 4 152 0
070000000F259A70 070000000F259A90 96 DL 37564 0 3 0 152 0
0700000010B1CC20 0700000010B1CD98 450 TX 131083 10201 6 0 31 0
0700000010956D38 0700000010956D60 450 TM 37564 0 3 0 31 0

rebuild online的道理和CREATE相同，在执行期间只持有模式2的TM锁，不会阻塞DML操作，但在操作的开始和结束阶段，是需要短暂的持有模式为4的TM锁的，模式4的TM锁会阻塞表上的所有DML操作（因为DML将请求模式3的TM锁）。

在做create index,rebuild index online的时候，一定要在事务较少的时候进行，最好的晚上不繁忙的时候在做。还有，如果不是关闭应用维护索引的话，一定要指定online

2 3 4 5 6 
NULL SS SX S SSX X
-----------------------------------------------------
NULL YES YES YES YES YES YES
2 SS YES YES YES YES YES no 2行共享(RS)：共享表锁，sub share 
3 SX YES YES YES no no no 3行独占(RX)：用于行的修改，sub exclusive 
4 S YES YES no YES no no 4共享锁(S)：阻止其他DML操作，share 
5 SSX YES YES no no no no 5共享行独占(SRX)：阻止其他事务操作，share/sub exclusive 
6 X YES no no no no no 6独占(X)：独立访问使用，exclusive

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
-----------------------------------------------------------

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

转载于:http://blog.itpub.net/611609/viewspace-743709/