Oracle 0至6級鎖的通俗解釋及實驗案例

11g Concepts中摘錄的鎖的資訊
Table Locks (TM)
A table lock, also called a TM lock, is acquired by a transaction when a table is modified by an INSERT, UPDATE, DELETE, MERGE, SELECT with the FOR UPDATE clause, or LOCK TABLE statement. DML operations require table locks to reserve DML access to the table on behalf of a transaction and to prevent DDL operations that would conflict with the transaction.
當事務透過INSERT、UPDATE、DELETE、MERGE和FOR UPDATE對錶進行修改時，就會獲得一個表鎖，也稱為TM鎖子句，或鎖表語句。DML操作需要表鎖來為事務保留對錶的DML訪問權限，並防止DDL與事務衝突的操作。

A table lock can be held in any of the following modes:
Row Share (RS)
This lock, also called a subshare table lock (SS), indicates that the transaction holding the lock on the table has locked rows in the table and intends to update them. A row share lock is the least restrictive mode of table lock, offering the highest degree of concurrency for a table.
這個鎖，也稱為子共享表鎖(SS)，表示持有表上鎖的事務已鎖定表中的行並打算鎖定更新它們。行共享鎖是表鎖中限制最少的一種模式，它為表提供最高程度的併發性。
Row Exclusive Table Lock (RX)
This lock, also called a subexclusive table lock (SX), generally indicates that the transaction holding the lock has updated table rows or issued SELECT ... FOR UPDATE. An SX lock allows other transactions to query, insert, update, delete, or lock rows concurrently in the same table. Therefore, SX locks allow multiple transactions to obtain simultaneous SX and subshare table locks for the same table.
這個鎖，也稱為subexclusive table lock (SX)，通常表示持有鎖的事務已經更新了錶行或發出了SELECT…FOR UPDATE。SX鎖允許其他事務在同一表中同時查詢、插入、更新、刪除或鎖定行。因此,SX鎖允許多個事務為同一個表獲取同步的SX和子共享表鎖。
Share Table Lock (S)
A share table lock held by a transaction allows other transactions to query the table (without using SELECT ... FOR UPDATE), but updates are allowed only if a single transaction holds the share table lock. Because multiple transactions may hold a share table lock concurrently, holding this lock is not sufficient to ensure that a transaction can modify the table.
事務持有的共享表鎖允許其他事務查詢表(除了SELECT…FOR UPDATE)，但只允許更新如果一個事務持有共享表鎖。由於多個事務可能同時持有一個共享表鎖，因此持有此鎖不足以確保事務可以修改表。
Share Row Exclusive Table Lock (SRX)
This lock, also called a share-subexclusive table lock (SSX), is more restrictive than a share table lock. Only one transaction at a time can acquire an SSX lock on a given table. An SSX lock held by a transaction allows other transactions to query the table (except for SELECT ... FOR UPDATE) but not to update the table.
這個鎖，也稱為共享-subexclusive table鎖(SSX)，比共享表鎖有更多的限制。一次只能獲得一個事務SSX鎖定給定的表。事務持有的SSX鎖允許其他事務查詢表(除了SELECT…FOR UPDATE)，但不更新表。
Exclusive Table Lock (X)
This lock is the most restrictive, prohibiting other transactions from performing any type of DML statement or placing any type of lock on the table.
此鎖是最嚴格的，禁止其他事務執行任何型別的DML語句或將任何型別的鎖放在表上。

因為ORACLE要處理不同的併發功能，一旦處理不了那麼多併發，就需要排隊，為保證排隊的公平就會出現各種優先順序，因此衍生出很多鎖模式，來支援不同業務層的併發需求。
在同一個session裡面，你執行一個UPDATE語句，在表上有DML鎖，那自己能去做DDL語句嗎，比如DROP?
因為是同一個session，所以不涉及併發，自己做一個update不提交，隨後drop table也是可以的



