有關lock的一點測試總結！

理解oracle lock的概念，有助於理解多使用者的併發執行。

[@more@]

測試過程1：
--===========================================================================
1.測試表t是一個存在200w左右資料的大表，資料完全來自dba_objects
session1:
22:52:28 SQL> select count(object_id) from t;

COUNT(OBJECT_ID)
----------------
2019072

已用時間: 00: 00: 01.20
22:21:46 SQL> create index idx_t on t(object_id);
執行上面create index語句，保證能持續一段時間，以便在session2,3,4中進行其他操作
session:
檢測在t上都加了什麼型別的lock
SQL> SELECT sid,type,LMODE,
2 decode(lmode ,0 , 'none',1,'null',2,'row share',
3 3,'row exclusive',4,'share',5,'share row exclusive',6,
4 'exclusive') lock_type , request,ctime ,block
5 from v$lock
6 where type in ('TX','TM');

SID TY LMODE LOCK_TYPE REQUEST CTIME BLOCK
---------- -- ---------- ------------------- ---------- ---------- ----------
139 TM 4 share 0 3 0
139 TM 3 row exclusive 0 3 0
139 TX 6 exclusive 0 3 0

SQL>
session3：
嘗試執行dml操作：
SQL> insert into t(id ,name) values(1,'a');
一直等待，被阻塞
session4：
嘗試執行ddl操作：
SQL> drop table t;
drop table t
*
第 1 行出現錯誤:
ORA-00054: 資源正忙, 但指定以 NOWAIT 方式獲取資源, 或者超時失效

SQL>
--===========================================================================
測試過程2：
session1:
22:23:14 SQL> alter index idx_t rebuild;
重新index保證能持續一段時間，以便在session2,3,4中進行其他操作
session2:
SQL> /

SID TY LMODE LOCK_TYPE REQUEST CTIME BLOCK
---------- -- ---------- ------------------- ---------- ---------- ----------
139 TM 4 share 0 3 0

SQL>
session3:
SQL> insert into t(id ,name) values(1,'a');
一直等待，被阻塞
session4:
SQL> drop table t;
drop table t
*
第 1 行出現錯誤:
ORA-00054: 資源正忙, 但指定以 NOWAIT 方式獲取資源, 或者超時失效

SQL>
--===========================================================================
測試過程3：
session1:
22:30:21 SQL> alter index idx_t rebuild online;
線上重新index保證能持續一段時間，以便在session2,3,4中進行其他操作
session2:
SQL> /

SID TY LMODE LOCK_TYPE REQUEST CTIME BLOCK
---------- -- ---------- ------------------- ---------- ---------- ----------
139 TM 2 row share 0 2 0
139 TM 4 share 0 2 0
139 TX 6 exclusive 0 2 0
session3:
SQL> insert into t(id ,name) values(1,'a');

已建立 1 行。
SQL> select sid from v$mystat where rownum=1;

SID
----------
136
說明在線上重建index期間，可以執行dml操作
session4:
SQL> drop table t;
drop table t
*
第 1 行出現錯誤:
ORA-00054: 資源正忙, 但指定以 NOWAIT 方式獲取資源, 或者超時失效
--==========================================================================
在session3成功執行之後既不commit也不rollback，再到session2中檢視一下lock的使用情況：
SQL> /

SID TY LMODE LOCK_TYPE REQUEST CTIME BLOCK
---------- -- ---------- ------------------- ---------- ---------- ----------
139 TM 2 row share 0 16 0
139 TM 4 share 0 16 0
136 TM 3 row exclusive 0 12 0
139 TX 6 exclusive 0 16 0
139 TX 6 exclusive 0 5 0
136 TX 6 exclusive 0 9 0

已選擇6行。

過一段時間初步估計在session1中的alter index idx_t rebuild online;操作應該能完成的時間之後再

來session2中監控一下：
SQL> /

SID TY LMODE LOCK_TYPE REQUEST CTIME BLOCK
---------- -- ---------- ------------------- ---------- ---------- ----------
139 TX 0 none 4 373 0
139 TM 2 row share 0 391 0
139 TM 4 share 0 391 0
136 TM 3 row exclusive 0 387 0
139 TX 6 exclusive 0 391 0
136 TX 6 exclusive 0 384 1

已選擇6行。

SQL>
發現第一行lock的lock_type由原來的2變成了0也就是說現在session1的操作進行到即將結束時不能在

session3插入的那條新資料insert into t(id ,name) values(1,'a')上獲得row exclusive lock而被阻

塞了，立即在session3中commit
session3:
SQL> commit;

提交完成。

SQL>
發現session1中被阻塞的alter index idx_t rebuild online;操作也馬上執行完畢了
session1:
22:30:21 SQL> alter index idx_t rebuild online;

索引已更改。

已用時間: 00: 14: 19.31
再回到session2中看看lock的監控情況:
SQL> /

未選定行

SQL>
一切都釋放了!
--====================================================================
上面的現象如何解釋，我想用doc上的一段話很容易就解釋清楚了
doc連線如下：

仔細看下面列出的表格，看看到底那些表鎖能相互相容那些不可以!
Table 13-3 Summary of Table Locks

SQL Statement Mode of Table Lock Lock Modes Permitted?
RS RX S SRX X
SELECT...FROM table...
none
Y
Y
Y
Y
Y

INSERT INTO table ...
RX
Y
Y
N
N
N

UPDATE table ...
RX
Y*
Y*
N
N
N

DELETE FROM table ...
RX
Y*
Y*
N
N
N

SELECT ... FROM table FOR UPDATE OF ...
RS
Y*
Y*
Y*
Y*
N

LOCK TABLE table IN ROW SHARE MODE
RS
Y
Y
Y
Y
N

LOCK TABLE table IN ROW EXCLUSIVE MODE
RX
Y
Y
N
N
N

LOCK TABLE table IN SHARE MODE
S
Y
N
Y
N
N

LOCK TABLE table IN SHARE ROW EXCLUSIVE MODE
SRX
Y
N
N
N
N

LOCK TABLE table IN EXCLUSIVE MODE
X
N
N
N
N
N

RS: row share

RX: row exclusive

S: share

SRX: share row exclusive

X: exclusive

*Yes, if no conflicting row locks are held by another transaction. Otherwise, waits occur.