行鎖：0、6兩類鎖
表鎖：0、1、2、3、4、5、6七類鎖
0（none）
1（null）
2（RS）
3（RX）
4（S）
5（SRX）
6（X）
R是ROW行，S是SHARE共享，X是eXclusive排他，獨佔鎖的意思

0：null 空
一般的SELECT，在表和行上都是0級鎖

1：null 空 
1級鎖有：Select有時會在v$locked_object出現。 

2：Row-S 行共享(RS)：共享表鎖，sub share  
2級鎖有：Lock Row Share，create index online
表鎖的情況下
locked_mode 2不影響後一個locked_mode 2、3、4、5的會話，如果後一個會話locked_mode為6，則後一個會話操作會提示ora-00054錯誤。 
ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired
行鎖的情況下
locked_mode 2對應行鎖0級鎖，不影響其他會話。

3：Row-X 行獨佔(RX)：用於行的修改，sub exclusive 
3級鎖有：Insert, Update, Delete, Select for update,Lock Row Exclusive
表鎖的情況下
locked_mode 3不影響後一個locked_mode 3的會話，但如果後一個會話locked_mode為4,5,6，則後一個會話操作會提示ora-00054錯誤。 
ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired
行鎖的情況下
locked_mode 3的表鎖對應行鎖6級鎖，兩個會話對同一行則影響。

4：Share 共享鎖(S)：阻止其他DML操作，share 
4級鎖有：Create Index, Lock Share

5：S/Row-X 共享行獨佔(SRX)：阻止其他事務操作，share/sub exclusive  
5級鎖有：Lock Share Row Exclusive  
具體來講有主外來鍵約束時update/delete ... ; 可能會產生4,5的鎖。 

6：exclusive 獨佔(X)：獨立訪問使用，exclusive 
6級鎖有：Drop table, Drop Index, Alter table，Truncate table, Lock Exclusive



參考Maclean的講座，用一個珠寶店來做比喻說明
珠寶店可以給大家免費參觀，可以讓你預定，可以試用後覺得好再買，可以把店都買下來
第0類人，免費參觀珠寶店的人
第1類人，免費參觀珠寶店的老弱病殘孕的客人
第2類人，預定了試用期，先買來幾天，如果試用後覺得好再買
第3類人，直接到店裡的目的就是立即購買
第4類人，把整個店的珠寶包下來，讓別人參觀，預定，但是不能買賣（這在ORACLE中叫只讀鎖，只允許別人讀，也就是隻允許第0，1，2類人來珠寶店，讓別人只讀方式的參觀，不允許買賣，再來個第4類人，還是允許的，因為大家雖然都想包，但是大家的目的都是分享，而不是獨佔，所以是可以相容的）
第5類人，它跟第4類人的區別只有一條，就是第5類人包下整個珠寶店後，另一個第5類人就不允許再包了(這在ORACLE中叫寫鎖定)，也就是第5類人是單通道的，你在珠寶店裡只能找到1個第5類人，不可能找出第2個第5類人，但是第5類人把珠寶店包下來後，仍然可以讓第0，1，2類人參觀，但不允許買賣
第6類人，它把整個珠寶店盤下來，不允許任何人有目的的參觀，只允許免費參觀，它是獨佔的，只允許0,1類人參觀，其他人都不允許
--以上第2類人預定的，所以第3類跟6類人不相容
--以上第3類人是要買珠寶的，所以第3類跟4，5，6類人都不相容

把珠寶店當成表，那珠寶店裡的珠寶櫃子當成行
珠寶店，7種人對應7種模式，對應表的7種鎖，0、1、2、3、4、5、6
櫃子，開啟或關閉2種狀態對應2種模式，對應行的2種鎖，0、6

珠寶店
（能不能同時進店，可以的）
表級鎖相當於珠寶店大門鎖，由門衛把關，表鎖有 0,1,2,3,4,5,6對應7類人群，7類人群能出現其中幾類人同時進店的情況，比如0、1、2、3類人同時進來了，或3類人同時進來好多人。

0級鎖：就是沒有鎖，只有純粹的select語句
0類人：免費參觀，不跟其他顧客有任何競爭

1級鎖：其實起不了鎖定的作用，他就是有一個通知的功能，根本阻止不了DDL，類似把執行計劃中的物件通知物件所屬的會話
1類人：（老弱病殘）免費參觀，不跟其他顧客有任何競爭，但是這個顧客有權知道這個店以後的動態，比如是否拆了。
比如會話A執行select * from T，然後把執行計劃儲存到記憶體，為了保護執行計劃是正確的，會話A要享受老弱病殘孕幼的待遇，因為如果T表被別人刪除了，那會話A生成的執行計劃還有用嗎？如果你不通知，A怎麼知道這個表物件已經失效了，也就是有1號鎖的物件，一旦被刪除，它會通知擁有該物件的會話，這個物件刪除了，請你重新再分析下你的SQL，1號鎖是系統自動生成的

2級表鎖：只跟X衝突，因為其他都是共享鎖，RX,SRX雖然也有X,但是是行的X,表上還是共享的意思，2級鎖在表級別和0-5級不衝突
2類人：有意圖買珠寶的人，但現在只是先來檢視下貨是不是值得我買，所以要開啟櫃檯，它只是一個SELECT動作。不會正面跟有免費參觀、有買賣企圖的顧客衝突。
2級表鎖的產生方式
顯式產生表級鎖（LOCK TABLE table IN ROW SHARE MODE，顯式產生一個RS表級鎖）
注意, 顯式產生表級鎖只產生表級鎖,不會級聯產生行級鎖，所以不會和其他會話產生行鎖

3級鎖：產生的原因（update、delete、select for update、顯示鎖表LOCK TABLE table IN ROW EXCLUSIVE MODE）
3類人：直接購買珠寶的人，所以要開啟櫃檯

6號的X是整個表級的排它鎖，顯示鎖表 LOCK TABLE table IN Exclusive MODE


珠寶櫃子
(能不能同時開啟同一個櫃子,不能啊，沒有這種概念)
行級鎖相當於珠寶店櫃檯鎖，由營業員把關，行鎖有0、6兩種對應櫃檯兩種狀態關閉、開啟
通常顧客如果進入珠寶店，跑到櫃檯前有哪幾種目的？
參觀
櫃子狀態是關閉:0號模式
只是以參觀為目的的顧客(第0類人、第1類人)，不存在資源競爭的問題，那還需要營業員拿鎖出來開啟櫃檯嗎？不需要，因為沒有資源競爭就不需要鎖了。
0號模式的行級鎖是因為0、1號的表級鎖造成的，簡單的select語句既是0級表級鎖也是0級行級鎖，也就是沒鎖

購買
櫃子狀態是開啟：6號模式
第2類人，試用期（試用期間不能讓別人用）
第3類人，立即購買（相當於我們的update、delete、select for  update、LOCK TABLE table IN ROW EXCLUSIVE MODE語句）
總結：update、delete、select for update在行上都是產生排他鎖


共享鎖將允許別的共享鎖存在，也就是共享跟共享是不衝突的。
比如使用者A在表T上執行了UPDATE第1行，那麼表t上有個表級的共享鎖，那使用者B在表T上執行了UPDATE第2行，那麼也會在表t上有個表級的共享鎖，雖然行上都是排它鎖，但不是同一行，所以他們在行上沒有衝突，在表上也沒有衝突
比如使用者A執行LOCK TABLE T IN ROW EXCLUSIVE MODE，使用者B可以同時執行LOCK TABLE T IN ROW EXCLUSIVE MODE或LOCK TABLE T IN ROW SHARE MODE

有行級鎖,必有表級鎖（3級表鎖引起6級行鎖）
有表級鎖,可以沒有行級鎖（顯式鎖，2,3,6號顯示鎖對應的表級鎖）
6號模式的行級鎖是因為2、3號的表級鎖造成的


ORACLE的鎖放在DATABASE BUFFER、LIBRARY CACHE的塊裡，不佔用其他記憶體。其他db2、informix裡，鎖會佔用記憶體,所以db2行鎖多會升級成表鎖

鎖的型別根據鎖的物件，分三大類
DML鎖
DDL鎖
內部鎖或LATCH

DML和DDL涉及可見的SCHEMA物件
DML就是我們的DELETE,UPDATE,INSERT語句,它操作的是表，檢視等,是可見的SCHEMA物件
DDL語句是ALTER TABLE,CREATE TABLE等語句同樣物件是表，檢視，儲存過程等,也是可見的SCHEMA物件

內部鎖或LATCH
使用者是看不到的，看不見被封裝起來的物件有哪些,就是內部鎖（LIBRARY CACHE，DATABASE BUFFER），因為這些物件都是共享的，共享的物件就涉及到資源競爭，所以必須要用鎖來進行限制資源的訪問，對於保護記憶體的低階鎖，我們叫做latch，它的機制類似紅綠燈，一條馬路是公用的，我們要設紅綠燈吧。如果就是私人的，那就沒必要設紅綠燈，所以PGA沒有latch。

DML是資料維護鎖
DML鎖是用來控制多個使用者並行訪問的資料確保一致性，SELECT是沒有任何鎖，只有select for update才有鎖
select...for update會鎖住結果行，導致其他session無法更新

DML鎖是確保在某一事務期間修改的資料，不允許其他事務進行修改
DML鎖確保被修改的表的事務還沒有結束時，不允許其他事務在表上做DDL。
（當然本使用者當前會話對錶update不提交，本使用者當前會話可以直接對該表做ddl，本使用者重新開一個session是不可以對該表做ddl的，其他使用者更是不能對該表做ddl）

DML鎖定按物件級別不同分：
表級鎖 TM（作用在表物件上，Table Manager）
行級鎖 TX（作用在行物件上，Transaction eXclusive）
ORACLE不會發生行級鎖升級成表級鎖
這就好像四合院，四間房門合成一個院，四合院的大門就是表鎖，每個房間就是行鎖
如果在sqlserver資料庫，當有3間房門要鎖起來的話，那我就直接鎖大門，因為sqlserver資料庫，鎖鑰匙很貴重，為了節省鑰匙就有了鎖升級，從行級鎖升級到頁級鎖，再從頁級鎖升級成表級鎖。




檢視當前會話sid
SQL> select distinct sid from v$mystat;

查詢兩個會話的鎖資訊
SQL> select sid,id1,id2,type,lmode,request from v$lock where sid in (sid1,sid2) order by sid;

查詢鎖型別的具體含義
SQL> select * from V$LOCK_TYPE where type in ('TX','AE','TM','TO','OD');
TYPE  NAME           ID1_TAG           ID2_TAG           IS_USE DESCRIPTION
----- -------------- ----------------- ----------------- ------ ----------------------------------------------------------------------
TM    DML            object #          table/partition   YES    Synchronizes accesses to an object
TX    Transaction    usn<<16 | slot    sequence          YES    Lock held by a transaction to allow other transactions to wait for it
AE    Edition Lock   edition obj#      0                 NO     Prevent Dropping an edition in use
OD    Online DDLs    object #          0                 NO     Lock to prevent concurrent online DDLs
TO    Temp Object    object #          1                 NO     Synchronizes DDL and DML operations on a temp object



案例1
會話1的sid是161,會話2的sid是189

sid1 不commit
SQL> update test set id=11;
1 row updated
sid2一直建立不成功
SQL> alter table test add hid3 number;
sid3查詢結果，發現sid1和和sid2的表級鎖都是3
SQL>  select sid,id1,id2,type,lmode,request from v$lock where sid in (161,189) order by sid;
       SID        ID1        ID2 TY      LMODE    REQUEST
---------- ---------- ---------- -- ---------- ----------
       161      65547       1930 TX          6          0
       161      88539          0 TM          3          0  --sid1的表級鎖為3
       161        100          0 AE          4          0
       161      79833          1 TO          3          0
       189     196612       2185 TX          6          0
       189      88539          0 TM          3          0  --sid2的表級鎖為3
       189        100          0 AE          4          0
       189      88539          0 OD          6          0
       189      65547       1930 TX          0          4
       189      79833          1 TO          3          0
SQL> select sid,FINAL_BLOCKING_SESSION,event from v$session where state='WAITING' and FINAL_BLOCKING_SESSION_STATUS='VALID';
       SID                FINAL_BLOCKING_SESSION   EVENT
      -----------------   ----------------------   -----------
       189                161                      enq: TX - row lock contention



案例2
會話1的sid是161,會話2的sid是189

sid1不commit
SQL> update test set id=11;
1 row updated
sid2，直接報錯
SQL> drop table test;
drop table test
           *
ERROR at line 1:
ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired
sid3修改ddl後，sid2再執行一次，sid查詢結果
SQL> alter system set ddl_lock_timeout=60
SQL> select sid,id1,id2,type,lmode,request from v$lock where sid in (161,189) order by sid;
       SID        ID1        ID2 TY      LMODE    REQUEST
---------- ---------- ---------- -- ---------- ----------
       161      88539          0 TM          3          0 --sid1的表級鎖為3
       161        100          0 AE          4          0
       161      79833          1 TO          3          0
       161     458768       1934 TX          6          0
       189      88539          0 TM          0          6 --sid2當前表級鎖為0，但是請求表級鎖6
       189        100          0 AE          4          0
       189          0          1 AE          4          0
       189      79833          1 TO          3          0
SQL> select sid,FINAL_BLOCKING_SESSION,event from v$session where state='WAITING' and FINAL_BLOCKING_SESSION_STATUS='VALID';
       SID      FINAL_BLOCKING_SESSION     EVENT
       ---      ----------------------    -------------
       189                    161          enq: TM - contention



CREATE INDEX ONLINE
create index online會堵塞update嗎？
不會
先執行update後不提交，後執行create index online不會報錯，但是create index online一直處於堵塞狀態
先執行create index online後，後執行update正常update，但是如果update不提交，則create index online一直處於堵塞狀態

理解到：create index online在一行行建立索引過程中，並不是說這一行建立好索引了，再對這一行執行update時必須等到所有行都create index online完成後才會正常udpate，也就是說不管update在create index online前還是後，create index online都不影響update，倒是update如果沒有提交會影響create index online。

如下兩個實驗會話1的sid是161,會話2的sid是189
實驗1，先執行create index online,建立到一半後，update最小rowid的一行，按理說create index online應該已經過了這一行，應該會堵塞update會話，實際上並沒有堵塞，update一樣很快，到時最後查詢下來發現update倒是把create index online堵塞了

sid1執行
SQL> select object_id from test1 where rowid in (select min(rowid) from test1);
 OBJECT_ID
----------
      4559
sid2執行，建立正常耗時6秒
SQL> create index ind_obd on test1 (OBJECT_ID) online;
Index created.
Elapsed: 00:00:06.06
SQL> drop index ind_obd;
Index dropped.
Elapsed: 00:00:00.14
SQL> create index ind_obd on test1 (OBJECT_ID) online;
在sid2執行的6秒期間，馬上在sid1執行，發現sid1執行很快，並不堵塞
SQL> update test1 set object_id=1 where OBJECT_ID=4559;
32 rows updated.
sid3執行如下，發現sid1 161堵塞了sid2 189
SQL> select sid,FINAL_BLOCKING_SESSION,event from v$session where state='WAITING' and FINAL_BLOCKING_SESSION_STATUS='VALID';
       SID    FINAL_BLOCKING_SESSION    EVENT
       ----   ----------------------   -------------------------
       189                    161      enq: TX - row lock contention
SQL>  select sid,id1,id2,type,lmode,request from v$lock where sid in (161,189) order by sid;
       SID        ID1        ID2 TY      LMODE    REQUEST
---------- ---------- ---------- -- ---------- ----------
       161      79833          1 TO          3          0
       161     262151       1938 TX          6          0
       161      88544          0 TM          3          0
       161        100          0 AE          4          0
       189        100          0 AE          4          0
       189      79833          1 TO          3          0
       189     131075       2139 TX          6          0
       189      88544          0 DL          3          0
       189     262151       1938 TX          0          4
       189      88552          0 TM          4          0
       189      88544          0 DL          3          0
       189      88544          0 OD          4          0
       189      88544          0 TM          2          0
13 rows selected.

實驗2，先執行create index online,建立到一半後，update最大rowid的一行，按理說create index online應該還沒到這一行，不會堵塞update會話，實驗也發現確實是這樣，update很快，到時最後查詢下來是update把create index online堵塞了

sid1執行
SQL>  select object_id from test1 where rowid in (select max(rowid) from test1);
 OBJECT_ID
----------
      85998
sid2執行，建立正常耗時6秒
SQL> create index ind_obd on test1 (OBJECT_ID) online;
Index created.
Elapsed: 00:00:06.06
SQL> drop index ind_obd;
Index dropped.
Elapsed: 00:00:00.14
SQL> create index ind_obd on test1 (OBJECT_ID) online;
在sid2執行的6秒期間，馬上在sid1執行，發現sid1執行很快，並不堵塞
SQL> update test1 set object_id=1 where OBJECT_ID=85998;
32 rows updated.
sid3執行如下，發現sid1 161堵塞了sid2 189
SQL> select sid,FINAL_BLOCKING_SESSION,event from v$session where state='WAITING' and FINAL_BLOCKING_SESSION_STATUS='VALID';
       SID    FINAL_BLOCKING_SESSION    EVENT
       ----   ----------------------   -------------------------
       189                    161      enq: TX - row lock contention
SQL> select sid,id1,id2,type,lmode,request from v$lock where sid in (161,189) order by sid;
       SID        ID1        ID2 TY      LMODE    REQUEST
---------- ---------- ---------- -- ---------- ----------
       161      79833          1 TO          3          0
       161      88544          0 TM          3          0
       161     393242       2315 TX          6          0
       161        100          0 AE          4          0
       189      79833          1 TO          3          0
       189      88544          0 TM          2          0
       189      88546          0 TM          4          0
       189     458777       1936 TX          6          0
       189        100          0 AE          4          0
       189      88544          0 DL          3          0
       189      88544          0 DL          3          0
       189     393242       2315 TX          0          4
       189      88544          0 OD          4          0
13 rows selected.





查詢鎖物件是哪張表，哪一行的SQL
先查出堵塞的會話的SID，再如下查詢堵塞的是哪張表，行是哪行
select a.sid, a.row_wait_obj#, a.row_wait_file#, a.row_wait_block#, a.row_wait_row#,b.owner,b.object_name from v$session a,dba_objects b where a.row_wait_obj#=b.object_id and sid in (XX);

select sid, row_wait_obj#, row_wait_file#, row_wait_block#, row_wait_row# from v$session where sid in (XX);--此次查詢到row_wait_obj#=-1表示是持有鎖的會話

row_wait_obj#:被等待的這行在哪個物件上
row_wait_file#:被等待的這行在哪個檔案上
row_wait_block#:被等待的這行在哪個塊上
row_wait_row#:被等待的這行在哪行上


統計資訊收集遇到的鎖
DBMS_STATS: GATHER_STATS_JOB encountered errors
ORA-04021: timeout occurred while waiting to lock object

收集統計資訊的時候，需要對錶或者索引的定義進行lock，其實這裡的lock是library cache lock/pin~
不是鎖定這個物件，而當收集某個物件的統計資訊時，發現所需的物件已經被其它會話鎖定，且在等待了一定時間後，其他會話仍然沒有釋放已持有該物件的鎖，導致統計資訊會話無法得到這個物件的鎖。

收集統計資訊會持有X mode的library cache lock（表在library cache裡的representation），所以會有鎖，但不是我們通常理解的enqueue鎖。 
其它使用者在解析用到這個表的SQL時需要申請S mode的表 library cache object的library cache lock，此時就會有衝突/阻塞。