溫馨提示×
您好，登錄后才能下訂單哦！
點擊 登錄注冊 即表示同意《億速云用戶服務條款》
您好，登錄后才能下訂單哦！
【鎖】Oracle鎖系列
各位技術愛好者，看完本文后，你可以掌握如下的技能，也可以學到一些其它你所不知道的知識，~O(∩_∩)O~：
① 鎖的概念、分類、及其模擬
② 查詢鎖的視圖及視圖之間的關聯
③ 鎖的參數（DML_LOCKS、DDL_LOCK_TIMEOUT）
④ FOR UPDATE及FOR UPDATE OF系列
⑤ 帶ONLINE和不帶ONLINE創建索引的鎖情況（是否阻塞DML操作）
⑥ 包或存過不能編譯的解決方法
⑦ ORA-08104解決方法
Tips：
① 本文在itpub（http://blog.itpub.net/26736162）、博客園(http://www.cnblogs.com/lhrbest)和微信公眾號（xiaomaimiaolhr）上有同步更新。
② 文章中用到的所有代碼、相關軟件、相關資料及本文的pdf版本都請前往小麥苗的云盤下載，小麥苗的云盤地址見：http://blog.itpub.net/26736162/viewspace-1624453/。
③ 若網頁文章代碼格式有錯亂，請下載pdf格式的文檔來閱讀。
④ 在本篇BLOG中，代碼輸出部分一般放在一行一列的表格中。其中，需要特別關注的地方我都用灰色背景和粉紅色字體來表示，比如在下邊的例子中，thread 1的最大歸檔日志號為33，thread 2的最大歸檔日志號為43是需要特別關注的地方；而命令一般使用黃色背景和紅色字體標注；對代碼或代碼輸出部分的注釋一般采用藍色字體表示。
|
List of Archived Logs in backup set 11 Thrd Seq Low SCN Low Time Next SCN Next Time ---- ------- ---------- ------------------- ---------- --------- 1 32 1621589 2015-05-29 11:09:52 1625242 2015-05-29 11:15:48 1 33 1625242 2015-05-29 11:15:48 1625293 2015-05-29 11:15:58 2 42 1613951 2015-05-29 10:41:18 1625245 2015-05-29 11:15:49 2 43 1625245 2015-05-29 11:15:49 1625253 2015-05-29 11:15:53 [ZHLHRDB1:root]:/>lsvg -o T_XLHRD_APP1_vg rootvg [ZHLHRDB1:root]:/> 00:27:22 SQL> alter tablespace idxtbs read write; ====》2097152*512/1024/1024/1024=1G |
本文如有錯誤或不完善的地方請大家多多指正，ITPUB留言或QQ皆可，您的批評指正是我寫作的最大動力。
有網友一直催著說發一些鎖系列的文章，其實小麥苗一直對鎖這塊也沒有徹底去研究過，今年寫書里邊寫到了鎖的內容，干脆就徹底把這一塊整理了一下，現在分享給大家，若有錯誤，還請大家及時指正。
文章很多內容來源于網絡或Concepts的內容，若有侵權還請聯系小麥苗刪除。
鎖的定義：鎖（lock）機制用于管理對共享資源的并發訪問，用于多用戶的環境下，可以保證數據庫的完整性和一致性。鎖是防止訪問相同資源的事務之間的破壞性交互的機制。既可以是用戶對象(例如表或行)，也可以是對用戶不可見的系統對象(例如共享數據結構和數據字典行)。
鎖的解釋：當多個用戶并發地存取數據時，在數據庫中就會產生多個事務同時存取同一數據的情況。若對并發操作不加控制就可能會讀取和存儲不正確的數據，破壞數據庫的完整性和一致性。當事務在對某個數據對象進行操作前，先向系統發出請求，對其加鎖。加鎖后事務就對該數據對象有了一定的控制。
鎖的作用：在并發事務之間防止破壞性的交互作用，不需要用戶的動作，自動使用最低的限制級別，在事務處理期間保持。
數據庫是一個多用戶使用的共享資源。當多個用戶并發地存取數據時，在數據庫中就會產生多個事務同時存取同一數據的情況。若對并發操作不加控制就可能會讀取和存儲不正確的數據，破壞數據庫的一致性。
鎖（lock）是防止訪問相同資源（例如表或數據行等用戶對象，或內存中的共享數據結構及數據字典等對用戶不可見的系統對象）的事務產生破壞性交互的機制。
在任何情況下，Oracle 都能夠自動地獲得執行 SQL 語句所必須的所有鎖，無需用戶干預。Oracle 會盡可能地減少鎖產生的影響，從而最大程度地保證數據的并發訪問能力，并確保數據一致性及錯誤恢復。同時，Oracle 也支持用戶手工加鎖的操作。
Oracle 從來不會升級鎖，但是它會執行鎖轉換（lock conversion）或鎖提升（lock promotion）。
A lock is a mechanism that prevents destructive interactions, which are interactions that incorrectly update data or incorrectly alter underlying data structures, between transactions accessing shared data. Locks play a crucial row in maintaining database concurrency and consistency.
鎖是一種機制，用來防止多個共同訪問共享數據的事務之間的破壞性交互，包括不正確地更新數據或不正確地更改基礎數據結構。鎖在維護數據庫并發性和一致性當中扮演著一個關鍵的角色。
并發(concurrency)和并行(parallel)。并發意思是在數據庫中有超過兩個以上用戶對同樣的數據做修改，而并行的意思就是將一個任務分成很多小的任務，讓每一個小任務同時執行，最后將結果匯總到一起。所以說，鎖產生的原因就是并發，并發產生的原因是因為系統和客戶的需要。
在單用戶數據庫中，鎖不是必需的，因為只有一個用戶在修改信息。但是，當多個用戶在訪問和修改數據時，數據庫必須提供一種方法，以防止對同一數據進行并發修改。鎖實現了以下重要的數據庫需求：
v ·一致性
一個會話正在查看或更改的數據不能被其它會話更改，直到用戶會話結束。
v ·完整性
數據和結構必須按正確的順序反映對他們所做的所有更改。數據庫通過其鎖定機制，提供在多個事務之間的數據并發性、一致性、和完整性。鎖定將自動執行，并且不需要用戶操作。
執行SQL語句時，Oracle數據庫自動獲取所需的鎖。例如，在數據庫允許某個會話修改數據之前，該會話必須先鎖定數據。鎖給予該會話對數據的獨占控制權，以便在釋放該鎖之前，任何其它事務都不可以修改被鎖定的數據。
因為數據庫的鎖定機制與事務控制緊密地綁定在一起，應用程序設計人員只需要正確地定義事務，而數據庫會自動管理鎖定。
Oracle數據庫自動使用最低適用的限制級別，來提供最高程度的數據并發，但還能提供非常安全的數據完整性。限制級別越低、則有更多的可用數據供其他用戶訪問。相反，限制級別越高，則其它事務為獲取其所需的鎖類型就將遭受更多的限制。
在多用戶的數據庫系統中，Oracle使用兩種模式的鎖：
事務內各語句獲得的鎖在事務執行期內有效，以防止事務間破壞性的相互干擾，例如：臟讀取（dirty read），無效地更新（lost update），以及其它并發事務中具有破壞性的 DDL 操作。如果某個事務中的 SQL 語句對數據進行了修改，只有在此事務提交后開始的事務才能看到前者修改的結果。
當用戶提交（commit）或撤銷（undo）一個事務后，Oracle 將釋放此事務內各個 SQL 語句獲得的鎖。當用戶在事務內回滾到某個保存點（savepoint）后，Oracle 也會釋放此保存點后獲得的鎖。只有當前沒有等待被鎖資源的事務才能獲得可用資源的鎖。等待事務不會對可用資源加鎖而是繼續等待，直至擁有其所等待資源的事務完成提交或回滾。
有兩種類型：顯式鎖定和隱式鎖定。Oracle鎖被自動執行，并且不要求用戶干預的鎖為隱式鎖。對于SQL語句隱式鎖是必須的，依賴被請求的動作。隱式鎖定除SELECT外，對所有的SQL語句都發生。用戶也可以手動鎖定數據，這是顯式鎖定。
隱式鎖定：這是Oracle中使用最多的鎖。通常用戶不必聲明要對誰加鎖，Oracle 自動可以為操作的對象加鎖，這就是隱式鎖定。
顯式鎖定：用戶可以使用命令明確的要求對某一對象加鎖。顯式鎖定很少使用。
LOCK TABLE沒有觸發行鎖，只有TM表鎖。
|
LOCK TABLE TABLE_NAME IN ROW SHARE MODE NOWAIT; --2：RS LOCK TABLE TABLE_NAME IN SHARE UPDATE MODE; --2：RS LOCK TABLE TABLE_NAME IN ROW EXCLUSIVE MODE NOWAIT; --3：RX LOCK TABLE TABLE_NAME IN SHARE MODE; --4：S LOCK TABLE TABLE_NAME IN SHARE ROW EXCLUSIVE MODE; --5：SRX LOCK TABLE TABLE_NAME IN EXCLUSIVE MODE NOWAIT; --6：X |
隱式鎖定：
Select * from table_name……
Insert into table_name……
Update table_name……
Delete from table_name……
Select * from table_name for update
鎖在用戶修改之前就發揮作用：
Select ..for update（nowait)
Select * from tab1 for update
用戶發出這條命令之后，oracle將會對返回集中的數據建立行級封鎖，以防止其他用戶的修改。
如果此時其他用戶對上面返回結果集的數據進行dml或ddl操作都會返回一個錯誤信息或發生阻塞。
1：對返回結果集進行update或delete操作會發生阻塞。
2：對該表進行ddl操作將會報：Ora-00054:resource busy and acquire with nowait specified.
原因分析
此時Oracle已經對返回的結果集上加了排它的行級鎖，所有其他對這些數據進行的修改或刪除操作都必須等待這個鎖的釋放，產生的外在現象就是其它的操作將發生阻塞，這個這個操作commit或rollback.
同樣這個查詢的事務將會對該表加表級鎖，不允許對該表的任何ddl操作，否則將會報出ora-00054錯誤：:resource busy and acquire with nowait specified.
會話1：
|
SYS@lhrdb S1> create table t_lock_lhr as select rownum as id,0 as type from dual connect by rownum <=3;
Table created.
SYS@lhrdb S1> select * from t_lock_lhr where id=2 and type =0 for update nowait;
ID TYPE ---------- ---------- 2 0 |
會話2：
|
SYS@lhrdb S2> select * from t_lock_lhr where id=2 and type=0 for update nowait; select * from t_lock_lhr where id=2 and type=0 for update nowait * ERROR at line 1: ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired
|
會話1：
|
SYS@lhrdb S1> update t_lock_lhr set type=1 where id=2 and type=0;
1 row updated.
SYS@lhrdb S1> commit;
Commit complete.
SYS@lhrdb S1> select * from t_lock_lhr where id=2;
ID TYPE ---------- ---------- 2 1
|
會話2：
|
SYS@lhrdb S2> select * from t_lock_lhr where id=2 and type=0 for update nowait;
no rows selected
|
樂觀的認為數據在select出來到update進取并提交的這段時間數據不會被更改。這里面有一種潛在的危險就是由于被選出的結果集并沒有被鎖定，是存在一種可能被其他用戶更改的可能。因此Oracle仍然建議是用悲觀封鎖，因為這樣會更安全。
|
會話1： SYS@lhrdb S1> select id,type,ora_rowscn from t_lock_lhr where id = 3;
ID TYPE ORA_ROWSCN ---------- ---------- ---------- 3 0 37698547 會話2： SYS@lhrdb S2> select id,type,ora_rowscn from t_lock_lhr where id = 3;
ID TYPE ORA_ROWSCN ---------- ---------- ---------- 3 0 37698547 會話1： SYS@lhrdb S1> update t_lock_lhr set type=1 where ora_rowscn=37698547 and id = 3;
1 row updated.
SYS@lhrdb S1> commit;
Commit complete. SYS@lhrdb S1> select id,type,ora_rowscn from t_lock_lhr where id = 3;
ID TYPE ORA_ROWSCN ---------- ---------- ---------- 3 1 37698591 會話2： SYS@lhrdb S2> update t_lock_lhr set type=1 where ora_rowscn=37698547 and id =3;
0 rows updated.
SYS@lhrdb S2> select id,type,ora_rowscn from t_lock_lhr where id = 3;
ID TYPE ORA_ROWSCN ---------- ---------- ---------- 3 1 37698591
|
更新丟失是指多個用戶通過應用程序訪問數據庫時，由于查詢數據并返回到頁面和用戶修改完畢點擊保存按鈕將修改后的結果保存到數據庫這個時間段（即修改數據在頁面上停留的時間）在不同用戶之間可能存在偏差，從而最先查詢數據并且最后提交數據的用戶會把其他用戶所作的修改覆蓋掉。
解決方法如下：
數據庫在必要時執行鎖轉換。在鎖轉換中，數據庫自動將較低限制的表鎖轉換為較高限制的其它鎖定。一個事務在該事務中所有執行插入、更新、或刪除的行上持有行獨占鎖。因為行鎖是在最高程度限制下獲得的，因此不要求鎖轉換，也不執行鎖轉換。鎖轉換不同于鎖升級，鎖升級發生在當某個粒度級別持有許多鎖（例如行），數據庫將其提高到更高粒度級別（例如表）。如果一個用戶鎖定了一個表中的許多行，則某些數據庫自動將行鎖升級到單個表鎖。鎖的數量減少了，但被鎖定的東西卻增加了。
Oracle數據庫永遠不會升級鎖。鎖升級極大地增加了死鎖的可能性。假定一個系統嘗試升級事務1中的鎖，但因為事務2持有該鎖，故不能成功。如果事務2在它可以繼續操作之前也需要在相同的數據上進行鎖升級，則將發生一個死鎖。
ORACLE的鎖是block里面實現的,SQLSERVER,DB2是內存里面實現的.內存實現有資源消耗問題,當內存不足會引發鎖升級,但是ORACLE不會發生鎖升級。
事務擁有在此事務內被插入（insert），更新（update），刪除（delete）的數據行的排它行級鎖（exclusive row lock）。對于數據行來說，排它行級鎖已經是限制程度最高的鎖，因此無需再進行鎖轉換（lock conversion）。
Oracle能夠自動地選擇不同類型的鎖對數據并發訪問進行控制，防止用戶間破壞性的交互操作。Oracle 將自動地為事務進行鎖管理，防止其它事務對需要排它訪問的資源執行操作。當事務不再需要加鎖的資源并觸發某個事件后，鎖能夠被自動地釋放。
在事務執行期間，Oracle 能夠根據加鎖的資源及需要執行的操作自動地決定鎖的類型（types of lock）及對資源的限制級別（level of restrictiveness）。
V$LOCK_TYPE 該視圖是對DML鎖的類型的解釋。
select * from V$LOCK_TYPE v where v.IS_USER='YES';
當Oracle執行DML語句時，系統自動在所要操作的表上申請TM類型的鎖。當TM鎖獲得后，系統再自動申請TX類型的鎖，并將實際鎖定的數據行的鎖標志位進行置位。這樣在事務加鎖前檢查TX鎖相容性時就不用再逐行檢查鎖標志，而只需檢查TM鎖模式的相容性即可，大大提高了系統的效率。TM鎖包括了SS、SX、S、X等多種模式，在數據庫中用0－6來表示。不同的SQL操作產生不同類型的TM鎖。
在數據行上只有X鎖（排它鎖）。在Oracle數據庫中，當一個事務首次發起一個DML語句時就獲得一個TX鎖，該鎖保持到事務被提交或回滾。當兩個或多個會話在表的同一條記錄上執行DML語句時，第一個會話在該條記錄上加鎖，其它的會話處于等待狀態。當第一個會話提交后，TX鎖被釋放，其它會話才可以加鎖。
當Oracle數據庫發生TX鎖等待時，如果不及時處理常常會引起Oracle數據庫掛起，或導致死鎖的發生，產生ORA-60的錯誤。這些現象都會對實際應用產生極大的危害，如長時間未響應，大量事務失敗等。
當Oracle執行DELETE,UPDATE,INSERT,SELECT FOR UPDATE DML語句時，oracle首先自動在所要操作的表上申請TM類型的鎖。當TM鎖獲得后，再自動申請TX類型的鎖，并將實際鎖定的數據行的鎖標志位(lb 即lock bytes)進行置位。在記錄被某一會話鎖定后，其它需要訪問被鎖定對象的會話會按先進先出的方式等待鎖的釋放，對于select操作而言，并不需要任何鎖，所以即使記錄被鎖定，select語句依然可以執行，實際上，在此情況下，oracle是用到undo的內容進行一致性讀來實現的。
當Oracle執行DML語句時，系統自動在所要操作的表上申請TM類型的鎖。當TM鎖獲得后，系統再自動申請TX類型的鎖，并將實際鎖定的數據行的鎖標志位進行置位。這樣在事務加鎖前檢查TX鎖相容性時就不用再逐行檢查鎖標志，而只需檢查TM鎖模式的相容性即可，大大提高了系統的效率。DML語句能夠自動地獲得所需的表級鎖（table-level lock）與行級鎖（row-level lock）。
DML鎖，也稱為數據鎖，確保由多個用戶并發訪問的數據的完整性。例如，DML鎖可防止兩個客戶從一個在線書店購買某一本書所剩的最后一個拷貝。DML鎖也可以防止多個相互沖突的DML或DDL操作產生破壞性干擾。
DML語句自動獲取下列類型的鎖：
n 行鎖(TX)
n 表鎖(TM)
行級鎖（row-level lock）的作用是防止兩個事務同時修改相同的數據行。當一個事務需要修改一行數據時，就需對此行數據加鎖。Oracle 對語句或事務所能獲得的行級鎖的數量沒有限制，Oracle 也不會講行級鎖的粒度升級（lock escalation）。行級鎖是粒度最精細的鎖，因此行級鎖能夠提供最好的數據并發訪問能力及數據處理能力。
Oracle 同時支持多版本并發訪問控制（multiversion concurrency control）及行級鎖技術（row-level locking），因此用戶只有在訪問相同數據行時才會出現競爭，具體來說：
l 讀取操作無需等待對相同數據行的寫入操作。
l 寫入操作無需等待對相同數據行的讀取操作，除非讀取操作使用了 SELECT ... FOR UPDATE 語句，此讀取語句需要對數據加鎖。
l 寫入操作只需等待并發地且針對相同數據行的其它寫入操作。
提示：讀取操作可能會等待對相同數據塊（data block）的寫入操作，這種情況只會在出現掛起的分布式事務（pending distributed transaction）時偶爾出現。
在執行下列語句時，事務需要獲得被修改的每一數據行的排它行級鎖（exclusive row lock）：INSERT，UPDATE，DELETE，及使用了FOR UPDATE 子句的 SELECT 語句。
在事務被提交或回滾前，此事務擁有在其內部被修改的所有數據行的排它鎖，其它事務不能對這些數據行進行修改操作。但是，如果事務由于實例故障而終止，在整個事務被恢復前，數據塊級的恢復將使數據塊內數據行上的鎖釋放。執行前面提到的 4 種 SQL 語句時，Oracle 能自動地對行級鎖進行管理。
當事務獲得了某些數據行上的行級鎖時，此事務同時獲得了數據行所屬表上的表級鎖（table lock）。表級鎖能夠防止系統中并發地執行有沖突的 DDL 操作，避免當前事務中的數據操作被并發地 DDL 操作影響。
行級鎖機制：
當一個事務開始時，必須申請一個TX鎖，這種鎖保護的資源是回滾段、回滾數據塊。因此申請也就意味著：用戶進程必須先申請到回滾段資源后才開始一個事務，才能執行DML操作。申請到回滾段后，用戶事務就可以修改數據了。具體順序如下：
1、首先獲得TM鎖，保護事務執行時，其他用戶不能修改表結構
2、事務修改某個數據塊中記錄時，該數據塊頭部的ITL表中申請一個空閑表項，在其中記錄事務項號，實際就是記錄這個事務要使用的回滾段的地址（應該叫包含）
3、事務修改數據塊中的某條記錄時，會設置記錄頭部的ITL索引指向上一步申請到的表項。然后修改記錄。修改前先在回滾段將記錄之前的狀態做一個拷貝，然后修改表中數據。
4、其他用戶并發修改這條記錄時，會根據記錄頭部ITL索引讀取ITL表項內容，確認是否事務提交。
5、若沒有提交，必須等待TX鎖釋放
從上面的機制來看，無論一個事務修改多少條記錄，都只需要一個TX鎖。所謂的“行級鎖”其實也就是數據塊頭、數據記錄頭的一些字段，不會消耗額外的資源。 從另一方面也證明了，當用戶被阻塞時，不是被某條記錄阻塞，而是被TX鎖堵塞。也正因為這點，很多人也傾向把TX鎖稱為事務鎖。這里可通過實驗來驗證所說 結論。
會話1：
|
SQL> select * from test; ID NAME ---------- -------- 1 A 2 B 3 C
SQL> savepoint a; Savepoint created.
SQL> update test set name='ssss' where id=2; 1 row updated.
|
會話2，更新同一行發生阻塞：
|
SQL> update test set name='ssdsdsds'where id=2;
|
會話1：
|
SQL> rollback to a; Rollback complete.
|
可以看到，雖然會話1已經撤銷了對記錄的修改，但是會話2仍然處于等待狀態這是因為會話2是被會話1的TX鎖阻塞的，而不是被會話1上的行級鎖 阻塞(rollback to savepoint不會結束事務) 。
|
會話3： SQL> select username,event,sid,blocking_session from v$session where SID IN (146,159); USERNAME EVENT SID BLOCKING_SESSION -------- ----------------------------------- ---------- ---------------- HR enq: TX - row lock contention 146 159 HR SQL*Net message from client 159 會話1： SQL> rollback; 會話2： SQL> update test set name='ssdsdsds'where id=2; 1 row updated. 會話3： SQL> select username,event,sid,blocking_session from v$session where username='HR'; USERNAME EVENT SID BLOCKING_SESSION -------- ----------------------------------- ---------- ---------------- HR SQL*Net message from client 159 |
事務結束，tx鎖釋放，會話2update執行成功。
行鎖，也稱為TX 鎖，是一個表中單個行上的鎖。一個事務在被INSERT、UPDATE、DELETE、MERGE、或SELECT ... FOR UPDATE 等語句所修改的每一行上獲取一個行鎖。行鎖一直存在直到事務提交或回滾。行鎖主要作為一種排隊的機制，以防止兩個事務修改相同的行。數據庫始終以獨占模式鎖定修改的行，以便其它事務不能修改該行，直到持有鎖的事務提交或回滾。行鎖定提供了近乎最細粒度的鎖定，并因此提供了近乎最佳的并發性和吞吐量。
如果一個事務因為數據庫實例失效而終止，會先進行塊級恢復以使行可用，之后進行整個事務恢復。
表級鎖（table-level lock）的作用是對并發的 DDL 操作進行訪問控制，例如防止在 DML 語句執行期間相關的表被移除。當用戶對表執行 DDL 或 DML 操作時，將獲取一個此表的表級鎖。表級鎖不會影響其他并發的 DML 操作。對于分區表來說，表級鎖既可以針對整個表，也可以只針對某個分區。
當用戶執行以下 DML 語句對表進行修改：INSERT，UPDATE，DELETE，及 SELECT ... FOR UPDATE，或執行 LOCK TABLE 語句時，事務將獲取一個表級鎖。這些 DML 語句獲取表級鎖的目的有兩個：首先保證自身對表的訪問不受其它事務 DML 語句的干擾，其次阻止其它事務中和自身有沖突的 DDL 操作執行。任何類型的表級鎖都將阻止對此表的排它 DDL 鎖（exclusive DDL lock），從而阻止了必須具備排它 DDL 鎖才能執行的 DDL 操作。例如，當一個未提交的事務擁有某個表上的鎖時，此表就無法被修改定義或被移除。
表級鎖具有以下幾種模式：行共享（row share，RS），行排它（row exclusive，RX），共享（share，S），共享行排它（share row exclusive，SRX），及排它（exclusive，X）。各種模式的表級鎖具有的限制級別決定了其是否能與其他表級鎖共處于同一數據表上。
下表顯示了各種語句所獲得的表級鎖的模式，以及此模式下被允許或禁止的操作。
ORACLE里鎖有以下幾種模式:
鎖的兼容模式如下表所示：
表鎖，也稱為TM鎖，當一個表被INSERT、UPDATE、DELETE、MERGE、帶FOR UPDATE子句的SELECT等修改時，由相關事務獲取該鎖。DML操作需要表鎖來為事務保護DML對表的訪問，并防止可能與事務沖突的DDL操作。
表鎖可能以下列模式之一持有：
這種鎖也被稱為子共享表鎖(SS，subshare table lock)，表示在表上持有鎖的事務在表中有被鎖定的行，并打算更新它們。行共享鎖是限制最少的表級鎖模式，提供在表上最高程度的并發性。
1、 實驗ROW SHARE模式允許同時訪問被鎖定的表，但是禁止用戶以排它方式鎖定整個表。ROW SHARE與SHARE UPDATE相同，只是為了兼容早期的Oracle版本。對應lmode2，row-S (SS)。
版本：11.2.0.4
|
會話1： SYS@lhrdb> set sqlprompt "_user'@'_connect_identifier S1> " SYS@lhrdb S1> select userenv('sid') from dual;
USERENV('SID') -------------- 6
SYS@lhrdb S1> LOCK TABLE SCOTT.EMP IN ROW SHARE MODE;
Table(s) Locked. 會話2： SYS@lhrdb> set sqlprompt "_user'@'_connect_identifier S2> " SYS@lhrdb S2> select userenv('sid') from dual;
USERENV('SID') -------------- 114
SYS@lhrdb S2> LOCK TABLE SCOTT.EMP IN EXCLUSIVE MODE;
====>>>>> 產生了阻塞 查詢2個會話的鎖： SYS@lhrdb S1> SELECT D.SID, D.TYPE, D.ID1, D.ID2, D.LMODE, D.REQUEST, D.CTIME, D.BLOCK 2 FROM V$LOCK D 3 WHERE D.SID IN (114, 6) 4 ORDER BY D.SID, D.TYPE; SID TY ID1 ID2 LMODE REQUEST CTIME BLOCK ---------- -- ---------- ---------- ---------- ---------- ---------- ---------- 6 AE 100 0 4 0 231 0 6 TM 86893 0 2 0 169 1 114 AE 100 0 4 0 378 0 114 TM 86893 0 0 6 144 0 114 TO 79619 1 3 0 376 0 |
SELECT D.SID, D.TYPE, D.ID1, D.ID2, D.LMODE, D.REQUEST, D.CTIME, D.BLOCK
FROM V$LOCK D
WHERE D.SID IN (114, 6)
ORDER BY D.SID, D.TYPE;
由BLOCK列可以看到sid為6的會話阻塞了一個會話，這里其實就是114，而114正在請求模式為6的鎖。將2個會話提交后繼續測試：
|
SYS@lhrdb S1> LOCK TABLE SCOTT.EMP IN SHARE UPDATE MODE;
Table(s) Locked.
SYS@lhrdb S1> SELECT D.SID, D.TYPE, D.ID1, D.ID2, D.LMODE, D.REQUEST, D.CTIME, D.BLOCK 2 FROM V$LOCK D 3 WHERE D.SID IN (114, 6) 4 AND D.TYPE = 'TM' 5 ORDER BY D.SID, D.TYPE;
SID TY ID1 ID2 LMODE REQUEST CTIME BLOCK ---------- -- ---------- ---------- ---------- ---------- ---------- ---------- 6 TM 86893 0 2 0 387 0
|
二、 行獨占表鎖 Row Exclusive Table Lock (RX)
這種鎖也被稱為子獨占表鎖（SX，subexclusive table lock），通常表示持有鎖的事務已更新了表行或發出了SELECT...FOR UPDATE。一個SX鎖允許其它事務并發地查詢、插入、更新、刪除、或鎖定在同一個表中的其它行。因此，SX鎖允許多個事務對同一個表同時獲得SX和子共享表鎖。
ROW EXCLUSIE類似于ROW SHARE模式，但是不能應用在SHARE模式中。當update,insert,delete發生時，ROW EXCLUSIVE會自動獲得。對應lmode3，row-X (SX) 。
1、 實驗實驗內容：but it also prohibits locking in SHARE mode
|
會話1： SQL> set sqlprompt "_user'@'_connect_identifier S1> " SYS@oratest S1> select distinct sid from v$mystat;
SID ---------- 21 SYS@oratest S1> lock table scott.emp in share mode;
Table(s) Locked.
會話2： SQL> set sqlprompt "_user'@'_connect_identifier S2> " SYS@oratest S2> select distinct sid from v$mystat;
SID ---------- 142
SYS@oratest S2> lock table scott.emp in row exclusive mode;
====>>>>> 產生了阻塞
查看鎖： SYS@oratest S1> set line 9999 SYS@oratest S1> select * from v$lock where sid in (21,142);
ADDR KADDR SID TY ID1 ID2 LMODE REQUEST CTIME BLOCK ---------------- ---------------- ---------- -- ---------- ---------- ---------- ---------- ---------- ---------- 00000000774D8518 00000000774D8570 142 TO 68064 1 3 0 7021 0 00000000774D9870 00000000774D98C8 142 TO 76985 1 3 0 7365 0 00000000774D9DC8 00000000774D9E20 21 AE 100 0 4 0 162 0 00000000774DA068 00000000774DA0C0 142 AE 100 0 4 0 7379 0 00007F567ADC2700 00007F567ADC2760 142 TM 75335 0 0 3 36 0 00007F567ADC2700 00007F567ADC2760 21 TM 75335 0 4 0 58 1
6 rows selected.
SYS@oratest S1> select * from v$lock where sid in (21,142) AND TYPE IN ('TX','TM');
ADDR KADDR SID TY ID1 ID2 LMODE REQUEST CTIME BLOCK ---------------- ---------------- ---------- -- ---------- ---------- ---------- ---------- ---------- ---------- 00007F567ADC7818 00007F567ADC7878 142 TM 75335 0 0 3 76 0 00007F567ADC7818 00007F567ADC7878 21 TM 75335 0 4 0 98 1
SYS@oratest S1> SELECT * FROM DBA_DML_LOCKS;
SESSION_ID OWNER NAME MODE_HELD MODE_REQUESTE LAST_CONVERT BLOCKING_OTHERS ---------- ------------------------------ ------------------------------ ------------- ------------- ------------ ---------------------------------------- 142 SCOTT EMP None Row-X (SX) 101 Not Blocking 21 SCOTT EMP Share None 123 Blocking
SYS@oratest S1>
|
這里可以看到會話1的TM4阻塞了會話2的TM3。
提交2個會話后，接著實驗：ROW EXCLUSIVE locks are automatically obtained when updating, inserting, or deleting.
|
SYS@oratest S1> update scott.emp set sal=sal where empno=7369;
1 row updated.
SYS@oratest S1> select * from v$lock where sid in (21,142) AND TYPE IN ('TX','TM');
ADDR KADDR SID TY ID1 ID2 LMODE REQUEST CTIME BLOCK ---------------- ---------------- ---------- -- ---------- ---------- ---------- ---------- ---------- ---------- 00007F567ADE6AC8 00007F567ADE6B28 21 TM 75335 0 3 0 4 0 0000000076227AB0 0000000076227B28 21 TX 196620 1097 6 0 4 0
|
當會話1做了修改而沒有commit或者rollback時，這里有兩個鎖，其中一個就是TM3的，一個是TX6的。
三、 共享表鎖 Share Table Lock (S)
由某個事務擁有的共享表鎖允許其它事務查詢（而不使用SELECT...FOR UPDATE)，但是更新操作只能在僅有單個事務持有共享表鎖時才允許。因為可能有多個事務同時持有共享表鎖，所以持有此鎖不足以確保一個事務可以修改該表。
SHARE允許同時查詢，但是禁止更新被鎖定的表。對應lmode4，share (S) 。
1、 實驗|
會話1： SQL> set sqlprompt "_user'@'_connect_identifier S1> " SYS@oratest S1> select distinct sid from v$mystat;
SID ---------- 21 SYS@oratest S1> lock table scott.emp in share mode;
Table(s) Locked.
會話2： SQL> set sqlprompt "_user'@'_connect_identifier S2> " SYS@oratest S2> select distinct sid from v$mystat;
SID ---------- 142
SYS@oratest S2> update scott.emp set sal=sal where empno=7369;
====>>>>> 產生了阻塞
查看鎖： SYS@oratest S1> select * from v$lock where sid in (21,142) AND TYPE IN ('TX','TM');
ADDR KADDR SID TY ID1 ID2 LMODE REQUEST CTIME BLOCK ---------------- ---------------- ---------- -- ---------- ---------- ---------- ---------- ---------- ---------- 00007F567ADE6AC8 00007F567ADE6B28 142 TM 75335 0 0 3 43 0 00007F567ADE6AC8 00007F567ADE6B28 21 TM 75335 0 4 0 62 1
SYS@oratest S1> SELECT * FROM DBA_DML_LOCKS;
SESSION_ID OWNER NAME MODE_HELD MODE_REQUESTE LAST_CONVERT BLOCKING_OTHERS ---------- -------- ------ ------------- ------------- ------------ --------------- 142 SCOTT EMP None Row-X (SX) 113 Not Blocking 21 SCOTT EMP Share None 132 Blocking SYS@oratest S1>
|
這里可以看到會話1的TM4阻塞了會話2的TM3。
四、 共享行獨占表鎖 Share Row Exclusive Table Lock (SRX)
這種鎖也稱為共享子獨占表鎖（SSX，share-subexclusive table lock），比共享表鎖的限制性更強。一次只能有一個事務可以獲取給定的表上的SSX鎖。由某個事務擁有的SSX鎖允許其它事務查詢該表（除SELECT...FOR UPDATE）但不能更新該表。
共享行級排它鎖有時也稱共享子排它鎖（Share Subexclusive Table Lock，SSX），它比共享鎖有更多限制。定義共享行級排它鎖的語法為：
Lock Table TableName In Share Row Exclusive Mode;
1、 實驗|
會話1： SQL> set sqlprompt "_user'@'_connect_identifier S1> " SYS@oratest S1> select distinct sid from v$mystat;
SID ---------- 21 SYS@oratest S1> lock table scott.emp in share row exclusive mode;
Table(s) Locked.
會話2： SQL> set sqlprompt "_user'@'_connect_identifier S2> " SYS@oratest S2> select distinct sid from v$mystat;
SID ---------- 142
SYS@oratest S2> lock table scott.emp in share mode;
====>>>>> 產生了阻塞
查看鎖： SYS@oratest S1> select * from v$lock where sid in (21,142) AND TYPE IN ('TX','TM');
ADDR KADDR SID TY ID1 ID2 LMODE REQUEST CTIME BLOCK ---------------- ---------------- ---------- -- ---------- ---------- ---------- ---------- ---------- ---------- 00007F567ADE7B00 00007F567ADE7B60 142 TM 75335 0 0 4 21 0 00007F567ADE7B00 00007F567ADE7B60 21 TM 75335 0 5 0 69 1 SYS@oratest S1> SELECT * FROM DBA_DML_LOCKS;
SESSION_ID OWNER NAME MODE_HELD MODE_REQUESTE LAST_CONVERT BLOCKING_OTHERS ---------- ------- ------ ------------- ------------- ------------ --------------- 142 SCOTT EMP None Share 44 Not Blocking 21 SCOTT EMP S/Row-X (SSX) None 92 Blocking |
這里可以看到會話1的TM5阻塞了會話2的TM4。
五、 獨占表鎖 Exclusive Table Lock (X)
這種鎖是最嚴格的鎖，禁止其它事務執行任何類型的DML語句，或在表上放置任何類型的鎖。
EXCLUSIVE EXCLUSIVE permits queries on the locked table but prohibits any other activity on it.
EXCLUSIVE模式允許查詢被鎖表上的數據，但是禁止任何其他任何活動（這里我理解是禁止添加其他任何模式的鎖）。對應lomde6，exclusive (X) 。
1、 實驗|
會話1： SQL> set sqlprompt "_user'@'_connect_identifier S1> " SYS@oratest S1> select distinct sid from v$mystat;
SID ---------- 21 SYS@oratest S1> CREATE TABLE SCOTT.EMP_01 AS SELECT * FROM SCOTT.EMP;
Table created.
SYS@oratest S1> update scott.emp_01 set sal=sal where empno=7369;
1 row updated.
會話2： SQL> set sqlprompt "_user'@'_connect_identifier S2> " SYS@oratest S2> select distinct sid from v$mystat;
SID ---------- 142
SYS@oratest S2> DELETE FROM scott.emp_01 where empno=7369;
====>>>>> 產生了阻塞
查看鎖： SYS@oratest S1> select * from v$lock where sid in (21,142) AND TYPE IN ('TX','TM');
ADDR KADDR SID TY ID1 ID2 LMODE REQUEST CTIME BLOCK ---------------- ---------------- ---------- -- ---------- ---------- ---------- ---------- ---------- ---------- 00000000774D9EA8 00000000774D9F00 142 TX 393247 1337 0 6 28 0 00007F567ABBC0A0 00007F567ABBC100 142 TM 77624 0 3 0 28 0 00007F567ABBC0A0 00007F567ABBC100 21 TM 77624 0 3 0 36 0 0000000076255548 00000000762555C0 21 TX 393247 1337 6 0 36 1
SYS@oratest S1> SELECT * FROM DBA_DML_LOCKS;
SESSION_ID OWNER NAME MODE_HELD MODE_REQUESTE LAST_CONVERT BLOCKING_OTHERS ---------- -------- -------- ------------- ------------- ------------ --------------- 142 SCOTT EMP_01 Row-X (SX) None 35 Not Blocking 21 SCOTT EMP_01 Row-X (SX) None 43 Not Blocking |
在這里，從BLOCK字段可以看到會話1的TM3并沒堵塞會話2的TM3，這里真正發生堵塞的是會話1的TX6。
這里還有一個鎖定對象的問題。上面兩個TM3的鎖針對的對象是object_id為77624的表，既然描述是類似行共享，自然是不會堵塞的。而兩個TX6的鎖針對的對象可以理解成表中的行，在這些行上添加EXCLUSIVE鎖（lmode6，exclusive (X) )自然是會堵塞其他的EXCLUSIVE鎖的。
解決這種類型的鎖堵塞當然就是在代碼中盡早commit結束事務。很多地方都寫到盡早commit可以提高運行效率，這里所指的是釋放鎖（特別是lmode6的EXCLUSIVE鎖）減少堵塞，以提高并發性。（不是以減少數據的量來提高效率的，事實上不管多大的數據量，一個commit的過程都是很"平"的。
2、 INSERT /*+APPEND*/ INTO加6級TM和TX獨占鎖
|
會話1： SYS@lhrdb> set sqlprompt "_user'@'_connect_identifier S1> " SYS@lhrdb S1> SELECT DISTINCT SID FROM V$MYSTAT;
SID ---------- 27 SYS@lhrdb S1> CREATE TABLE T_APPEND_161107_LHR AS SELECT * FROM DUAL;
Table created.
SYS@lhrdb S1> INSERT /*+ APPEND */ INTO T_APPEND_161107_LHR SELECT * FROM DUAL;
3 rows created.
會話2： SYS@lhrdb> set sqlprompt "_user'@'_connect_identifier S2> " SYS@lhrdb S2> SELECT DISTINCT SID FROM V$MYSTAT;
SID ---------- 162
SYS@lhrdb S2> INSERT /*+ APPEND */ INTO T_APPEND_161107_LHR SELECT * FROM DUAL;
<<<<<<<<<-------- 產生了阻塞
|
會話3：
|
SYS@lhrdb> set sqlprompt "_user'@'_connect_identifier S3> " SYS@lhrdb S3> set line 9999 SYS@lhrdb S3> SELECT * FROM V$LOCK T WHERE T.SID IN (27,162) AND T.TYPE IN ('TX','TM') ORDER BY T.SID ;
ADDR KADDR SID TY ID1 ID2 LMODE REQUEST CTIME BLOCK ---------------- ---------------- ---------- -- ---------- ---------- ---------- ---------- ---------- ---------- 00000001109F5A40 00000001109F5AA0 27 TM 100957 0 6 0 2217 1 070001007C7EB2B0 070001007C7EB328 27 TX 589843 58249 6 0 2217 0 00000001109F5A40 00000001109F5AA0 162 TM 100957 0 0 6 2214 0
====>>>>> 過了很久 SYS@lhrdb S3> SELECT * FROM V$LOCK T WHERE T.SID IN (27,162) AND T.TYPE IN ('TX','TM') ORDER BY T.SID ;
ADDR KADDR SID TY ID1 ID2 LMODE REQUEST CTIME BLOCK ---------------- ---------------- ---------- -- ---------- ---------- ---------- ---------- ---------- ---------- 00000001109F6A78 00000001109F6AD8 27 TM 100957 0 6 0 2882 1 070001007C7EB2B0 070001007C7EB328 27 TX 589843 58249 6 0 2882 0 00000001109F6A78 00000001109F6AD8 162 TM 100957 0 0 6 2879 0
SYS@lhrdb S3> /
ADDR KADDR SID TY ID1 ID2 LMODE REQUEST CTIME BLOCK ---------------- ---------------- ---------- -- ---------- ---------- ---------- ---------- ---------- ---------- 00000001109F5A40 00000001109F5AA0 27 TM 100957 0 6 0 2885 1 070001007C7EB2B0 070001007C7EB328 27 TX 589843 58249 6 0 2885 0 00000001109F5A40 00000001109F5AA0 162 TM 100957 0 0 6 2882 0
|
其中，會話1的sid為27，分別在TX和TM級別，擁有LMODE為6的X鎖。BLOCK為1說明會話1阻塞了其它會話（0表示沒有阻塞，2表示RAC環境需要用GV$LOCK）。CTIME表示擁有此鎖的時間，單位為秒。會話2的sid為162，REQUEST為6表示正在請求模式為6的鎖。
當TYPE列為TM的時候，即對于TM鎖來說，ID1列表示被鎖定的對象的對象ID，ID2始終為0，如下：
|
SYS@lhrdb S3> COL OWNER FORMAT A5 SYS@lhrdb S3> COL OBJECT_NAME FORMAT A20 SYS@lhrdb S3> SELECT D.OWNER,D.OBJECT_NAME,D.OBJECT_ID FROM DBA_OBJECTS D WHERE D.OBJECT_ID = 100957; OWNER OBJECT_NAME OBJECT_ID ----- -------------------- ---------- SYS T_APPEND_161107_LHR 100957
|
當TYPE列為TX的時候，即對于TX鎖來說，ID1列表示事務使用的回滾段編號以及在事務表中對應的記錄編號，ID2表示該記錄編號被重用的次數（wrap），ID1列表示事務的信息，如下：
|
SYS@lhrdb S3> SELECT A.TADDR FROM V$SESSION A WHERE SID = 27;
TADDR ---------------- 070001007C7EB2B0
SYS@lhrdb S3> SELECT A.XIDUSN, A.XIDSLOT, A.XIDSQN 2 FROM V$TRANSACTION A 3 WHERE A.ADDR = '070001007C7EB2B0';
XIDUSN XIDSLOT XIDSQN ---------- ---------- ---------- 9 19 58249
SYS@lhrdb S3> SELECT TRUNC(589843 / POWER(2, 16)) AS UNDO_SEG#, 2 BITAND(589843, TO_NUMBER('ffff', 'xxxx')) + 0 AS SLOT#, 3 58249 XIDSQN 4 FROM DUAL;
UNDO_SEG# SLOT# XIDSQN ---------- ---------- ---------- 9 19 58249
SYS@lhrdb S3> SELECT SID, 2 STATUS, 3 SQL_ID, 4 LAST_CALL_ET, 5 BLOCKING_INSTANCE, 6 BLOCKING_SESSION, 7 EVENT 8 FROM GV$SESSION 9 WHERE BLOCKING_SESSION IS NOT NULL;
SID STATUS SQL_ID LAST_CALL_ET BLOCKING_INSTANCE BLOCKING_SESSION EVENT ---------- -------- ------------- ------------ ----------------- ---------------- --------------------- 162 ACTIVE 2kvrfkkjukryr 4875 1 27 enq: TM - contention
SYS@lhrdb S3> select sql_text from v$sql where sql_id='2kvrfkkjukryr';
SQL_TEXT ---------------------------------------------------- INSERT /*+ APPEND */ INTO T_APPEND_161107_LHR SELECT * FROM DUAL
SYS@lhrdb S3> SELECT * FROM DBA_DML_LOCKS D WHERE D.SESSION_ID IN (27, 162);
SESSION_ID OWNER NAME MODE_HELD MODE_REQUESTE LAST_CONVERT BLOCKING_OTHERS ---------- ----- --------------------- ------------- ------------- ------------ --------------- 27 SYS T_APPEND_161107_LHR Exclusive None 647 Blocking 162 SYS T_APPEND_161107_LHR None Exclusive 468 Not Blocking |
從視圖DBA_DML_LOCKS可以非常直觀的看出鎖的情況，會話1即SID為27，擁有Exclusive的排它鎖，沒有請求其它鎖，而會話2即SID為162正在請求Exclusive的排它鎖。
SELECT * FROM V$EVENT_NAME WHERE NAME = 'enq: TM - contention';
從會話查詢鎖的信息：
SELECT SID,
STATUS,
SQL_ID,
LAST_CALL_ET,
EVENT,
A.P1,
A.P2,
A.P3,
CHR(BITAND(P1, -16777216) / 16777215) ||
CHR(BITAND(P1, 16711680) / 65535) "LOCK",
BITAND(P1, 65535) "MODE",
(SELECT OBJECT_NAME FROM DBA_OBJECTS D WHERE D.OBJECT_ID = A.P2) OBJECT_NAME
FROM GV$SESSION A
WHERE A.EVENT = 'enq: TM - contention';
會話1提交，查看會話2的情況：
|
SYS@lhrdb S1> commit;
Commit complete.
SYS@lhrdb S1> 會話2： SYS@lhrdb S2> INSERT /*+ APPEND */ INTO T_APPEND_161107_LHR SELECT * FROM DUAL;
3 rows created.
SYS@lhrdb S2> SYS@lhrdb S2> SYS@lhrdb S2> commit;
Commit complete.
SYS@lhrdb S2> SELECT * FROM V$LOCK T WHERE T.SID IN (27,162) AND T.TYPE IN ('TX','TM') ORDER BY T.SID ;
no rows selected
|
執行不同的 DML 語句時，Oracle自動地對數據加鎖。
一、 查詢操作默認獲取的鎖執行查詢（query）的 SQL 語句不易與其他 SQL 語句沖突，因為查詢只需讀取數據。除了 SELECT 之外，INSERT，UPDATE，及 DELETE 語句中也可能包含隱式的查詢。因此，以下語句都屬于查詢操作：
SELECT
INSERT ... SELECT ... ;
UPDATE ... ;
DELETE ... ;
但是以下語句不屬于查詢操作：
SELECT ... FOR UPDATE OF ... ;
查詢操作具備以下特性：
l 查詢無需獲取數據鎖。因此當某事務查詢數據表時，其它事務可以并發地查詢、更新同一個表，包括此表中正在被查詢的數據行。沒有使用 FOR UPDATE 子句的 SELECT 語句無需獲取任何數據鎖，因此也不會阻塞任何操作，此類查詢在 Oracle 中被稱為非阻塞查詢（nonblocking query）。
l 執行查詢也不受數據鎖的限制。（在某些特殊情況下，查詢需要等待掛起的分布式事務所擁有的數據鎖）
二、 INSERT，UPDATE，DELETE，及 SELECT ... FOR UPDATE 語句默認獲取的鎖INSERT，UPDATE，DELETE，及 SELECT ... FOR UPDATE 語句默認獲取的鎖有以下特點：
l 包含 DML 語句的事務需要獲得被其修改的數據行上的排它行級鎖（exclusive row lock）。在擁有鎖的事務提交或回滾前，其它事務不能更新或刪除被加鎖的數據行。
l 事務無需獲取 DML 語句內的子查詢（subquery）或隱式查詢（implicit query）（例如 WHERE 子句內的查詢）所選擇的行上的行級鎖。DML 內的子查詢或隱式查詢獲得的數據相對查詢開始的時間點滿足一致性，這些查詢不會看到 DML 語句自身對數據的影響。
l 事務內的查詢能夠看到本事務內之前執行的 DML 語句對數據的修改，但無法看到本事務開始后執行的其它事務對數據的修改。
l 事務內的 DML 語句除了需要獲得必要的排它行級鎖（exclusive row lock）外，至少還需獲得包含被修改數據行的表上的行排它表級鎖（row exclusive table lock）。如果事務已經獲得了相關表上的共享表級鎖（share），共享行排它表級鎖（share row exclusive），或排它表級鎖（exclusive），那么就無需獲取行排它表級鎖了。如果事務已經獲得了相關表上的行共享表級鎖（row share table lock），Oracle 將自動地將此鎖轉換為行排它表級鎖。
當某個運行中的DDL操作正在操作或引用某模式對象時，數據字典(DDL)鎖保護該模式對象的定義。在DDL操作的過程中，只有被修改或引用的單個模式的對象被鎖定。數據庫絕不會鎖定整個數據字典。
Oracle數據庫將為任何要求鎖的DDL事務自動獲取DDL鎖。用戶不能顯式請求DDL鎖。例如，如果用戶創建一個存儲過程，則數據庫自動為過程定義中引用的所有模式對象獲取DDL鎖。DDL鎖防止在過程編譯完成之前，這些對象被更改或刪除。
數據字典鎖（data dictionary lock，DDL）的作用是在執行 DDL 操作時對被修改的方案對象或其引用對象的定義進行保護。管理員及開發者應該意識到 DDL 語句將會隱式地提交一個事務。例如，用戶創建一個存儲過程時，相當于執行一個只包含一條 SQL 語句的事務，Oracle 會自動獲取過程定義中所引用的所有方案對象的 DDL 鎖。DDL 鎖能夠防止編譯期間過程所引用的對象被其它事務修改或移除。
當 DDL 事務需要時 Oracle 將自動地為其獲取數據字典鎖。用戶不能顯示地獲取 DDL 鎖。只有在 DDL 操作中被修改或引用的對象才會被加鎖，整個數據字典不會被加鎖。
當用戶發布DDL（Data Definition Language）語句時會對涉及的對象加DDL鎖。由于DDL語句會更改數據字典，所以該鎖也被稱為字典鎖。
DDL鎖能防止在用DML語句操作數據庫表時，對表進行刪除，或對表的結構進行更改。
對于DDL鎖，要注意的是：
l DDL鎖只鎖定DDL操作所涉及的對象，而不會鎖定數據字典中的所有對象。
l DDL鎖由Oracle自動加鎖和釋放。不能顯式地給對象加DDL鎖，即沒有加DDL鎖的語句。
l 在過程中引用的對象，在過程編譯結束之前不能被改變或刪除，即不能被加排它DDL鎖。
DDL 鎖可以分為三類：排它 Ddl 鎖（Exclusive DDL Lock），共享 Ddl 鎖（Share DDL Lock），及可中斷的解析鎖（Breakable Parse Lock）。
大多數DDL 都帶有一個排它DDL 鎖。如果發出如下一條語句：
Alter table t add new_column date;
在執行這條語句時，表T 不能被別人修改。在此期間，可以使用SELECT 查詢這個表，但是大多數其他操作都不允許執行，包括所有DDL 語句。
獨占DDL鎖可防止其它會話獲取DDL或DML鎖。除了那些在"共享DDL鎖"中所述操作之外，絕大多數DDL操作需要對資源獲取獨占鎖，以防止和其它可能會修改或引用相同模式對象的DDL之間的破壞性干擾。例如，當ALTER TABLE正在將一列添加到表時，不允許DROP TABLE刪除表，反之亦然。
獨占DDL鎖在整個DDL語句執行期間一直持續，并自動提交。在獨占DDL鎖獲取過程中，如果另一個操作在該模式對象上持有另一個DDL鎖，則這個鎖獲取將一直等待，直到前一個DDL鎖被釋放，才能繼續。
create index t_idx on t(x) ONLINE;
ONLINE 關鍵字會改變具體建立索引的方法。Oracle 并不是加一個排它DDL 鎖 防止數據修改，而只會試圖得到表上的一個低級 （mode 2 ）TM 鎖。這會有效地防止其他DDL 發生，同時還允許DML 正常進行。Oracle 執行這一壯舉”的做法是，為DDL 語句執行期 間對表所做的修改維護一個記錄，執行CREATE 時再把這些修改應用至新的索引。這樣能大大增加數據的可用性。
另外一類DDL 會獲得共享DDL 鎖。在創建存儲的編譯對象（如過程和視圖）時，會對依賴的對象加這種共享DDL 鎖。例如，如果 執行以下語句：
Create view MyView as select * from emp, dept where emp.deptno = dept.deptno;
表EMP 和DEPT 上都會加共享DDL 鎖，而CREATE VIEW 命令仍在處理。可以修改這些表的內容，但是不能修改它們的結構。
A share DDL lock for a resource prevents destructive interference with conflicting DDL operations, but allows data concurrency for similar DDL operations.
在資源上的共享DDL鎖可防止與沖突的DDL操作發生破壞性干擾，但允許類似的DDL操作的數據并發。
例如，當CREATE PROCEDURE語句運行時，所在事務將為所有被引用的表獲取共享DDL鎖。其它事務可以同時創建引用相同表的過程，并在相同的表上同時獲得共享DDL鎖，但沒有任何事務能在任何被引用表上獲取獨占DDL鎖。
共享DDL鎖在整個DDL語句執行期間持續存在，并自動提交。因此，持有一個共享DDL鎖的事務，可保證在事務過程中，被引用模式對象的定義保持不變。
某些 DDL 操作需要獲取相關資源上的共享 DDL 鎖（share DDL lock）以防止與之沖突的 DDL 操作造成破壞性的干擾，但與之類似的 DDL 操作可以并發地訪問數據，不受共享 DDL 鎖的限制。例如，執行 CREATE PROCEDURE 語句時，事務將獲取所有引用對象上的共享 DDL 鎖。此時，其它事務可以并發地獲取相同表上的共享 DDL 鎖并創建引用了相同表的過程。但任何事務都無法獲取被引用表上的排它 DDL 鎖（exclusive DDL lock），即任何事務都無法對表進行修改或移除操作。因此獲得了共享 DDL 鎖的事務能夠保證在其執行期間，所有引用對象的定義不會被修改。
執行以下 DDL 語句時，需要獲取引用對象上的共享 DDL 鎖：AUDIT，NOAUDIT，COMMENT，CREATE [OR REPLACE] VIEW/ PROCEDURE/PACKAGE/PACKAGE BODY/FUNCTION/ TRIGGER，CREATE SYNONYM，及 CREATE TABLE（沒有使用 CLUSTER 參數時）。
SQL語句或PL/SQL程序單元，為每個被其引用的模式對象持有一個解析鎖。獲取解析鎖的目的是，如果被引用的對象被更改或刪除，可以使相關聯的共享SQL區無效。解析鎖被稱為可中斷的解析鎖，因為它并不禁止任何DDL操作，并可以被打破以允許沖突的DDL操作。
解析鎖是在執行SQL語句的分析階段，在共享池中獲取的。只要該語句的共享SQL區仍保留在共享池中，該鎖就一直被持有。
位于共享池（shared pool）內的 SQL 語句（或 PL/SQL 程序結構）擁有其引用的所有方案對象上的解析鎖（parse lock）。解析鎖的作用是，當共享 SQL 區（shared SQL area）所引用的對象被修改或移除后，此共享 SQL 區能夠被置為無效。解析鎖不會禁止任何 DDL 操作，當出現與解析鎖沖突的 DDL 操作時，解析鎖將被解除，因此也稱之為可解除的解析鎖。
解析鎖是在 SQL 語句執行的解析階段（parse phase）獲得的，在共享 SQL 區被清除出共享池（shared pool）前一直保持。
你的會話解析一條語句時，對于該語句引用的每一個對象都會加一個解析鎖。加這些鎖的目的是：如果以某種方式刪除或修改了一個被引用的對象，可以將共享池中已解析的緩存語句置為無效（刷新輸出）。
一、 查看分析鎖|
CREATE OR REPLACE PROCEDURE P_BPL_LHR AS BEGIN NULL; END;
|
要看到一個實際的可中斷解析鎖，下面先創建并運行存儲過程P_BPL_LHR：
|
SYS@lhrdb> CREATE OR REPLACE PROCEDURE P_BPL_LHR AS 2 BEGIN 3 NULL; 4 END; 5 /
Procedure created.
SYS@lhrdb> exec P_BPL_LHR;
PL/SQL procedure successfully completed.
SYS@lhrdb> SELECT DISTINCT SID FROM V$MYSTAT;
SID ---------- 194
|
過程P_BPL_LHR現在會出現在DBA_DDL_LOCKS 視圖中。我們有這個過程的一個解析鎖：
SELECT * FROM DBA_DDL_LOCKS D WHERE D.SESSION_ID = 194;
然后重新編譯這個過程，并再次查詢視圖：
|
SYS@lhrdb> ALTER PROCEDURE P_BPL_LHR COMPILE;
Procedure altered.
|
可以看到，現在這個視圖中沒有P_BPL_LHR了。我們的解析鎖被中斷了。這個視圖對 發人員很有用，發現測試或開發系統中某段代碼無法編譯時，將會掛起并最終超時。這說明，有人正在使用這段代碼 （實際上在運行這段代碼），你可以使用這個視圖 查看這個人是誰。對于GRANTS 和對象的其他類型的DDL 也是一樣。例如，無法對正在運行的過程授予EXECUTE 權限。可以使用同樣的方法 發現潛在的阻塞者和等待者。
DDL 鎖的持續時間取決于其類型。共享 DDL 鎖（share DDL lock）及排它 DDL 鎖（exclusive DDL lock）在 DDL 語句執行期間一直存在，在 DDL 語句自動提交后釋放。而解析鎖一直存在，直至相關的共享 SQL 區從共享池中被清除。
對簇（cluster）執行的 DDL 操作需要獲取簇及簇內所有表及物化視圖上的排它 DDL 鎖（exclusive DDL lock）。對簇內表及物化視圖的 DDL 操作需要獲取簇上的共享 DDL 鎖（share DDL lock），以及表或物化視圖上的共享 DDL 鎖或排它 DDL 鎖。簇上的共享 DDL 鎖能夠防止操作期間其他 DDL 操作將簇移除。
Oracle數據庫使用各種類型的系統鎖，來保護數據庫內部和內存結構。由于用戶不能控制其何時發生或持續多久，這些機制對于用戶幾乎是不可訪問的。閂鎖、互斥體、和內部鎖是完全自動的。
閂鎖（latche）是一種簡單的底層串行化機制，用于保護 SGA 內的共享數據結構。例如，用于記錄當前正在訪問數據庫的用戶的列表，或用于記錄位于數據庫緩存（buffer cache）內的數據塊的數據結構，都可通過閂鎖進行保護。當服務進程（background process）或后臺進程（server process）需要操作或查詢此類數據結構時，就需要獲取一個閂鎖，但其加鎖時間極短。閂鎖的實現與操作系統有關，例如進程是否需要等待栓鎖以及等待多長時間等。
閂鎖是簡單、低級別的串行化機制，用于協調對共享數據結構、對象、和文件的多用戶訪問。閂鎖防止共享內存資源被多個進程訪問時遭到破壞。具體而言，閂鎖在以下情況下保護數據結構：
l 被多個會話同時修改
l 正在被一個會話讀取時，又被另一個會話修改
l 正在被訪問時，其內存被釋放（換出）
通常，一個單一的閂鎖保護SGA中的多個對象。例如，后臺進程（如DBWn和LGWR）從共享池分配內存來創建數據結構。為分配此內存，這些進程使用共享池閂鎖來串行化對內存的訪問，以防止兩個進程同時嘗試檢查或修改共享池。內存分配后，其它進程可能需要訪問共享池區域，如用于解析所需的庫高速緩存。在這種情況下，進程只在庫緩存獲取閂鎖，而不是在整個共享池。
與行鎖之類的入隊閂鎖不同，閂鎖不允許會話排隊。當閂鎖可用時，請求閂鎖的第一個會話將獲得它的獨占訪問權限。閂鎖旋轉（Latch spinning）發生在當一個進程不斷地循環來請求一個閂鎖時，而閂鎖睡眠（latch sleeping）發生在重新發起閂鎖請求之前，釋放CPU時。
通常，一個Oracle進程在操作或查看一種數據結構時，只需在一個極短的時間內獲得閂鎖。例如，僅僅為某一名員工處理工資更新，數據庫就可能需要獲取并釋放成千上萬個閂鎖。閂鎖的實現依賴于操作系統，特別是在一個進程是否會在閂鎖上等待以及會在閂鎖等待多長時間方面。
閂鎖的增加意味著并發的降低。例如，過度硬解析操作會產生庫緩存閂鎖爭用。V$LATCH視圖包含每個閂鎖的詳細使用情況的統計信息，包括每個閂鎖被請求和被等待的次數。
互斥對象（mutual exclusion object，mutex），也叫互斥體，它是一種底層機制，用于防止在內存中的對象在被多個并發進程訪問時，被換出內存或遭到破壞。互斥對象類似于閂鎖，但閂鎖通常保護一組對象，而互斥對象通常保護單個對象。
互斥對象提供以下幾個優點：
1、 互斥體可以減少發生爭用的可能性。
由于閂鎖保護多個對象，當多個進程試圖同時訪問這些對象的任何一個時，它可能成為一個瓶頸。而互斥體僅僅串行化對單個對象的訪問，而不是一組對象，因此互斥體提高了可用性。
2、 互斥體比閂鎖消耗更少的內存。
3、 在共享模式下，互斥體允許被多個會話并發引用。
內部鎖是比閂鎖和互斥體更高級、更復雜的機制，并用于各種目的。數據庫使用以下類型的內部鎖：
1、 字典緩存鎖（Dictionary cache locks）
這些鎖的持續時間很短，當字典緩存中的條目正在被修改或使用時被持有。它們保證正在被解析的語句不會看到不一致的對象定義。字典緩存鎖可以是共享的或獨占的。共享鎖在解析完成后被釋放，而獨占鎖在DDL操作完成時釋放。
當用戶更新或使用時數據字典緩存內的條目（entry）時，需要獲取條目上的數據字典緩存鎖（dictionary cache lock），此類鎖的持續時間極短。此類鎖的作用是確保正在被解析的語句不會看到不一致的對象定義。數據字典緩存鎖可以為共享或排它的。當語句解析結束時共享鎖將被釋放，而當 DDL 操作結束時排它鎖將被釋放。
2、 文件和日志管理鎖（File and log management locks）
這些鎖保護各種文件。例如，一種內部鎖保護控制文件，以便一次只有一個進程可以對其進行更改。而另一種鎖用于協調聯機重做日志文件的使用和歸檔。數據文件被鎖定，確保數據庫被多個實例以共享模式裝載，或以獨占模式被單個實例裝載。因為文件和日志鎖表示文件的狀態，這些鎖必要時會被持有較長一段時間。
此類內部鎖（internal lock）用于保護各種文件。例如，保護控制文件（control file）的鎖，確保同一時間只有一個進程能夠對其進行修改。還有協調重做日志文件（redo log file）使用與歸檔的鎖。以及數據文件（datafile）鎖，實現多實例在共享模式下掛載數據庫，或一個實例在排它模式下掛載數據庫。由于文件及重做日志鎖反映的是 物理文件的狀態，因此此類鎖的持續時間較長。
3、 表空間和撤銷段鎖（Tablespace and undo segment locks）
這些鎖保護的表空間和撤銷段。例如，訪問數據庫的所有實例對一個表空間是否處于聯機或脫機必須保持一致。撤銷段被鎖定，以便只能有一個數據庫實例可以寫入該段。
此類鎖用于保護表空間及回滾段（rollback segment）。例如，一個表空間處于聯機（online）還是脫機（offline）狀態對訪問同一數據庫的所有實例應該是一致的。回滾段上的鎖保證 同一時間只有一個實例能夠對其執行寫操作。
有關死鎖的內容之前發布過一次，具體內容參考：http://blog.itpub.net/26736162/viewspace-2127247/，本篇文章不再講解。
常用的數據字典視圖有DBA_DML_LOCKS、DBA_DDL_LOCKS、V$LOCK、DBA_LOCK、V$LOCKED_OBJECT。
---查詢的都是當前實例的鎖
select * from dba_dml_locks;
select * from dba_ddl_locks d where d.owner not in('SYS','WMSYS','MDSYS');
select * from DBA_LOCK V where V.session_id=23;
select * from V$LOCK V where V.SID=23;
select * from V$LOCK_TYPE;
select * from V$LOCKED_OBJECT;
本視圖列出Oracle 服務器當前擁有的鎖以及未完成的鎖或栓鎖請求。
v$lock和dba_locks和 dba_lock 內容一樣，dba_locks是dba_lock的同義詞。可以用動態性能視圖的定義來查看它們的關系V$FIXED_VIEW_DEFINITION。
SELECT * FROM Dba_Objects d WHERE d.object_name LIKE '%DBA_LOCK%' ;
SELECT * FROM Dba_Synonyms d WHERE d.synonym_name LIKE '%DBA_LOCK%' ;
SELECT * FROM V$FIXED_VIEW_DEFINITION d WHERE d.VIEW_NAME LIKE '%V$LOCK%' ;
注意：V$LOCKED_OBJECT記錄的是DML鎖信息，DDL鎖的信息不在里面。
這個視圖列出系統上的每個事務處理所獲得的所有鎖。記錄了當前已經被鎖定的對象的信息
XIDUSN表示當前事務使用的回滾段的編號
XIDSLOT說明該事務在回滾段頭部的事務表中對應的記錄編號
XIDSQN說明序列號
OBJECT_ID說明當前被鎖定的對象的ID號，可以根據該ID號到dba_objects里查找被鎖定的對象名稱
LOCKED_MODE說明鎖定模式的數字編碼
V$LOCKED_OBJECT中的列說明：
示例：1.以DBA角色查看當前數據庫里鎖的情況可以用如下SQL語句：
SELECT v.object_id,
d.OBJECT_NAME,
d.OBJECT_TYPE,
locked_mode,
v2.username,
v2.sid,
v2.serial#,
v2.logon_time
FROM v$locked_object v,
dba_objects d,
v$session v2
WHERE v.OBJECT_ID = d.OBJECT_ID
AND v.SESSION_ID = v2.SID
ORDER BY v2.logon_time;
v$locked_object視圖列出當前系統中哪些對象正被鎖定.
v$lock視圖列出當前系統持有的或正在申請的所有鎖的情況.
DBA_DDL_LOCKS lists all DDL locks held in the database and all outstanding requests for a DDL lock.
查詢所有DDL鎖的信息：
SELECT * FROM DBA_DDL_LOCKS D WHERE D.SESSION_ID = 115;
如果提示沒有這個視圖，可以在sys用戶下執行$ORACLE_HOME/rdbms/admin/catblock.sql腳本進行創建（這個腳本還包含其他一些非常有意義的鎖相關視圖）
sys@ora10g> conn / as sysdba
Connected.
sys@ora10g> @?/rdbms/admin/catblock.sql
這里省略創建過程
打印一下catblock.sql腳本的內容，這個創建腳本其實可以當做一個參考文檔來用，尤其是其中關于鎖類型的描述。
DBA_DML_LOCKS lists all DML locks held in the database and all outstanding requests for a DML lock.
|
SQL> CREATE TABLE TB_DML_LOCK_LHR (ID NUMBER);
Table created.
SQL> INSERT INTO TB_DML_LOCK_LHR VALUES(1);
1 row created.
SQL> set line 9999 SQL> SELECT * FROM DBA_DML_LOCKS;
SESSION_ID OWNER NAME MODE_HELD MODE_REQUESTE LAST_CONVERT BLOCKING_OTHERS ---------- ------- ------------------ ------------- ------------- ------------ ----------------- 151 SYS TB_DML_LOCK_LHR Row-X (SX) None 10 Not Blocking
SQL>
|
|
會話1： SYS@oratest S1> select distinct sid from v$mystat;
SID ---------- 22
SYS@oratest S1> CREATE TABLE SCOTT.EMP_LHR AS SELECT * FROM SCOTT.EMP;
Table created.
SYS@oratest S1> delete from scott.EMP_LHR where empno=7369;
1 row deleted.
SYS@oratest S1>
會話2： SYS@oratest S2> select distinct sid from v$mystat;
SID ---------- 143
SYS@oratest S2> delete from scott.EMP_LHR where empno=7369;
====>>>>> 產生了阻塞
會話3查詢鎖： SQL> set line 9999 SQL> SELECT A.TADDR, 2 A.LOCKWAIT, 3 A.ROW_WAIT_OBJ#, 4 A.ROW_WAIT_FILE#, 5 A.ROW_WAIT_BLOCK#, 6 A.ROW_WAIT_ROW#, 7 A.EVENT, 8 A.P1, 9 A.P2, 10 A.SID, 11 A.BLOCKING_SESSION 12 FROM V$SESSION A 13 WHERE A.SID IN (22, 143); TADDR LOCKWAIT ROW_WAIT_OBJ# ROW_WAIT_FILE# ROW_WAIT_BLOCK# ROW_WAIT_ROW# EVENT P1 P2 SID BLOCKING_SESSION ---------------- ---------------- ------------- -------------- --------------- ------------- ------------------------------ ---------- ---------- ---------- ---------------- 000000007622B710 -1 0 0 0 SQL*Net message from client 1650815232 1 22 000000007622AD00 00000000774DA0C0 77669 8 2799 0 enq: TX - row lock contention 1415053318 524299 143 22
|
V$SESSION視圖的TADDR列表示事務處理狀態對象的地址，對應于V$TRANSACTION.ADDR列；V$SESSION視圖的LOCKWAIT列表示等待鎖的地址，對應于V$LOCK的KADDR列；若當前會話沒有被阻塞則為空。V$SESSION視圖的SADDR列對應于V$TRANSACTION的SES_ADDR列。可以通過ROW_WAIT_OBJ#、ROW_WAIT_FILE#、ROW_WAIT_BLOCK#、ROW_WAIT_ROW#這幾個字段查詢現在正在被鎖的表的相關信息（ROWID），例如，表名、文件名及行號。P1和P2根據等待事件的不同所代表的含義不同，可以從V$EVENT_NAME視圖獲知每個參數的含義。
|
SQL> SELECT D.PARAMETER1,D.PARAMETER2,D.PARAMETER3 FROM V$EVENT_NAME D WHERE D.NAME='enq: TX - row lock contention';
PARAMETER1 PARAMETER2 PARAMETER3 ------------ --------------- ---------- name|mode usn<<16 | slot sequence
SQL> SELECT CHR(BITAND(P1, -16777216) / 16777215) || 2 CHR(BITAND(P1, 16711680) / 65535) "LOCK", 3 BITAND(P1, 65535) "MODE", 4 TRUNC(P2 / POWER(2, 16)) AS XIDUSN, 5 BITAND(P2, TO_NUMBER('FFFF', 'XXXX')) + 0 AS XIDSLOT, 6 P3 XIDSQN 7 FROM V$SESSION A 8 WHERE A.SID IN (143);
LOCK MODE XIDUSN XIDSLOT XIDSQN ---- ---------- ---------- ---------- ---------- TX 6 4 30 894
<<<<<---從P1參數獲知請求的鎖的類型和模式；從P2參數可以獲知槽位號
SQL> SELECT ADDR,XIDUSN,XIDSLOT,XIDSQN FROM v$transaction a WHERE a.ADDR IN ('000000007622B710');
ADDR XIDUSN XIDSLOT XIDSQN ---------------- ---------- ---------- ---------- 000000007622B710 4 30 894
SQL> SELECT ADDR,XIDUSN,XIDSLOT,XIDSQN FROM v$transaction a WHERE a.SES_ADDR ='0000000077E6F600';
ADDR XIDUSN XIDSLOT XIDSQN ---------------- ---------- ---------- ---------- 000000007622B710 4 30 894
SQL> SELECT * FROM V$LOCK A WHERE A.SID IN (22, 143) AND A.TYPE IN ('TX','TM') AND A.KADDR='00000000774DA0C0' ORDER BY a.SID,a.TYPE;
ADDR KADDR SID TY ID1 ID2 LMODE REQUEST CTIME BLOCK ---------------- ---------------- ---------- -- ---------- ---------- ---------- ---------- ---------- ---------- 00000000774DA068 00000000774DA0C0 143 TX 262174 894 0 6 658 0
SQL> SELECT DBMS_ROWID.ROWID_CREATE(1, 77766, 4, 131, 0) FROM DUAL;
DBMS_ROWID.ROWID_C ------------------ AAAS/GAAEAAAACDAAA
SQL> SELECT * FROM SCOTT.EMP A WHERE A.ROWID='AAAS/GAAEAAAACDAAA';
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO ---------- ---------- --------- ---------- ------------------- ---------- ---------- ---------- 7369 SMITH CLERK 7902 1980-12-17 00:00:00 800 20
SQL>
|
可以看到被鎖的行的地址。
|
SQL> SELECT * FROM V$LOCK A WHERE A.SID IN (22, 143) AND A.TYPE IN ('TX','TM') ORDER BY a.SID,a.TYPE;
ADDR KADDR SID TY ID1 ID2 LMODE REQUEST CTIME BLOCK ---------------- ---------------- ---------- -- ---------- ---------- ---------- ---------- ---------- ---------- 00007FF44BF72D18 00007FF44BF72D78 22 TM 77766 0 3 0 793 0 000000007622B710 000000007622B788 22 TX 262174 894 6 0 793 1 00007FF44BF72D18 00007FF44BF72D78 143 TM 77766 0 3 0 787 0 00000000774DA068 00000000774DA0C0 143 TX 262174 894 0 6 787 0 6 rows selected.
SQL> SQL> SELECT * FROM DBA_DML_LOCKS D WHERE D.SESSION_ID IN (22, 143) ORDER BY d.SESSION_ID;
SESSION_ID OWNER NAME MODE_HELD MODE_REQUESTE LAST_CONVERT BLOCKING_OTHERS ---------- -------- -------- ------------- ------------- ------------ --------------- 22 SCOTT EMP_LHR Row-X (SX) None 1146 Not Blocking 143 SCOTT EMP_LHR Row-X (SX) None 1140 Not Blocking
SQL> SELECT D.OWNER, D.OBJECT_NAME, D.OBJECT_ID, D.OBJECT_TYPE 2 FROM DBA_OBJECTS D 3 WHERE D.OBJECT_ID IN (77766);
OWNER OBJECT_NAME OBJECT_ID OBJECT_TYPE -------- ------------ ---------- ------------------- SCOTT EMP_LHR 77766 TABLE
SQL> SQL> SELECT a.XIDUSN, 2 a.XIDSLOT, 3 a.XIDSQN FROM v$transaction a WHERE a.XIDSQN =894;
XIDUSN XIDSLOT XIDSQN ---------- ---------- ---------- 4 30 894 SQL> SELECT 4*POWER(2,16)+30 FROM DUAL;
4*POWER(2,16)+30 ---------------- 262174
SQL> SQL> SELECT TRUNC(ID1 / POWER(2, 16)) AS XIDUSN, 2 BITAND(ID1, TO_NUMBER('FFFF', 'XXXX')) + 0 AS XIDSLOT, 3 894 XIDSQN 4 FROM V$LOCK A 5 WHERE A.SID IN (22, 143) 6 AND A.TYPE IN ('TX', 'TM') 7 AND A.ADDR = '000000007622B710' 8 ORDER BY A.SID, A.TYPE;
XIDUSN XIDSLOT XIDSQN ---------- ---------- ---------- 4 30 894
|
在V$LOCK中，當TYPE列的值為TM時，ID1的值為DBA_OBJECTS.OBJECT_ID；當為TX鎖時，ID1對應視圖V$TRANSACTION中的XIDUSN字段（Undo segment number：事務對應的撤銷段序列號）和XIDSLOT字段（Slot number：事務對應的槽位號）。其中ID1的高16位為XIDUSN，低16位為XIDSLOT。計算公式為：SELECT TRUNC(ID1/POWER(2,16)) AS XIDUSN,BITAND(ID1,TO_NUMBER('FFFF','XXXX')) + 0 AS XIDSLOT , ID2 XIDSQN FROM DUAL;
在V$LOCK中，當TYPE列的值為TM鎖時，ID2的值為0；當為TX鎖時，ID2對應視圖V$TRANSACTION中的XIDSQN字段（Sequence number：事務對應的序列號）。
從V$SESSION視圖可以得到所有內容：
SELECT A.TADDR,
A.LOCKWAIT,
A.ROW_WAIT_OBJ#,
A.ROW_WAIT_FILE#,
A.ROW_WAIT_BLOCK#,
A.ROW_WAIT_ROW#,
(SELECT D.OWNER || '|' || D.OBJECT_NAME || '|' || D.OBJECT_TYPE
FROM DBA_OBJECTS D
WHERE D.OBJECT_ID = A.ROW_WAIT_OBJ#) OBJECT_NAME,
A.EVENT,
A.P1,
A.P2,
A.P3,
CHR(BITAND(P1, -16777216) / 16777215) ||
CHR(BITAND(P1, 16711680) / 65535) "LOCK",
BITAND(P1, 65535) "MODE",
TRUNC(P2 / POWER(2, 16)) AS XIDUSN,
BITAND(P2, TO_NUMBER('FFFF', 'XXXX')) + 0 AS XIDSLOT,
P3 XIDSQN,
A.SID,
A.BLOCKING_SESSION,
A.SADDR,
DBMS_ROWID.ROWID_CREATE(1, 77669, 8, 2799, 0) REQUEST_ROWID,
(SELECT B.SQL_TEXT
FROM V$SQL B
WHERE B.SQL_ID = NVL(A.SQL_ID, A.PREV_SQL_ID)) SQL_TEXT
FROM V$SESSION A
WHERE A.SID IN (143);
可以獲得的TX鎖定的總個數由初始化參數transactions決定，而可以獲得的TM鎖定的個數則由初始化參數dml_locks決定
select name,value from v$parameter where name in('transactions','dml_locks');
|
SYS@racdb1> col name format a15 SYS@racdb1> col value format a5 SYS@racdb1> select name,value from v$parameter where name in('transactions','dml_locks');
NAME VALUE --------------- ----- dml_locks 1088 transactions 272
SYS@racdb1> select 272*4 from dual;
272*4 ---------- 1088
|
DML_LOCKS參數屬于推導參數，DML_LOCKS=4 * TRANSACTIONS。
select resource_name as "R_N",current_utilization as "C_U",max_utilization as "M_U",initial_allocation as "I_U"
from v$resource_limit
where resource_name in('transactions','dml_locks');
|
SYS@racdb1> select resource_name as "R_N",current_utilization as "C_U",max_utilization as "M_U",initial_allocation as "I_U" 2 from v$resource_limit 3 where resource_name in('transactions','dml_locks');
R_N C_U M_U I_U ------------------------------ ---------- ---------- -------------------- dml_locks 0 28 1088 transactions 0 6 272
|
系統中允許的TM 鎖總數可以由你配置（有關細節請見Oracle Database Reference 手冊中的DML_LOCKS 參數定義）。實際上，這個數可能設置為0。但這并不是說你的數據庫變成了一個只讀數據庫（沒有鎖），而是說不允許DDL。在非常專業的應用（如RAC 實現）中，這一點就很有用，可以減少實例內可能發生的協調次數。通過使用ALTER TABLE TABLENAME DISABLE TABLE LOCK 命令，還可以逐對象地禁用TM 鎖。這是一種快捷方法，可以使意外刪除表的難度更大”，因為在刪除表之前，你必須重新啟用表鎖。還能用它檢測由于外鍵未加索引而導致的全表鎖（前面已經討論過）。
|
Property |
Description |
|
Parameter type |
Integer |
|
Default value |
Derived: 4 * TRANSACTIONS |
|
Modifiable |
No |
|
Range of values |
20 to unlimited; a setting of 0 disables enqueues |
|
Basic |
No |
|
Oracle RAC |
You must set this parameter for every instance, and all instances must have positive values or all must be 0. |
A DML lock is a lock obtained on a table that is undergoing a DML operation (insert, update, delete). DML_LOCKS specifies the maximum number of DML locks—one for each table modified in a transaction. The value should equal the grand total of locks on tables currently referenced by all users. For example, if three users are modifying data in one table, then three entries would be required. If three users are modifying data in two tables, then six entries would be required.
The default value assumes an average of four tables referenced for each transaction. For some systems, this value may not be enough.
Enqueues are shared memory structures that serialize access to database resources. If you set the value of DML_LOCKS to 0, enqueues are disabled and performance is slightly increased. However, you should be aware of the following restrictions when you set you DML_LOCKS to 0:
l You cannot use DROP TABLE, CREATE INDEX statements
l You cannot use explicit lock statements such as LOCK TABLE IN EXCLUSIVE MODE
l Enterprise Manager cannot run on any instances for which DML_LOCKS is set to 0
Oracle holds more locks during parallel DML than during serial execution. Therefore, if your database supports a lot of parallel DML, you may need to increase the value of this parameter.
11g以前，DDL 語句是不會等待DML語句的，當DDL語句訪問的對象正在執行的DML語句，會立即報錯ORA-00054: 資源正忙, 但指定以 NOWAIT 方式獲取資源, 或者超時失效。而在11g以后，DDL_LOCK_TIMEOUT參數可以修改這一狀態，當DDL_LOCK_TIMEOUT=0時，DDL 不等待DML，當DDL_LOCK_TIMEOUT 為N（秒）時，DDL等待DML N 秒，該值默認為0。
|
Property |
Description |
|
Parameter type |
Integer |
|
Default value |
0 |
|
Modifiable |
ALTER SESSION |
|
Range of values |
0 to 1,000,000 (in seconds) |
|
Basic |
No |
DDL_LOCK_TIMEOUT specifies a time limit for how long DDL statements will wait in a DML lock queue. The default value of zero indicates a status of NOWAIT. The maximum value of 1,000,000 seconds will result in the DDL statement waiting forever to acquire a DML lock.
If a lock is not acquired before the timeout period expires, then an error is returned.
會話1：
|
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> set sqlprompt "_user'@'_connect_identifier S1> " SYS@oratest S1> set timing on SYS@oratest S1> update scott.emp set ename='' where empno=7499;
1 row updated.
Elapsed: 00:00:00.00 SYS@oratest S1>
|
會話2：
|
SQL> set sqlprompt "_user'@'_connect_identifier S2> " SYS@oratest S2> set timing on SYS@oratest S2> drop table scott.emp; drop table scott.emp * ERROR at line 1: ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired
Elapsed: 00:00:00.74 SYS@oratest S2> show parameter ddl_lock_timeout
NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ ddl_lock_timeout integer 0 SYS@oratest S2> alter session set ddl_lock_timeout=5;
Session altered.
Elapsed: 00:00:00.00 SYS@oratest S2> drop table scott.emp; drop table scott.emp * ERROR at line 1: ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired
Elapsed: 00:00:05.01 SYS@oratest S2> alter session set ddl_lock_timeout=10;
Session altered.
Elapsed: 00:00:00.00 SYS@oratest S2> drop table scott.emp; drop table scott.emp * ERROR at line 1: ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired
Elapsed: 00:00:10.03 SYS@oratest S2>
|
綜上，設置ddl_lock_timeout為N（秒）后，DDL執行后將等待N秒鐘后才拋出報錯信息。在ddl_lock_timeout為默認值 0 時，DDL語句提交之后馬上報錯。
SELECT...FOR UPDATE 語句的語法如下：
SELECT ... FOR UPDATE [OF column_list][WAIT n|NOWAIT][SKIP LOCKED];
其中：
l OF 這個OF子句在牽連到多個表時，具有較大作用，如不使用OF指定鎖定的表的列，則所有表的相關行均被鎖定，若在OF中指定了需修改的列，則只有與這些列相關的表的行才會被鎖定。
l WAIT 子句指定等待其他用戶釋放鎖的秒數，防止無限期的等待。
“使用FOR UPDATE WAIT”子句的優點如下：
１防止無限期地等待被鎖定的行；
２允許應用程序中對鎖的等待時間進行更多的控制。
３對于交互式應用程序非常有用，因為這些用戶不能等待不確定
４ 若使用了skip locked，則可以越過鎖定的行，不會報告由wait n 引發的‘資源忙’異常報告
for update nowait和 for update都會對所查詢到得結果集進行加鎖，所不同的是，如果另外一個進程正在修改結果集中的數據，for update nowait不會進行資源等待，只要發現結果集中有些數據被加鎖，立刻返回“ORA-00054錯誤，內容是資源正忙, 但指定以 NOWAIT 方式獲取資源”。
for update 和 for update nowait加上的是一個行級鎖，也就是只有符合where條件的數據被加鎖。如果僅僅用update語句來更改數據時，可能會因為加不上鎖而沒有響應地、莫名其妙地等待，但如果在此之前，for update NOWAIT語句將要更改的數據試探性地加鎖，就可以通過立即返回的錯誤提示而明白其中的道理，或許這就是For Update和NOWAIT的意義之所在。
|
會話1： SYS@oratest S1> SELECT EMPNO, ENAME FROM SCOTT.EMP WHERE EMPNO = '7369' FOR UPDATE NOWAIT;
EMPNO ENAME ---------- ---------- 7369 SMITH 會話2： SYS@oratest S2> SELECT EMPNO, ENAME FROM SCOTT.EMP WHERE EMPNO = '7369' FOR UPDATE NOWAIT; SELECT EMPNO, ENAME FROM SCOTT.EMP WHERE EMPNO = '7369' FOR UPDATE NOWAIT * ERROR at line 1: ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired
上面會話都提交commit，開啟會話1，不使用NOWAIT： SYS@oratest S1> SELECT EMPNO, ENAME FROM SCOTT.EMP WHERE EMPNO = '7369' FOR UPDATE ;
EMPNO ENAME ---------- ---------- 7369 SMITH
SYS@oratest S1> 開啟另一會話 SYS@oratest S2> SELECT EMPNO, ENAME FROM SCOTT.EMP WHERE EMPNO = '7369' FOR UPDATE ;
====>>>>> 產生了阻塞
|
阻塞，不返回錯誤。提交第一個會話，第二個回話自動執行，然后提交第二個會話
select for update of，這個of子句在牽連到多個表時，具有較大作用，如不使用of指定鎖定的表的列，則所有表的相關行均被鎖定，若在of中指定了需修改的列，則只有與這些列相關的表的行才會被鎖定。
|
會話1： SYS@oratest S1> SELECT * FROM SCOTT.EMP E, SCOTT.DEPT D WHERE E.DEPTNO = D.DEPTNO FOR UPDATE;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO DEPTNO DNAME LOC ---------- ---------- --------- ---------- ------------------- ---------- ---------- ---------- ---------- -------------- ------------- 7369 SMITH CLERK 7902 1980-12-17 00:00:00 800 20 20 RESEARCH DALLAS 7499 ALLEN SALESMAN 7698 1981-02-20 00:00:00 1600 300 30 30 SALES CHICAGO 7521 WARD SALESMAN 7698 1981-02-22 00:00:00 1250 500 30 30 SALES CHICAGO 7566 JONES MANAGER 7839 1981-04-02 00:00:00 2975 20 20 RESEARCH DALLAS 7654 MARTIN SALESMAN 7698 1981-09-28 00:00:00 1250 1400 30 30 SALES CHICAGO 7698 BLAKE MANAGER 7839 1981-05-01 00:00:00 2850 30 30 SALES CHICAGO 7782 CLARK MANAGER 7839 1981-06-09 00:00:00 2450 10 10 ACCOUNTING NEW YORK 7788 SCOTT ANALYST 7566 1987-04-19 00:00:00 3000 20 20 RESEARCH DALLAS 7839 KING PRESIDENT 1981-11-17 00:00:00 5000 10 10 ACCOUNTING NEW YORK 7844 TURNER SALESMAN 7698 1981-09-08 00:00:00 1500 0 30 30 SALES CHICAGO 7876 ADAMS CLERK 7788 1987-05-23 00:00:00 1100 20 20 RESEARCH DALLAS 7900 JAMES CLERK 7698 1981-12-03 00:00:00 950 30 30 SALES CHICAGO 7902 FORD ANALYST 7566 1981-12-03 00:00:00 3000 20 20 RESEARCH DALLAS 7934 MILLER CLERK 7782 1982-01-23 00:00:00 1300 10 10 ACCOUNTING NEW YORK
14 rows selected. 會話2： SYS@oratest S2> SELECT * FROM SCOTT.DEPT FOR UPDATE NOWAIT; SELECT * FROM SCOTT.DEPT FOR UPDATE NOWAIT * ERROR at line 1: ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired
SYS@oratest S2> SYS@oratest S2> SELECT * FROM V$LOCK A WHERE A.SID IN (16,27) AND A.TYPE IN ('TX','TM') ORDER BY a.SID,a.TYPE;
ADDR KADDR SID TY ID1 ID2 LMODE REQUEST CTIME BLOCK ---------------- ---------------- ---------- -- ---------- ---------- ---------- ---------- ---------- ---------- 00007F8FABF13398 00007F8FABF133F8 16 TM 77667 0 3 0 201 0 00007F8FABF13398 00007F8FABF133F8 16 TM 77669 0 3 0 201 0 000000007620A7C0 000000007620A838 16 TX 327687 1138 6 0 201 0
SYS@oratest S2> SELECT * FROM DBA_DML_LOCKS D WHERE D.SESSION_ID IN (16,27) ORDER BY d.SESSION_ID;
SESSION_ID OWNER NAME MODE_HELD MODE_REQUESTE LAST_CONVERT BLOCKING_OTHERS ---------- --------- ------ ------------- ------------- ------------ --------------- 16 SCOTT EMP Row-X (SX) None 225 Not Blocking 16 SCOTT DEPT Row-X (SX) None 225 Not Blocking SYS@oratest S2>
|
可以看到，會話1在SCOTT.EMP和SCOTT.DEPT表上都加上了3級的行級排它鎖。
提交以上的會話，然后繼續試驗OF特性：
|
會話1： SYS@oratest S1> SELECT * FROM SCOTT.EMP E, SCOTT.DEPT D WHERE E.DEPTNO = D.DEPTNO FOR UPDATE OF SAL ;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO DEPTNO DNAME LOC ---------- ---------- --------- ---------- ------------------- ---------- ---------- ---------- ---------- -------------- ------------- 7369 SMITH CLERK 7902 1980-12-17 00:00:00 800 20 20 RESEARCH DALLAS 7499 ALLEN SALESMAN 7698 1981-02-20 00:00:00 1600 300 30 30 SALES CHICAGO 7521 WARD SALESMAN 7698 1981-02-22 00:00:00 1250 500 30 30 SALES CHICAGO 7566 JONES MANAGER 7839 1981-04-02 00:00:00 2975 20 20 RESEARCH DALLAS 7654 MARTIN SALESMAN 7698 1981-09-28 00:00:00 1250 1400 30 30 SALES CHICAGO 7698 BLAKE MANAGER 7839 1981-05-01 00:00:00 2850 30 30 SALES CHICAGO 7782 CLARK MANAGER 7839 1981-06-09 00:00:00 2450 10 10 ACCOUNTING NEW YORK 7788 SCOTT ANALYST 7566 1987-04-19 00:00:00 3000 20 20 RESEARCH DALLAS 7839 KING PRESIDENT 1981-11-17 00:00:00 5000 10 10 ACCOUNTING NEW YORK 7844 TURNER SALESMAN 7698 1981-09-08 00:00:00 1500 0 30 30 SALES CHICAGO 7876 ADAMS CLERK 7788 1987-05-23 00:00:00 1100 20 20 RESEARCH DALLAS 7900 JAMES CLERK 7698 1981-12-03 00:00:00 950 30 30 SALES CHICAGO 7902 FORD ANALYST 7566 1981-12-03 00:00:00 3000 20 20 RESEARCH DALLAS 7934 MILLER CLERK 7782 1982-01-23 00:00:00 1300 10 10 ACCOUNTING NEW YORK
14 rows selected.
會話2： SYS@oratest S2> SELECT * FROM SCOTT.DEPT FOR UPDATE NOWAIT;
DEPTNO DNAME LOC ---------- -------------- ------------- 10 ACCOUNTING NEW YORK 20 RESEARCH DALLAS 30 SALES CHICAGO 40 OPERATIONS BOSTON
SYS@oratest S2>
SYS@oratest S1> SELECT * FROM V$LOCK A WHERE A.SID IN (16,27) AND A.TYPE IN ('TX','TM') ORDER BY a.SID,a.TYPE;
ADDR KADDR SID TY ID1 ID2 LMODE REQUEST CTIME BLOCK ---------------- ---------------- ---------- -- ---------- ---------- ---------- ---------- ---------- ---------- 00007F73CBCE38D8 00007F73CBCE3938 16 TM 77669 0 3 0 114 0 000000007620A7C0 000000007620A838 16 TX 327698 1138 6 0 114 0 00007F73CBCE38D8 00007F73CBCE3938 27 TM 77667 0 3 0 81 0 000000007620B1D0 000000007620B248 27 TX 131076 1128 6 0 81 0
SYS@oratest S1> SELECT * FROM DBA_DML_LOCKS D WHERE D.SESSION_ID IN (16,27) ORDER BY d.SESSION_ID;
SESSION_ID OWNER NAME MODE_HELD MODE_REQUESTE LAST_CONVERT BLOCKING_OTHERS ---------- --------- ------ ------------- ------------- ------------ --------------- 16 SCOTT EMP Row-X (SX) None 123 Not Blocking 27 SCOTT DEPT Row-X (SX) None 90 Not Blocking
SYS@oratest S1>
|
可以看到，會話1在SCOTT.EMP表上加上了3級的行級排它鎖，而會話2在和SCOTT.DEPT表上加上了3級的行級排它鎖。
|
SQL*Plus: Release 9.2.0.1.0 - Production on 星期一 11月 14 17:29:40 2016
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
請輸入用戶名: sys as sysdba 請輸入口令:
連接到: Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production With the Partitioning, OLAP and Oracle Data Mining options JServer Release 9.2.0.1.0 - Production
SQL> set line 9999 SQL> set pagesize 9999 SQL> select * from scott.emp for update;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO ---------- ---------- --------- ---------- ------------------- ---------- ---------- ---------- 7369 SMITH CLERK 7902 1980-12-17 00:00:00 800 20 7499 ALLEN SALESMAN 7698 1981-02-20 00:00:00 1600 300 30 7521 WARD SALESMAN 7698 1981-02-22 00:00:00 1250 500 30 7566 JONES MANAGER 7839 1981-04-02 00:00:00 2975 20 7654 MARTIN SALESMAN 7698 1981-09-28 00:00:00 1250 1400 30 7698 BLAKE MANAGER 7839 1981-05-01 00:00:00 2850 30 7782 CLARK MANAGER 7839 1981-06-09 00:00:00 2450 10 7788 SCOTT ANALYST 7566 1987-04-19 00:00:00 3000 20 7839 KING PRESIDENT 1981-11-17 00:00:00 5000 10 7844 TURNER SALESMAN 7698 1981-09-08 00:00:00 1500 0 30 7876 ADAMS CLERK 7788 1987-05-23 00:00:00 1100 20 7900 JAMES CLERK 7698 1981-12-03 00:00:00 950 30 7902 FORD ANALYST 7566 1981-12-03 00:00:00 3000 20 7934 MILLER CLERK 7782 1982-01-23 00:00:00 1300 10
已選擇14行。
SQL> select distinct sid from v$mystat;
SID ---------- 10
SQL> SELECT * FROM V$LOCK A WHERE A.SID=10 ORDER BY a.SID,a.TYPE;
ADDR KADDR SID TY ID1 ID2 LMODE REQUEST CTIME BLOCK -------- -------- ---------- -- ---------- ---------- ---------- ---------- ---------- ---------- 67B4E0F8 67B4E10C 10 TM 30139 0 2 0 35 0 67BAB0CC 67BAB1D8 10 TX 131082 2874 6 0 25 0
SQL> SELECT * FROM DBA_DML_LOCKS D WHERE D.SESSION_ID =10 ORDER BY d.SESSION_ID;
SESSION_ID OWNER NAME MODE_HELD MODE_REQUESTE LAST_CONVERT BLOCKING_OTHERS ---------- --------- ------ ------------- ------------- ------------ ------------------ 10 SCOTT EMP Row-S (SS) None 99 Not Blocking
|
可以看到在Oracle 10g之前，SELECT FOR UPDATE獲取的是2級TM鎖，在Oracle 10g及其之后的版本中，SELECT FOR UPDATE獲取的是3級TM鎖。
1. SELECT * FROM TABLE1 FOR UPDATE 鎖定表的所有行，其它會話只能讀不能寫
2. SELECT * FROM TABLE1 WHERE PKID = 1 FOR UPDATE 只鎖定PKID=1的行
3. SELECT * FROM TABLE1 A JOIN TABLE2 B ON A.PKID=B.PKID FOR UPDATE 鎖定兩個表的所有記錄
4. SELECT * FROM TABLE1 A JOIN TABLE2 B ON A.PKID=B.PKID WHERE A.PKID = 10 FOR UPDATE 鎖定兩個表的中滿足條件的行
5. SELECT * FROM TABLE1 A JOIN TABLE2 B ON A.PKID=B.PKID WHERE A.PKID = 10 FOR UPDATE OF A.PKID 只鎖定TABLE1中滿足條件的行
FOR UPDATE 是把所有的表都鎖定。FOR UPDATE OF 根據OF后表的條件鎖定相對應的表。
摘抄自網絡，小麥苗感覺自己對這個部分也沒啥可寫的，主要是包不能編譯的時候需要查詢DBA_DDL_LOCKS視圖，最后殺會話的時候需要穩重一點。
在數據庫的開發過程中，經常碰到包、存儲過程、函數無法編譯或編譯時會導致PL/SQL 無法響應的問題。碰到這種問題，基本上都要重啟數據庫解決，嚴重浪費開發時間。本文將就產生這種現象的原因和解決方案做基本的介紹。
問題分析
從事數據庫開發的都知道鎖的概念，如：執行 Update Table xxx Where xxx 的時候就會產生鎖。這種常見的鎖在Oracle里面被稱為DML鎖。在Oracle中還有一種DDL鎖，主要用來保證存儲過程、表結構、視圖、包等數據庫對象的完整性，這種鎖的信息可以在DBA_DDL_LOCKS中查到。注意：V$LOCKED_OBJECT記錄的是DML鎖信息，DDL鎖的信息不在里面。
對應DDL鎖的是DDL語句，DDL語句全稱數據定義語句（Data Define Language）。用于定義數據的結構或Schema，如：CREATE、ALTER、DROP、TRUNCATE、COMMENT、RENAME。當我們在執行某個存儲過程、或者編譯它的時候Oracle會自動給這個對象加上DDL鎖，同時也會對這個存儲過程所引用的對象加鎖。
舉例：
1、 打開一個PL/SQL，開始調試某個函數（假設為：FUN_CORE_SERVICECALL），并保持在調試狀態
2、 打開一個SQL Window，輸入Select * From dba_ddl_locks a Where a.name = 'FUN_CORE_SERVICECALL' 會發現一行記錄：
3、 打開一個新的PL/SQL，重新編譯這個函數。我們會發現此時已經無法響應了
4、 回到第一個PL/SQL,重新執行Select * From dba_ddl_locks a Where a.name = 'FUN_CORE_SERVICECALL' 我們將會看到如下記錄：
5、 上述的情況表明發生了鎖等待的情況。
當我們試圖編譯、修改存儲過程、函數、包等對數據對象的時候，如果別人也正在編譯或修改他們時就會產生鎖等待；或者我們在編譯某個存儲過程的時候，如果它所引用的數據庫對象正在被修改應該也會產生鎖等待。這種假設有興趣的兄弟可以測試下，不過比較困難。
解決方案
碰到這種問題，如果知道是被誰鎖定了（可以查出來的），可以讓對方盡快把鎖釋放掉；實在查不出來只能手工將這個鎖殺掉了。步驟如下：
1、 首先查出哪些進程鎖住了這個對象，語句如下：
Select b.SID,b.SERIAL#
From dba_ddl_locks a, v$session b
Where a.session_id = b.SID
And a.name = 'FUN_CORE_SERVICECALL';
2、 執行如下語句殺進程：alter system kill session 'sid,serial#' IMMEDIATE;
3、 執行了以上的語句后，有的時候不一定能夠將進程殺掉。這個時候就需要連到數據庫服務器上殺掉服務器端的進程了，查詢語句：
Select spid, osuser, s.program
From v$session s, v$process p
Where s.paddr = p.addr
And s.sid =（上面查出來的SID）
在服務器上執行如下語句：
#kill -9 spid（UNIX平臺）
orakill sid thread（Windows平臺 SID是Oracle的實例名，thread是上面查出來的SID）
執行完4步以后基本上就可以殺掉這些鎖死的進程了，不放心的話可以再執行第一步確認下。
|
SQL> select distinct sid from v$mystat;
SID ---------- 24
SQL> CREATE OR REPLACE PROCEDURE PRO_TESTDDL_LHR AS 2 3 V_COUNT NUMBER; 4 5 BEGIN 6 7 SELECT COUNT(1) INTO V_COUNT FROM SCOTT.EMP_LHR; 8 9 DBMS_LOCK.SLEEP(600); 10 11 END; 12 /
Procedure created.
SQL> exec PRO_TESTDDL_LHR;
====>>>>> 腳本在執行
|
查看DDL鎖：
SELECT * FROM DBA_DDL_LOCKS D WHERE D.SESSION_ID = 24;
SELECT *
FROM V$ACCESS A
WHERE A.SID = 24
AND A.OBJECT IN ('PRO_TESTDDL_LHR', 'EMP_LHR', 'DBMS_LOCK');
版本：11.2.0.3
|
首先建表T_INDEX_161113并插入很多數據 SYS@oratest S1> CREATE TABLE T_INDEX_161113 AS SELECT * FROM DBA_OBJECTS;
Table created.
SYS@oratest S1> INSERT INTO T_INDEX_161113 SELECT * FROM T_INDEX_161113;
75349 rows created.
SYS@oratest S1> INSERT INTO T_INDEX_161113 SELECT * FROM T_INDEX_161113;
150698 rows created.
SYS@oratest S1> INSERT INTO T_INDEX_161113 SELECT * FROM T_INDEX_161113;
301396 rows created.
SYS@oratest S1> INSERT INTO T_INDEX_161113 SELECT * FROM T_INDEX_161113;
602792 rows created.
SYS@oratest S1> COMMIT;
Commit complete. 接著再在該表上創建一個索引 SYS@oratest S1> CREATE INDEX IDX_TEST_LHR ON T_INDEX_161113(OBJECT_NAME);
在創建索引的同時，在會話2上插入一條記錄： SYS@oratest S2> INSERT INTO T_INDEX_161113 SELECT * FROM T_INDEX_161113 WHERE ROWNUM<=1;
====>>>>> 產生了阻塞 在創建索引的同時，查詢相關鎖的信息： SQL> SELECT SID, 2 A.BLOCKING_SESSION, 3 EVENT, 4 A.P1, 5 A.P2, 6 A.P3, 7 CHR(BITAND(P1, -16777216) / 16777215) || 8 CHR(BITAND(P1, 16711680) / 65535) "LOCK", 9 BITAND(P1, 65535) "MODE", 10 (SELECT OBJECT_NAME FROM DBA_OBJECTS D WHERE D.OBJECT_ID = A.P2) OBJECT_NAME 11 FROM GV$SESSION A 12 WHERE A.SID=141;
SID BLOCKING_SESSION EVENT P1 P2 P3 LOCK MODE OBJECT_NAME ---------- ---------------- ----------------------- ---------- ---------- ---------- ---- ---------- ---------------- 142 21 enq: TM - contention 1414332419 77629 0 TM 3 T_INDEX_161113
SQL> SELECT * FROM V$LOCK A WHERE A.SID IN (21,142) AND A.TYPE IN ('TX','TM'); ADDR KADDR SID TY ID1 ID2 LMODE REQUEST CTIME BLOCK ---------------- ---------------- ---------- -- ---------- ---------- ---------- ---------- ---------- ---------- 00007F44001842E0 00007F4400184340 142 TM 77629 0 0 3 2 0 00007F44001842E0 00007F4400184340 21 TM 77629 0 4 0 3 1 00007F44001842E0 00007F4400184340 21 TM 18 0 3 0 3 0 0000000076273C58 0000000076273CD0 21 TX 65567 846 6 0 3 0
SQL> SELECT * FROM DBA_DML_LOCKS D WHERE D.SESSION_ID IN (21, 142); SESSION_ID OWNER NAME MODE_HELD MODE_REQUESTE LAST_CONVERT BLOCKING_OTHERS ---------- --------- -------------------- ------------- ------------- ------------ -------------------- 142 SYS T_INDEX_161113 None Row-X (SX) 2 Not Blocking 21 SYS T_INDEX_161113 Share None 3 Blocking 21 SYS OBJ$ Row-X (SX) None 3 Not Blocking
SQL> SELECT D.OWNER, D.OBJECT_NAME, D.OBJECT_ID, D.OBJECT_TYPE 2 FROM DBA_OBJECTS D 3 WHERE D.OBJECT_ID IN (18, 77629);
OWNER OBJECT_NAME OBJECT_ID OBJECT_TYPE ---------- ---------------------- ---------- ------------------- SYS T_INDEX_161113 77629 TABLE SYS OBJ$ 18 TABLE SQL> SELECT * FROM DBA_DDL_LOCKS D WHERE D.SESSION_ID IN (21, 142) AND D.name NOT IN ('STANDARD','DICTIONARY_OBJ_OWNER','DICTIONARY_OBJ_NAME','UTL_RAW','DBMS_APPLICATION_INFO','SDO_GEOR_DEF','SQL_TXT','DBMS_ASSERT','SDO_GEOR_DEF','TRACE_PUT_LINE','PLITBLM','DICTIONARY_OBJ_TYPE','DDLREPLICATION','DBMS_STANDARD','DBMS_APPLICATION_INFO','UTL_FILE','DDLAUX','DBMS_ASSERT','STANDARD','UTL_RAW','DDLREPLICATION','UTL_FILE','DDLAUX','GGS_MARKER_SEQ','DATABASE','LOGIN_USER','FILTERDDL','DBMS_UTILITY','GGS_DDL_SEQ','SYSEVENT','DBMS_UTILITY','LOGIN_USER','UTL_FILE','DATABASE','SDO_GEOR_DEF','UTL_RAW','GGS_DDL_SEQ','SDO_GEOR_DEF','DICTIONARY_OBJ_TYPE','UTL_RAW','DDLREPLICATION','DBMS_UTILITY','SYSEVENT','IS_VPD_ENABLED','DBMS_APPLICATION_INFO','FILTERDDL','DDLREPLICATION','STANDARD','DDLAUX','GGS_MARKER_SEQ','DDLAUX','SQL_TXT','PLITBLM','AW_DROP_PROC','DBMS_APPLICATION_INFO','DBMS_UTILITY','DICTIONARY_OBJ_OWNER','DICTIONARY_OBJ_NAME','STANDARD','DBMS_STANDARD','TRACE_PUT_LINE','UTL_FILE');
SESSION_ID OWNER NAME TYPE MODE_HELD MODE_REQU ---------- --------- ----------------- ----------- --------- --------- 21 SYS 73 Share None 21 SYS IDX_TEST_LHR Index Exclusive None
|
可以發現在會話1中，在創建索引的過程中會生成2個TM鎖，鎖類別分別為4和3，根據查詢結果發現lmode=4的object_id為77629的對象對應的是T_INDEX_161113這個表，對應的是TM的S鎖。另一個lmode=3的鎖對象是系統基表OBJ$表，允許其它會話對該表執行DML操作。可以得出這樣一個結論:當對表進行創建索引操作時，會伴隨出現LMODE=4的S鎖。根據鎖的兼容模式可以發現S鎖和任何DML操作都是沖突的！所以，尤其是在生產上，當在一個很大的表上進行索引創建的時候，任何對該表的DML操作都會被夯住!!!
從DBA_DDL_LOCKS視圖可以看到，建索引的同時有6級排它DDL鎖。
版本：11.2.0.3
接著上面的實驗，重建索引的時候加上ONLINE，由于會話斷開了，重新開2個會話，會話1為22，會話2為142：
|
SYS@oratest S1> ALTER INDEX IDX_TEST_LHR REBUILD ONLINE;
在創建索引的同時，在會話2上插入一條記錄： SYS@oratest S2> INSERT INTO T_INDEX_161113 SELECT * FROM T_INDEX_161113 WHERE ROWNUM<=1;
1 row created.
====>>>>> 加上ONLINE后無阻塞產生 在創建索引的同時，查詢相關鎖的信息： SQL> SELECT * FROM V$LOCK A WHERE A.SID IN (22,141) AND A.TYPE IN ('TX','TM');
ADDR KADDR SID TY ID1 ID2 LMODE REQUEST CTIME BLOCK ---------------- ---------------- ---------- -- ---------- ---------- ---------- ---------- ---------- ---------- 00000000774D9C08 00000000774D9C60 22 TX 327688 1122 0 4 761 0 00007FD883B38350 00007FD883B383B0 22 TM 77629 0 2 0 768 0 00007FD883B38350 00007FD883B383B0 22 TM 77643 0 4 0 767 0 0000000076274668 00000000762746E0 22 TX 196612 1119 6 0 768 0 0000000076236E38 0000000076236EB0 141 TX 327688 1122 6 0 763 1 00007FD883B38350 00007FD883B383B0 141 TM 77629 0 3 0 763 0
6 rows selected.
SQL> SELECT * FROM DBA_DML_LOCKS D WHERE D.SESSION_ID IN (22,141);
SESSION_ID OWNER NAME MODE_HELD MODE_REQUESTE LAST_CONVERT BLOCKING_OTHERS ---------- ----------- -------------------- ------------- ------------- ------------ ---------------------------------------- 141 SYS T_INDEX_161113 Row-X (SX) None 625 Not Blocking 22 SYS T_INDEX_161113 Row-S (SS) None 630 Not Blocking 22 SYS SYS_JOURNAL_77631 Share None 629 Not Blocking SQL> SELECT D.OWNER, D.OBJECT_NAME, D.OBJECT_ID, D.OBJECT_TYPE 2 FROM DBA_OBJECTS D 3 WHERE D.OBJECT_ID IN (77629, 77643);
OWNER OBJECT_NAME OBJECT_ID OBJECT_TYPE ---------- ------------------------- ---------- ------------------- SYS SYS_JOURNAL_77631 77643 TABLE SYS T_INDEX_161113 77629 TABLE
SQL> SELECT * FROM DBA_DDL_LOCKS D WHERE D.SESSION_ID IN (22,141) AND D.name NOT IN ('STANDARD','DICTIONARY_OBJ_OWNER','DICTIONARY_OBJ_NAME','UTL_RAW','DBMS_APPLICATION_INFO','SDO_GEOR_DEF','SQL_TXT','DBMS_ASSERT','SDO_GEOR_DEF','TRACE_PUT_LINE','PLITBLM','DICTIONARY_OBJ_TYPE','DDLREPLICATION','DBMS_STANDARD','DBMS_APPLICATION_INFO','UTL_FILE','DDLAUX','DBMS_ASSERT','STANDARD','UTL_RAW','DDLREPLICATION','UTL_FILE','DDLAUX','GGS_MARKER_SEQ','DATABASE','LOGIN_USER','FILTERDDL','DBMS_UTILITY','GGS_DDL_SEQ','SYSEVENT','DBMS_UTILITY','LOGIN_USER','UTL_FILE','DATABASE','SDO_GEOR_DEF','UTL_RAW','GGS_DDL_SEQ','SDO_GEOR_DEF','DICTIONARY_OBJ_TYPE','UTL_RAW','DDLREPLICATION','DBMS_UTILITY','SYSEVENT','IS_VPD_ENABLED','DBMS_APPLICATION_INFO','FILTERDDL','DDLREPLICATION','STANDARD','DDLAUX','GGS_MARKER_SEQ','DDLAUX','SQL_TXT','PLITBLM','AW_DROP_PROC','DBMS_APPLICATION_INFO','DBMS_UTILITY','DICTIONARY_OBJ_OWNER','DICTIONARY_OBJ_NAME','STANDARD','DBMS_STANDARD','TRACE_PUT_LINE','UTL_FILE'); no rows selected
SQL> SELECT SID, 2 A.BLOCKING_SESSION, 3 EVENT, 4 A.P1, 5 A.P2, 6 A.P3, 7 CHR(BITAND(P1, -16777216) / 16777215) || 8 CHR(BITAND(P1, 16711680) / 65535) "LOCK", 9 BITAND(P1, 65535) "MODE", 10 (SELECT b.SQL_TEXT FROM v$sql b WHERE b.SQL_ID=NVL(a.sql_id,a.PREV_SQL_ID)) SQL_TEXT 11 FROM GV$SESSION A 12 WHERE A.SID IN (141,22);
SID BLOCKING_SESSION EVENT P1 P2 P3 LOCK MODE SQL_TEXT ---------- ---------------- ---------------------------------- ---------- ---------- ---------- ---- ---------- ----------------------------------------------------------------------- 22 141 enq: TX - row lock contention 1415053316 327688 1122 TX 4 ALTER INDEX IDX_TEST_LHR REBUILD ONLINE 141 SQL*Net message from client 1650815232 1 0 be 28928 INSERT INTO T_INDEX_161113 SELECT * FROM T_INDEX_161113 WHERE ROWNUM<=1
|
可以發現在會話1中，在加上ONLINE重建索引的過程中會生成2個TM鎖，鎖類別分別為2和4，根據查詢結果發現lmode=2的object_id為77629的對象對應的是T_INDEX_161113這個表，對應的是TM的Row-S (SS)鎖即行級共享鎖，該鎖允許其它會話對該表執行DML操作。另一個lmode=4的鎖對象是SYS_JOURNAL_77631，應該為系統臨時創建的對象，對應的是TM的S鎖。
在會話2中，TX為6的鎖，阻塞了其它會話，在這里其實是阻塞了會話1的重建索引的操作。
可以得出這樣一個結論:當對表進行創建或重建索引操作時，可以加上ONLINE選項，不阻塞其它會話的DML操作，但是在創建或重建索引的過程中，其它的會話產生的事務會阻塞索引的創建或重建操作，所以必須結束其它會話的事務才能讓創建或重建索引的操作完成。
注意：在加上ONLINE選項創建索引的過程中，若手動CTRL+C取消后，可能導致索引被鎖，出現ORA-08104: this index object 77645 is being online built or rebuilt的錯誤，這個時候可以利用如下的腳本清理對象，77645為對象的OBJECT_ID：
|
DECLARE DONE BOOLEAN; BEGIN DONE := DBMS_REPAIR.ONLINE_INDEX_CLEAN(77645); END; |
版本為：10.2.0.1.0
重新開3個會話，會話1為143，會話2為152，會話3為158：
|
SYS@lhrdb S1> alter index IDX_TEST1_LHR rebuild online;
在創建索引的同時，在會話2上插入一條記錄： SYS@oratest S2> INSERT INTO T_INDEX_161113 SELECT * FROM T_INDEX_161113 WHERE ROWNUM<=1;
1 row created.
====>>>>> 加上ONLINE后仍然會阻塞DML語句，若無阻塞可以重新連接會話2再執行插入操作 在創建索引的同時，在會話3上插入一條記錄： SYS@oratest S2> INSERT INTO T_INDEX_161113 SELECT * FROM T_INDEX_161113 WHERE ROWNUM<=1;
1 row created.
====>>>>> 加上ONLINE后仍然會阻塞DML語句，若無阻塞可以重新連接會話3再執行插入操作
在創建索引的同時，查詢相關鎖的信息： SQL> SELECT * FROM V$LOCK A WHERE A.SID IN (143,152,158) ORDER BY a.SID,a.TYPE;
ADDR KADDR SID TY ID1 ID2 LMODE REQUEST CTIME BLOCK ---------------- ---------------- ---------- -- ---------- ---------- ---------- ---------- ---------- ---------- 00000000704A7850 00000000704A7870 143 DL 53121 0 3 0 144 0 00000000704A7980 00000000704A79A0 143 DL 53121 0 3 0 144 0 00000000703B8630 00000000703B8658 143 TM 53121 0 2 4 161 0 00000000703B8730 00000000703B8758 143 TM 53156 0 4 0 161 0 000000006F49F268 000000006F49F3F0 143 TX 196651 452 6 0 159 0 00000000703B8930 00000000703B8958 152 TM 53121 0 0 3 141 0 00000000703B8830 00000000703B8858 158 TM 53121 0 3 0 153 1 000000006F45DC78 000000006F45DE00 158 TX 262170 423 6 0 153 0
8 rows selected. SQL> SELECT * FROM V$lock_Type d WHERE d.TYPE='DL';
TYPE NAME ID1_TAG DESCRIPTION -------- ---------------------------------- ----------- ------------ DL Direct Loader Index Creation object # Lock to prevent index DDL during direct load
SQL> SELECT * FROM V$LOCK A WHERE A.SID IN (143,152,158) AND A.TYPE IN ('TX','TM') ORDER BY a.SID,a.TYPE;
ADDR KADDR SID TY ID1 ID2 LMODE REQUEST CTIME BLOCK ---------------- ---------------- ---------- -- ---------- ---------- ---------- ---------- ---------- ---------- 00000000703B8630 00000000703B8658 143 TM 53121 0 2 4 161 0 00000000703B8730 00000000703B8758 143 TM 53156 0 4 0 161 0 000000006F49F268 000000006F49F3F0 143 TX 196651 452 6 0 159 0 00000000703B8930 00000000703B8958 152 TM 53121 0 0 3 141 0 00000000703B8830 00000000703B8858 158 TM 53121 0 3 0 153 1 000000006F45DC78 000000006F45DE00 158 TX 262170 423 6 0 153 0
6 rows selected. SQL> SELECT * FROM DBA_DML_LOCKS D WHERE D.SESSION_ID IN (143,152,158) ORDER BY d.SESSION_ID;
SESSION_ID OWNER NAME MODE_HELD MODE_REQUESTE LAST_CONVERT BLOCKING_OTHERS ---------- ------------------------------ ------------------------------ ------------- ------------- ------------ ---------------------------------------- 143 SYS T_INDEX_161113 Row-S (SS) Share 335 Not Blocking 143 SYS SYS_JOURNAL_53122 Share None 335 Not Blocking 152 SYS T_INDEX_161113 None Row-X (SX) 315 Blocking 158 SYS T_INDEX_161113 Row-X (SX) None 327 Blocking SQL> SELECT D.OWNER, D.OBJECT_NAME, D.OBJECT_ID, D.OBJECT_TYPE 2 FROM DBA_OBJECTS D 3 WHERE D.OBJECT_ID IN (53121, 53156);
OWNER OBJECT_NAME OBJECT_ID OBJECT_TYPE --------- ---------------------- ---------- ------------------- SYS T_INDEX_161113 53121 TABLE SYS SYS_JOURNAL_53122 53156 TABLE
SQL> SELECT d.owner,d.table_name,d.iot_type FROM dba_tables d WHERE d.table_name='SYS_JOURNAL_53122';
OWNER TABLE_NAME IOT_TYPE ------------------------------ ------------------------------ ------------ SYS SYS_JOURNAL_53122 IOT
SQL> SQL> SELECT * FROM DBA_DDL_LOCKS D WHERE D.SESSION_ID IN (143,152,158) AND D.name NOT IN ('STANDARD','DICTIONARY_OBJ_OWNER','DICTIONARY_OBJ_NAME','UTL_RAW','DBMS_APPLICATION_INFO','SDO_GEOR_DEF','SQL_TXT','DBMS_ASSERT','SDO_GEOR_DEF','TRACE_PUT_LINE','PLITBLM','DICTIONARY_OBJ_TYPE','DDLREPLICATION','DBMS_STANDARD','DBMS_APPLICATION_INFO','UTL_FILE','DDLAUX','DBMS_ASSERT','STANDARD','UTL_RAW','DDLREPLICATION','UTL_FILE','DDLAUX','GGS_MARKER_SEQ','DATABASE','LOGIN_USER','FILTERDDL','DBMS_UTILITY','GGS_DDL_SEQ','SYSEVENT','DBMS_UTILITY','LOGIN_USER','UTL_FILE','DATABASE','SDO_GEOR_DEF','UTL_RAW','GGS_DDL_SEQ','SDO_GEOR_DEF','DICTIONARY_OBJ_TYPE','UTL_RAW','DDLREPLICATION','DBMS_UTILITY','SYSEVENT','IS_VPD_ENABLED','DBMS_APPLICATION_INFO','FILTERDDL','DDLREPLICATION','STANDARD','DDLAUX','GGS_MARKER_SEQ','DDLAUX','SQL_TXT','PLITBLM','AW_DROP_PROC','DBMS_APPLICATION_INFO','DBMS_UTILITY','DICTIONARY_OBJ_OWNER','DICTIONARY_OBJ_NAME','STANDARD','DBMS_STANDARD','TRACE_PUT_LINE','UTL_FILE','DBMS_SYS_SQL','DBMS_XDBZ0','DBMS_SYS_SQL','DBMS_SQL','DBMS_SQL','DBMS_XDBZ0');
no rows selected SQL> SELECT SID, 2 A.BLOCKING_SESSION, 3 EVENT, 4 A.P1, 5 A.P2, 6 A.P3, 7 CHR(BITAND(P1, -16777216) / 16777215) || 8 CHR(BITAND(P1, 16711680) / 65535) "LOCK", 9 BITAND(P1, 65535) "MODE", 10 (SELECT b.SQL_TEXT FROM v$sql b WHERE b.SQL_ID=NVL(a.sql_id,a.PREV_SQL_ID)) SQL_TEXT 11 FROM GV$SESSION A 12 WHERE A.SID IN (143,152,158);
SID BLOCKING_SESSION EVENT P1 P2 P3 LOCK MODE SQL_TEXT ---------- ---------------- ---------------------------- ---------- ---------- ---------- ---- ---------- ----------------------------------------------- 143 158 enq: TM - contention 1414332420 53121 0 TM 4 alter index IDX_TEST1_LHR rebuild online 152 143 enq: TM - contention 1414332419 53121 0 TM 3 INSERT INTO T_INDEX_161113 SELECT * FROM T_INDEX_161113 WHERE ROWNUM<=1 158 SQL*Net message from client 1650815232 1 0 be 28928 INSERT INTO T_INDEX_161113 SELECT * FROM T_INDEX_161113 WHERE ROWNUM<=1
|
可以發現在會話1中，在加上ONLINE重建索引的過程中會生成2個TM鎖，鎖類別分別為2和4，根據查詢結果發現lmode=2的object_id為53121的對象對應的是T_INDEX_161113這個表，對應的是TM的Row-S (SS)鎖即行級共享鎖，該鎖允許其它會話對該表執行DML操作，但是該會話在請求模式為4的S鎖。另一個lmode=4的鎖對象是SYS_JOURNAL_53122，為系統臨時創建的索引組織表（IOT），對應的是TM的S鎖。
在會話2中，請求3級TM鎖。會阻塞關系可以看出，會話3阻塞了會話1，而會話1阻塞了會話2，所以提交會話3即可讓索引創建完成。
版本為：10.2.0.1.0
重新開3個會話，會話1為143，會話2為152，會話3為158，會話1插入一條記錄：
|
SYS@lhrdb S1> INSERT INTO T_INDEX_161113 SELECT * FROM T_INDEX_161113 WHERE ROWNUM<=1;
1 row created.
在會話2上采用ONLINE建立索引： SYS@lhrdb S2> alter index IDX_TEST1_LHR rebuild online;
====>>>>> 加上ONLINE后仍然會被阻塞 在創建索引的同時，查詢相關鎖的信息： SQL> SELECT * FROM V$LOCK A WHERE A.SID IN (143,152) ORDER BY a.SID,a.TYPE;
ADDR KADDR SID TY ID1 ID2 LMODE REQUEST CTIME BLOCK ---------------- ---------------- ---------- -- ---------- ---------- ---------- ---------- ---------- ---------- 00000000703B8630 00000000703B8658 143 TM 53121 0 3 0 1119 1 000000006F495E38 000000006F495FC0 143 TX 524318 484 6 0 1119 0 00000000704A7980 00000000704A79A0 152 DL 53121 0 3 0 1113 0 00000000704A7850 00000000704A7870 152 DL 53121 0 3 0 1113 0 00000000703B8730 00000000703B8758 152 TM 53121 0 2 4 1113 0 00000000703B8830 00000000703B8858 152 TM 53162 0 4 0 1112 0
6 rows selected. SQL> SELECT * FROM V$lock_Type d WHERE d.TYPE='DL';
TYPE NAME ID1_TAG DESCRIPTION -------- ---------------------------------- ----------- ------------ DL Direct Loader Index Creation object # Lock to prevent index DDL during direct load
SQL> SELECT * FROM DBA_DML_LOCKS D WHERE D.SESSION_ID IN (143,152) ORDER BY d.SESSION_ID;
SESSION_ID OWNER NAME MODE_HELD MODE_REQUESTE LAST_CONVERT BLOCKING_OTHERS ---------- ------------------------------ ------------------------------ ------------- ------------- ------------ ---------------------------------------- 143 SYS T_INDEX_161113 Row-X (SX) None 1176 Blocking 152 SYS SYS_JOURNAL_53122 Share None 1169 Not Blocking 152 SYS T_INDEX_161113 Row-S (SS) Share 1170 Not Blocking SQL> SELECT D.OWNER, D.OBJECT_NAME, D.OBJECT_ID, D.OBJECT_TYPE 2 FROM DBA_OBJECTS D 3 WHERE D.OBJECT_ID IN (53121, 53162);
OWNER OBJECT_NAME OBJECT_ID OBJECT_TYPE --------- ---------------------- ---------- ------------------- SYS T_INDEX_161113 53121 TABLE SYS SYS_JOURNAL_53122 53162 TABLE
SQL> SELECT d.owner,d.table_name,d.iot_type FROM dba_tables d WHERE d.table_name='SYS_JOURNAL_53122';
OWNER TABLE_NAME IOT_TYPE ------------------------------ ------------------------------ ------------ SYS SYS_JOURNAL_53122 IOT
SQL> SQL> SELECT * FROM DBA_DDL_LOCKS D WHERE D.SESSION_ID IN (143,152,158) AND D.name NOT IN ('ALERT_QUE_R','AQ$_ALERT_QT_E','AW_DROP_PROC','DATABASE','DBMS_APPLICATION_INFO','DBMS_BACKUP_RESTORE','DBMS_HA_ALERTS_PRVT','DBMS_OUTPUT','DBMS_PRVT_TRACE','DBMS_RCVMAN','DBMS_SQL','DBMS_STANDARD','DBMS_SYS_SQL','DBMS_TRANSACTION','DBMS_UTILITY','DBMS_XDBZ0','DICTIONARY_OBJ_NAME','DICTIONARY_OBJ_OWNER','PLITBLM','SCHEDULER$_INSTANCE_S','STANDARD');
no rows selected SQL> SELECT SID, 2 A.BLOCKING_SESSION, 3 EVENT, 4 A.P1, 5 A.P2, 6 A.P3, 7 CHR(BITAND(P1, -16777216) / 16777215) || 8 CHR(BITAND(P1, 16711680) / 65535) "LOCK", 9 BITAND(P1, 65535) "MODE", 10 (SELECT b.SQL_TEXT FROM v$sql b WHERE b.SQL_ID=NVL(a.sql_id,a.PREV_SQL_ID)) SQL_TEXT 11 FROM GV$SESSION A 12 WHERE A.SID IN (143,152);
SID BLOCKING_SESSION EVENT P1 P2 P3 LOCK MODE SQL_TEXT ---------- ---------------- ---------------------------- ---------- ---------- ---------- ---- ---------- ----------------------------------------------- 143 SQL*Net message from client 1650815232 1 0 be 28928 152 143 enq: TM - contention 1414332420 53121 0 TM 4 alter index IDX_TEST1_LHR rebuild online |
從上面的結果可以知道，會話2即創建索引的會話一共出現了4個鎖，兩個DL鎖，一個針對表T_INDEX_161113的TM鎖，一個是online rebuild index時需要的一個中間表的TM鎖，中間表用于記錄rebuild期間的增量數據，原理類似于物化視圖日志，其object_id為53162，這是一個索引組織表(IOT)，從這里我們也可以發現IOT的優點和適合的場合，這張中間表只有插入，不會有刪除和修改操作，而且只有主鍵條件查詢，正是IOT最合適的場景。
會話2在請求一個模式為4的TM鎖，模式4會阻塞這個表上的所有DML操作，所以這時再往這個表上執行DML也會掛起。
會話3刪除一條語句：
|
SYS@lhrdb S3> delete from T_INDEX_161113 where rownum<=1;
====>>>>> 有阻塞 查詢鎖的資源： SQL> SELECT * FROM V$LOCK A WHERE A.SID IN (143,152,158) ORDER BY a.SID,a.TYPE;
ADDR KADDR SID TY ID1 ID2 LMODE REQUEST CTIME BLOCK ---------------- ---------------- ---------- -- ---------- ---------- ---------- ---------- ---------- ---------- 00000000703B8630 00000000703B8658 143 TM 53121 0 3 0 7573 1 000000006F495E38 000000006F495FC0 143 TX 524318 484 6 0 7573 0 00000000704A7850 00000000704A7870 152 DL 53121 0 3 0 7567 0 00000000704A7980 00000000704A79A0 152 DL 53121 0 3 0 7567 0 00000000703B8830 00000000703B8858 152 TM 53162 0 4 0 7566 0 00000000703B8730 00000000703B8758 152 TM 53121 0 2 4 7567 0 00000000703B8930 00000000703B8958 158 TM 53121 0 0 3 165 0
7 rows selected.
SQL> SELECT * FROM DBA_DML_LOCKS D WHERE D.SESSION_ID IN (143,152,158) ORDER BY d.SESSION_ID;
SESSION_ID OWNER NAME MODE_HELD MODE_REQUESTE LAST_CONVERT BLOCKING_OTHERS ---------- ---------- ------------------------------ ------------- ------------- ------------ --------------- 143 SYS T_INDEX_161113 Row-X (SX) None 7582 Blocking 152 SYS T_INDEX_161113 Row-S (SS) Share 7576 Not Blocking 152 SYS SYS_JOURNAL_53122 Share None 7575 Not Blocking 158 SYS T_INDEX_161113 None Row-X (SX) 174 Blocking
SQL> SELECT * FROM DBA_DDL_LOCKS D WHERE D.SESSION_ID IN (143,152,158) AND D.name NOT IN ('ALERT_QUE_R','AQ$_ALERT_QT_E','AW_DROP_PROC','DATABASE','DBMS_APPLICATION_INFO','DBMS_BACKUP_RESTORE','DBMS_HA_ALERTS_PRVT','DBMS_OUTPUT','DBMS_PRVT_TRACE','DBMS_RCVMAN','DBMS_SQL','DBMS_STANDARD','DBMS_SYS_SQL','DBMS_TRANSACTION','DBMS_UTILITY','DBMS_XDBZ0','DICTIONARY_OBJ_NAME','DICTIONARY_OBJ_OWNER','PLITBLM','SCHEDULER$_INSTANCE_S','STANDARD');
no rows selected
SQL> SELECT SID, 2 A.BLOCKING_SESSION, 3 EVENT, 4 A.P1, 5 A.P2, 6 A.P3, 7 CHR(BITAND(P1, -16777216) / 16777215) || 8 CHR(BITAND(P1, 16711680) / 65535) "LOCK", 9 BITAND(P1, 65535) "MODE", 10 (SELECT b.SQL_TEXT FROM v$sql b WHERE b.SQL_ID=NVL(a.sql_id,a.PREV_SQL_ID)) SQL_TEXT 11 FROM GV$SESSION A 12 WHERE A.SID IN (143,152,158);
SID BLOCKING_SESSION EVENT P1 P2 P3 LOCK MODE SQL_TEXT ---------- ---------------- ------------------------------ ---------- ---------- ---------- ---- ---------- ---------------------------------------------- 143 SQL*Net message from client 1650815232 1 0 be 28928 152 143 enq: TM - contention 1414332420 53121 0 TM 4 alter index IDX_TEST1_LHR rebuild online 158 152 enq: TM - contention 1414332419 53121 0 TM 3 delete from T_INDEX_161113 where rownum<=1
SQL>
|
會話3請求模式為3的TM鎖無法獲得，會話被阻塞。這是因為鎖請求是需要排隊的，即使會話3和會話1是可以并發的，但由于會話2先請求鎖并進入等待隊列，后來的會話3也只好進入隊列等待。所以，如果在執行rebuild index online前有長事務，并且并發量比較大，則一旦執行alter index rebuild online，可能因為長事務阻塞，可能導致系統瞬間出現大量的鎖，對于壓力比較大的系統，這是一個不小的風險。這是需要迅速找出導致阻塞的會話kill掉，rebuild index online一旦執行，不可輕易中斷，否則可能遇到ORA-08104。
從會話級別可以看出，會話1阻塞了會話2，會話2阻塞了會話3，在會話1執行rollback，可以發現很短時間內會話3也正常執行完畢，說明會話2持有模式4的TM鎖的時間很短，然后在rebuild online的進行過程中，對表加的是模式為2的TM鎖，所以這段時間不會阻塞DML操作：
回滾會話1，然后觀察鎖的情況：
|
SQL> SELECT * FROM V$LOCK A WHERE A.SID IN (143,152,158) ORDER BY a.SID,a.TYPE;
ADDR KADDR SID TY ID1 ID2 LMODE REQUEST CTIME BLOCK ---------------- ---------------- ---------- -- ---------- ---------- ---------- ---------- ---------- ---------- 00000000704A7850 00000000704A7870 152 DL 53121 0 3 0 8219 0 00000000704A7980 00000000704A79A0 152 DL 53121 0 3 0 8219 0 00000000703B8730 00000000703B8758 152 TM 53121 0 2 4 238 0 00000000703B8830 00000000703B8858 152 TM 53162 0 4 0 8218 0 000000006FFFDEB8 000000006FFFDF18 152 TS 0 4257321 6 0 237 0 000000006F4A7558 000000006F4A76E0 152 TX 262184 426 6 0 237 0 00000000703B8930 00000000703B8958 158 TM 53121 0 3 0 238 1 000000006F45DC78 000000006F45DE00 158 TX 589824 470 6 0 238 0
8 rows selected.
|
會話2又開始在請求模式4的TM鎖，被會話3阻塞!這時在會話1再執行DML操作，同樣會被會話2阻塞，進入鎖等待隊列。
在會話3執行rollback或者commit以后，會話2和會話3都很快執行完畢。
|
會話3： SYS@lhrdb S3> rollback;
Rollback complete.
會話2： SYS@lhrdb S2> alter index IDX_TEST1_LHR rebuild online;
Index altered.
SYS@lhrdb S2> SYS@lhrdb S2> SYS@lhrdb S2>
|
從上面的試驗可以發現，雖然rebuild index online在執行期間只持有模式2的TM鎖，不會阻塞DML操作，但在操作的開始和結束階段，是需要短暫的持有模式為4的TM鎖的，這段會阻塞表上的所有DML操作。我們在做rebuild index online的時候，一定要在開始和結束階段觀察系統中是否有長事務的存儲，對于并發量較大的系統，最嚴重的后果，可能在這兩個關鍵點導致數據庫產生大量鎖等待，系統負載飆升，甚至宕機。
版本為：11.2.0.3.0
開3個會話，會話1為16，會話2為27，會話3為150，會話1刪除一條記錄：
|
SYS@oratest S1> delete from T_INDEX_161113 where rownum<=1;
1 row deleted.
在會話2上采用ONLINE建立索引： SYS@lhrdb S2> alter index IDX_TEST_LHR rebuild online;
====>>>>> 會話2掛起 在創建索引的同時，查詢相關鎖的信息： SYS@oratest S3> SELECT * FROM V$LOCK A WHERE A.SID IN (16,27) ORDER BY a.SID,a.TYPE;
ADDR KADDR SID TY ID1 ID2 LMODE REQUEST CTIME BLOCK ---------------- ---------------- ---------- -- ---------- ---------- ---------- ---------- ---------- ---------- 00000000774DA148 00000000774DA1A0 16 AE 100 0 4 0 17039 0 00007F95B6CC6C88 00007F95B6CC6CE8 16 TM 77629 0 3 0 4034 0 000000007620A7C0 000000007620A838 16 TX 131076 1126 6 0 4034 1 00000000774D9410 00000000774D9468 27 AE 100 0 4 0 18569 0 00000000774D9250 00000000774D92A8 27 DL 77629 0 3 0 115 0 00000000774DA4C8 00000000774DA520 27 DL 77629 0 3 0 115 0 00000000774DA5A8 00000000774DA600 27 OD 77631 0 6 0 115 0 00000000774D9A30 00000000774D9A88 27 OD 77629 0 4 0 115 0 00007F95B6CC6C88 00007F95B6CC6CE8 27 TM 77629 0 2 0 115 0 00007F95B6CC6C88 00007F95B6CC6CE8 27 TM 77665 0 4 0 115 0 00000000774D9090 00000000774D90E8 27 TO 68064 1 3 0 16833 0 0000000076218728 00000000762187A0 27 TX 196627 1131 6 0 115 0 00000000774D9B10 00000000774D9B68 27 TX 131076 1126 0 4 115 0
13 rows selected.
SYS@oratest S3> SELECT * FROM V$LOCK A WHERE A.SID IN (16,27) AND A.TYPE IN ('TX','TM') ORDER BY a.SID,a.TYPE;
ADDR KADDR SID TY ID1 ID2 LMODE REQUEST CTIME BLOCK ---------------- ---------------- ---------- -- ---------- ---------- ---------- ---------- ---------- ---------- 00007F95B6CC5588 00007F95B6CC55E8 16 TM 77629 0 3 0 4071 0 000000007620A7C0 000000007620A838 16 TX 131076 1126 6 0 4071 1 00007F95B6CC5588 00007F95B6CC55E8 27 TM 77629 0 2 0 152 0 00007F95B6CC5588 00007F95B6CC55E8 27 TM 77665 0 4 0 152 0 00000000774D9B10 00000000774D9B68 27 TX 131076 1126 0 4 152 0 0000000076218728 00000000762187A0 27 TX 196627 1131 6 0 152 0
6 rows selected.
SYS@oratest S3> SELECT * FROM V$LOCK_TYPE D WHERE D.TYPE IN ('AE','DL','OD','TO');
TYPE NAME ID1_TAG DESCRIPTION ------- ------------------------------ --------------- ------------------------------------------ --- ----------- DL Direct Loader Index Creation object # Lock to prevent index DDL during direct load AE Edition Lock edition obj# Prevent Dropping an edition in use OD Online DDLs object # Lock to prevent concurrent online DDLs TO Temp Object object # Synchronizes DDL and DML operations on a temp object
SYS@oratest S3> SELECT D.OWNER, D.OBJECT_NAME, D.OBJECT_ID, D.OBJECT_TYPE 2 FROM DBA_OBJECTS D 3 WHERE D.OBJECT_ID IN (77665, 77629);
OWNER OBJECT_NAME OBJECT_ID OBJECT_TYPE ------- ------------------- ---------- ------------------- SYS SYS_JOURNAL_77631 77665 TABLE SYS T_INDEX_161113 77629 TABLE
SYS@oratest S3>
SYS@oratest S3> SELECT * FROM DBA_DML_LOCKS D WHERE D.SESSION_ID IN (16,27) ORDER BY d.SESSION_ID;
SESSION_ID OWNER NAME MODE_HELD MODE_REQUESTE LAST_CONVERT BLOCKING_OTHERS ---------- --------- ------------------ ------------- ------------- ------------ --------------- 16 SYS T_INDEX_161113 Row-X (SX) None 4093 Not Blocking 27 SYS SYS_JOURNAL_77631 Share None 174 Not Blocking 27 SYS T_INDEX_161113 Row-S (SS) None 174 Not Blocking
SYS@oratest S3> SELECT * FROM DBA_DDL_LOCKS D WHERE D.SESSION_ID IN (16,27) AND D.name NOT IN ('ALERT_QUE_R','AQ$_ALERT_QT_E','AW_DROP_PROC','DATABASE','DBMS_APPLICATION_INFO','DBMS_BACKUP_RESTORE','DBMS_HA_ALERTS_PRVT','DBMS_OUTPUT','DBMS_PRVT_TRACE','DBMS_RCVMAN','DBMS_SQL','DBMS_STANDARD','DBMS_SYS_SQL','DBMS_TRANSACTION','DBMS_UTILITY','DBMS_XDBZ0','DICTIONARY_OBJ_NAME','DICTIONARY_OBJ_OWNER','PLITBLM','SCHEDULER$_INSTANCE_S','STANDARD','SDO_GEOR_DEF','SQL_TXT');
no rows selected
SYS@oratest S3> SELECT SID, 2 A.BLOCKING_SESSION, 3 EVENT, 4 A.P1, 5 A.P2, 6 A.P3, 7 CHR(BITAND(P1, -16777216) / 16777215) || 8 CHR(BITAND(P1, 16711680) / 65535) "LOCK", 9 BITAND(P1, 65535) "MODE", 10 (SELECT b.SQL_TEXT FROM v$sql b WHERE b.SQL_ID=NVL(a.sql_id,a.PREV_SQL_ID)) SQL_TEXT 11 FROM GV$SESSION A 12 WHERE A.SID IN (16,27);
SID BLOCKING_SESSION EVENT P1 P2 P3 LOCK MODE SQL_TEXT ---------- ---------------- ---------------------------------- ---------- ---------- ---------- ---- ---------- --------------------------------------------- 16 SQL*Net message from client 1650815232 1 0 be 28928 delete from T_INDEX_161113 where rownum<=1 27 16 enq: TX - row lock contention 1415053316 131076 1126 TX 4 alter index IDX_TEST_LHR rebuild online
|
可以看到會話2正在請求一個模式為4的TX鎖，注意和Oracle 10g請求的TM鎖是不一樣的，而且在我們以前的概念中，TX鎖的模式都是6，這里出現了模式4的TX鎖請求，應該是Oracle 11g中新引入的。那么模式4的TX鎖和TM鎖有什么不同呢？我們繼續前面的實驗步驟：
|
SYS@oratest S3> delete from T_INDEX_161113 where object_id=2;
16 rows deleted.
|
會話3的DML操作順利完成，沒有被阻塞。而在10g當中，會話3是會被會話2請求的TM鎖所阻塞的，這一點改進是非常有意思的，這樣即使rebuid online操作被會話1的長事務阻塞，其他會話的DML操作，只要不和會話1沖突，都可以繼續操作，在Oracle 10g及以前版本中的執行rebuild index online而造成鎖等待的風險被大大的降低了。
依次提交會話1和會話3，則會話2成功完成。
Oracle 11g在很多細節方面確實做了不少的優化，而且像這樣的優化，對于提高系統的高可用性的好處是不言而喻的，在Oracle 11g中，執行rebuild index online的風險將比10g以及更老版本中小得多，因為從頭至尾都不再阻塞DML操作了，終于可以算得上名副其實的online操作了。
使用10704事件跟蹤以下四類操作并對比跟蹤結果：
*create index
*alter index rebuild
*create index online
*alter index rebuild online
1、create index與alter index rebuild所獲取的TM鎖完全一致
2、create index online與alter index rebuild online所獲取的TM鎖、臨時表完全一致
版本：10.2.0.1
一、 create index|
SQL> drop index IDX_TEST1_LHR;
Index dropped.
SQL> ALTER SESSION SET EVENTS '10704 trace name context forever,level 10';
Session altered.
SQL> ALTER SESSION SET EVENTS '10046 trace name context forever,level 12';
Session altered.
SQL> CREATE INDEX IDX_TEST1_LHR ON T_INDEX_161113(OBJECT_NAME);
Index created.
SQL> ALTER SESSION SET EVENTS '10704 trace name context off';
Session altered.
SQL> ALTER SESSION SET EVENTS '10046 trace name context off';
Session altered.
SQL> CREATE OR REPLACE VIEW VW_SQL_TRACE_NAME_LHR AS 2 SELECT D.VALUE || '/' || LOWER(RTRIM(I.INSTANCE, CHR(0))) || '_ora_' || 3 P.SPID || '.trc' TRACE_FILE_NAME 4 FROM (SELECT P.SPID 5 FROM V$MYSTAT M, V$SESSION S, V$PROCESS P 6 WHERE M.STATISTIC# = '1' 7 AND S.SID = M.SID 8 AND P.ADDR = S.PADDR) P, 9 (SELECT T.INSTANCE 10 FROM V$THREAD T, V$PARAMETER V 11 WHERE V.NAME = 'thread' 12 AND (V.VALUE = '0' OR TO_CHAR(T.THREAD#) = V.VALUE)) I, 13 (SELECT VALUE FROM V$PARAMETER WHERE NAME = 'user_dump_dest') D;
View created.
SQL> SQL> SQL> SQL> CREATE OR REPLACE PUBLIC SYNONYM SYN_TRACENAME_LHR FOR VW_SQL_TRACE_NAME_LHR;
Synonym created.
SQL> SQL> select * from VW_SQL_TRACE_NAME_LHR;
TRACE_FILE_NAME ----------------------------- /u01/app/oracle/admin/jiagulun/udump/jiagulun_ora_516.trc SQL> SELECT OBJECT_NAME, 2 OBJECT_ID, 3 DATA_OBJECT_ID, 4 TO_CHAR(OBJECT_ID, 'xxxxxxxxxxxx') HEX_OBJECTID, 5 TO_CHAR(DATA_OBJECT_ID, 'xxxxxxxxxxxx') HEX_DOBJECTID 6 FROM DBA_OBJECTS 7 WHERE OBJECT_NAME IN ('T_INDEX_161113', 'IDX_TEST1_LHR');
OBJECT_NAME OBJECT_ID DATA_OBJECT_ID HEX_OBJECTID HEX_DOBJECTID --------------- ---------- -------------- ------------- ------------- IDX_TEST1_LHR 53239 53239 cff7 cff7 T_INDEX_161113 53121 53121 cf81 cf81
|
trace文件如下，搜字符串“cf81”：
|
1、獲取T_INDEX_161113表mode=3 DL鎖 *** 2016-11-21 16:23:57.846 ksqgtl *** DL-0000cf81-00000000 mode=3 flags=0x11 timeout=0 *** ksqgtl: xcb=0x0x6f45dc78, ktcdix=2147483647, topxcb=0x0x6f45dc78 ktcipt(topxcb)=0x0
2、獲取T_INDEX_161113表mode=4 TM鎖 *** 2016-11-21 16:23:57.847 ksqgtl *** TM-0000cf81-00000000 mode=4 flags=0x401 timeout=0 *** ksqgtl: xcb=0x0x6f45dc78, ktcdix=2147483647, topxcb=0x0x6f45dc78 ktcipt(topxcb)=0x0
3、釋放T_INDEX_161113表DL鎖 *** 2016-11-21 16:24:06.899 ksqrcl: DL,cf81,0 ksqrcl: returns 0
4、釋放T_INDEX_161113表TM鎖 *** 2016-11-21 16:24:06.902 ksqrcl: TM,cf81,0 ksqrcl: returns 0
|
二、 alter index ... rebuild
|
SQL> CONN / AS SYSDBA Connected. SQL> ALTER SESSION SET EVENTS '10704 trace name context forever,level 10';
Session altered.
SQL> ALTER SESSION SET EVENTS '10046 trace name context forever,level 12';
Session altered.
SQL> ALTER INDEX IDX_TEST1_LHR REBUILD;
Index altered.
SQL> ALTER SESSION SET EVENTS '10704 trace name context off';
Session altered.
SQL> ALTER SESSION SET EVENTS '10046 trace name context off';
Session altered.
SQL> select * from VW_SQL_TRACE_NAME_LHR;
TRACE_FILE_NAME ----------------------------- /u01/app/oracle/admin/jiagulun/udump/jiagulun_ora_1383.trc
SQL> SELECT OBJECT_NAME, 2 OBJECT_ID, 3 DATA_OBJECT_ID, 4 TO_CHAR(OBJECT_ID, 'xxxxxxxxxxxx') HEX_OBJECTID, 5 TO_CHAR(DATA_OBJECT_ID, 'xxxxxxxxxxxx') HEX_DOBJECTID 6 FROM DBA_OBJECTS 7 WHERE OBJECT_NAME IN ('T_INDEX_161113', 'IDX_TEST_LHR'); OBJECT_NAME OBJECT_ID DATA_OBJECT_ID HEX_OBJECTID HEX_DOBJECTID --------------- ---------- -------------- ------------- ------------- IDX_TEST1_LHR 53239 53242 cff7 cffa T_INDEX_161113 53121 53121 cf81 cf81
|
trace文件如下，搜字符串“cf81”：
|
1、獲取T_INDEX_161113表mode=3 DL鎖 *** 2016-11-21 16:37:04.615 ksqgtl *** DL-0000cf81-00000000 mode=3 flags=0x11 timeout=0 *** ksqgtl: xcb=0x0x6f45dc78, ktcdix=2147483647, topxcb=0x0x6f45dc78 ktcipt(topxcb)=0x0
2、獲取T_INDEX_161113表mode=4 TM鎖 *** 2016-11-21 16:37:04.616 ksqgtl *** TM-0000cf81-00000000 mode=4 flags=0x401 timeout=0 *** ksqgtl: xcb=0x0x6f45dc78, ktcdix=2147483647, topxcb=0x0x6f45dc78 ktcipt(topxcb)=0x0
3、釋放T_INDEX_161113表DL鎖 *** 2016-11-21 16:37:09.948 ksqrcl: DL,cf81,0 ksqrcl: returns 0
4、釋放T_INDEX_161113表TM鎖 *** 2016-11-21 16:37:10.003 ksqrcl: TM,cf81,0 ksqrcl: returns 0
|
三、 create index ... online
|
SQL> conn / as sysdba Connected.
SQL> drop index IDX_TEST1_LHR;
Index dropped.
SQL> ALTER SESSION SET EVENTS '10704 trace name context forever,level 10';
Session altered.
SQL> ALTER SESSION SET EVENTS '10046 trace name context forever,level 12';
Session altered.
SQL> CREATE INDEX IDX_TEST1_LHR ON T_INDEX_161113(OBJECT_NAME) ONLINE;
Index created.
SQL> ALTER SESSION SET EVENTS '10704 trace name context off';
Session altered.
SQL> ALTER SESSION SET EVENTS '10046 trace name context off';
Session altered.
SQL> select * from VW_SQL_TRACE_NAME_LHR;
TRACE_FILE_NAME ----------------------------- /u01/app/oracle/admin/jiagulun/udump/jiagulun_ora_1915.trc
SQL> col object_name format a15 SQL> SELECT OBJECT_NAME, 2 OBJECT_ID, 3 DATA_OBJECT_ID, 4 TO_CHAR(OBJECT_ID, 'xxxxxxxxxxxx') HEX_OBJECTID, 5 TO_CHAR(DATA_OBJECT_ID, 'xxxxxxxxxxxx') HEX_DOBJECTID 6 FROM DBA_OBJECTS 7 WHERE OBJECT_NAME IN ('T_INDEX_161113', 'IDX_TEST_LHR'); OBJECT_NAME OBJECT_ID DATA_OBJECT_ID HEX_OBJECTID HEX_DOBJECTID --------------- ---------- -------------- ------------- ------------- IDX_TEST1_LHR 53243 53243 cffb cffb T_INDEX_161113 53121 53121 cf81 cf81
|
trace文件如下，搜字符串“cf81”：
|
1、獲取T_INDEX_161113表mode=3 DL鎖 *** 2016-11-21 16:45:14.381 ksqgtl *** DL-0000cf81-00000000 mode=3 flags=0x11 timeout=0 *** ksqgtl: xcb=0x0x6f45dc78, ktcdix=2147483647, topxcb=0x0x6f45dc78 ktcipt(topxcb)=0x0
2、獲取T_INDEX_161113表mode=2 TM鎖 *** 2016-11-21 16:45:14.383 ksqgtl *** TM-0000cf81-00000000 mode=2 flags=0x401 timeout=21474836 *** ksqgtl: xcb=0x0x6f45dc78, ktcdix=2147483647, topxcb=0x0x6f45dc78 ktcipt(topxcb)=0x0
3、2級TM鎖轉換為4級TM鎖，4級TM鎖轉換為2級TM鎖 *** 2016-11-21 16:45:14.659 ksqcnv: TM-0000cf81,00000000 mode=4 timeout=21474836 *** 2016-11-21 16:45:14.659 ksqcmi: TM,cf81,0 mode=4 timeout=21474836 ksqcmi: returns 0 ksqcnv: RETURNS 0 *** 2016-11-21 16:45:14.659 ksqcnv: TM-0000cf81,00000000 mode=2 timeout=21474836 *** 2016-11-21 16:45:14.659 ksqcmi: TM,cf81,0 mode=2 timeout=21474836 ksqcmi: returns 0 ksqcnv: RETURNS 0 WAIT #1: nam='db file sequential read' ela= 14264 file#=1 block#=62781 blocks=1 obj#=53121 tim=1445037026096411 WAIT #1: nam='db file scattered read' ela= 19094 file#=1 block#=62913 blocks=3 obj#=53121 tim=1445037026118946 WAIT #1: nam='db file scattered read' ela= 4712 file#=1 block#=62980 blocks=5 obj#=53121 tim=1445037026125569 。。。。。。。。。。。
4、2級TM鎖轉換為4級TM鎖 *** 2016-11-21 16:45:26.192 ksqcnv: TM-0000cf81,00000000 mode=4 timeout=21474836 *** 2016-11-21 16:45:26.192 ksqcmi: TM,cf81,0 mode=4 timeout=21474836 ksqcmi: returns 0 ksqcnv: RETURNS 0
5、釋放T_INDEX_161113表DL鎖 *** 2016-11-21 16:45:27.274 ksqrcl: DL,cf81,0 ksqrcl: returns 0
6、釋放T_INDEX_161113表TM鎖 *** 2016-11-21 16:45:27.393 ksqrcl: TM,cf81,0 ksqrcl: returns 0 |
四、 alter index ... rebuild online
|
SQL> conn / as sysdba Connected.
SQL> ALTER SESSION SET EVENTS '10704 trace name context forever,level 10';
Session altered.
SQL> ALTER SESSION SET EVENTS '10046 trace name context forever,level 12';
Session altered.
SQL> ALTER INDEX IDX_TEST1_LHR REBUILD ONLINE;
Index created.
SQL> ALTER SESSION SET EVENTS '10704 trace name context off';
Session altered.
SQL> ALTER SESSION SET EVENTS '10046 trace name context off';
Session altered.
SQL> select * from VW_SQL_TRACE_NAME_LHR;
TRACE_FILE_NAME ----------------------------- /u01/app/oracle/admin/jiagulun/udump/jiagulun_ora_3347.trc
SQL> col object_name format a15
SQL> SELECT OBJECT_NAME, 2 OBJECT_ID, 3 DATA_OBJECT_ID, 4 TO_CHAR(OBJECT_ID, 'xxxxxxxxxxxx') HEX_OBJECTID, 5 TO_CHAR(DATA_OBJECT_ID, 'xxxxxxxxxxxx') HEX_DOBJECTID 6 FROM DBA_OBJECTS 7 WHERE OBJECT_NAME IN ('T_INDEX_161113', 'IDX_TEST_LHR'); OBJECT_NAME OBJECT_ID DATA_OBJECT_ID HEX_OBJECTID HEX_DOBJECTID --------------- ---------- -------------- ------------- ------------- IDX_TEST1_LHR 53243 53247 cffb cfff T_INDEX_161113 53121 53121 cf81 cf81
|
trace文件如下，搜字符串“cf81”：
|
1、獲取T_INDEX_161113表mode=3 DL鎖 *** 2016-11-21 17:06:23.837 ksqgtl *** DL-0000cf81-00000000 mode=3 flags=0x11 timeout=0 *** ksqgtl: xcb=0x0x6f45dc78, ktcdix=2147483647, topxcb=0x0x6f45dc78 ktcipt(topxcb)=0x0
2、獲取T_INDEX_161113表mode=2 TM鎖 PARSING IN CURSOR #1 len=40 dep=0 uid=0 oct=9 lid=0 tim=1445038265466869 hv=1374438854 ad='6c6dc948' ALTER INDEX IDX_TEST1_LHR REBUILD ONLINE END OF STMT PARSE #1:c=6999,e=7057,p=0,cr=12,cu=0,mis=1,r=0,dep=0,og=1,tim=1445038265466867 *** 2016-11-21 17:06:23.838 ksqgtl *** TM-0000cf81-00000000 mode=2 flags=0x401 timeout=21474836 *** ksqgtl: xcb=0x0x6f45dc78, ktcdix=2147483647, topxcb=0x0x6f45dc78 ktcipt(topxcb)=0x0
3、2級TM鎖轉換為4級TM鎖，4級TM鎖轉換為2級TM鎖 *** 2016-11-21 17:06:23.937 ksqcnv: TM-0000cf81,00000000 mode=4 timeout=21474836 *** 2016-11-21 17:06:23.937 ksqcmi: TM,cf81,0 mode=4 timeout=21474836 ksqcmi: returns 0 ksqcnv: RETURNS 0 *** 2016-11-21 17:06:23.937 ksqcnv: TM-0000cf81,00000000 mode=2 timeout=21474836 *** 2016-11-21 17:06:23.937 ksqcmi: TM,cf81,0 mode=2 timeout=21474836 ksqcmi: returns 0 ksqcnv: RETURNS 0 WAIT #1: nam='db file sequential read' ela= 17434 file#=1 block#=62781 blocks=1 obj#=53121 tim=1445038265592696 WAIT #1: nam='db file scattered read' ela= 25149 file#=1 block#=62913 blocks=3 obj#=53121 tim=1445038265625891 WAIT #1: nam='db file scattered read' ela= 22659 file#=1 block#=62980 blocks=5 obj#=53121 tim=1445038265654375 WAIT #1: nam='db file sequential read' ela= 19 file#=1 block#=62984 blocks=1 obj#=53121 tim=1445038265654750 WAIT #1: nam='db file scattered read' ela= 23256 file#=1 block#=63142 blocks=2 obj#=53121 tim=1445038265680595 。。。。。。。。。。。。。。
4、2級TM鎖轉換為4級TM鎖 *** 2016-11-21 17:06:31.754 ksqcnv: TM-0000cf81,00000000 mode=4 timeout=21474836 *** 2016-11-21 17:06:31.754 ksqcmi: TM,cf81,0 mode=4 timeout=21474836 ksqcmi: returns 0 ksqcnv: RETURNS 0
5、釋放T_INDEX_161113表DL鎖 *** 2016-11-21 17:06:32.806 ksqrcl: DL,cf81,0 ksqrcl: returns 0
6、釋放T_INDEX_161113表TM鎖 *** 2016-11-21 17:06:32.976 ksqrcl: TM,cf81,0 ksqrcl: returns 0
|
版本：11.2.0.3
一、 create index|
SQL> drop index IDX_TEST_LHR;
Index dropped.
SQL> ALTER SESSION SET EVENTS '10704 trace name context forever,level 10';
Session altered.
SQL> ALTER SESSION SET EVENTS '10046 trace name context forever,level 12';
Session altered.
SQL> CREATE INDEX IDX_TEST_LHR ON T_INDEX_161113(OBJECT_NAME);
Index created.
SQL> ALTER SESSION SET EVENTS '10704 trace name context off';
Session altered.
SQL> ALTER SESSION SET EVENTS '10046 trace name context off';
Session altered.
SQL> select value from v$diag_info where name like '%File%';
VALUE -------------------------------------------------------------------------------- /u02/app/oracle/diag/rdbms/oratest/oratest/trace/oratest_ora_23527.trc
SQL> col object_name format a15 SQL> SELECT OBJECT_NAME, 2 OBJECT_ID, 3 DATA_OBJECT_ID, 4 TO_CHAR(OBJECT_ID, 'xxxxxxxxxxxx') HEX_OBJECTID 5 FROM DBA_OBJECTS 6 WHERE OBJECT_NAME IN ('T_INDEX_161113', 'IDX_TEST_LHR'); OBJECT_NAME OBJECT_ID DATA_OBJECT_ID HEX_OBJECTID --------------- ---------- -------------- ------------- T_INDEX_161113 77629 77629 12f3d IDX_TEST_LHR 77884 77884 1303c
|
trace文件如下，搜字符串“12f3d”：
|
1、獲取T_INDEX_161113表mode=4 TM鎖 PARSING IN CURSOR #140411478315224 len=50 dep=1 uid=0 oct=26 lid=0 tim=1479709305055527 hv=3478035675 ad='716d5f28' sqlid='b3p9ubr7nx76v' LOCK TABLE "T_INDEX_161113" IN SHARE MODE NOWAIT END OF STMT PARSE #140411478315224:c=2000,e=3081,p=0,cr=19,cu=0,mis=1,r=0,dep=1,og=4,plh=0,tim=1479709305055527
*** 2016-11-21 14:21:45.055 ksqgtl *** TM-00012f3d-00000000 mode=4 flags=0x401 timeout=0 *** ksqgtl: xcb=0x76273c58, ktcdix=2147483647, topxcb=0x76273c58 ktcipt(topxcb)=0x0
2、獲取T_INDEX_161113表mode=3 DL鎖 *** 2016-11-21 14:21:45.056 ksqgtl *** DL-00012f3d-00000000 mode=3 flags=0x10001 timeout=0 *** ksqgtl: xcb=0x76273c58, ktcdix=2147483647, topxcb=0x76273c58 ktcipt(topxcb)=0x0
3、釋放T_INDEX_161113表DL鎖 *** 2016-11-21 14:21:50.392 ksqrcl: DL,12f3d,0 ksqrcl: returns 0
4、釋放T_INDEX_161113表TM鎖 *** 2016-11-21 14:21:50.395 ksqrcl: TM,12f3d,0 ksqrcl: returns 0
|
二、 alter index ... rebuild
|
SQL> CONN / AS SYSDBA Connected. SQL> ALTER SESSION SET EVENTS '10704 trace name context forever,level 10';
Session altered.
SQL> ALTER SESSION SET EVENTS '10046 trace name context forever,level 12';
Session altered.
SQL> ALTER INDEX IDX_TEST_LHR REBUILD;
Index altered.
SQL> ALTER SESSION SET EVENTS '10704 trace name context off';
Session altered.
SQL> ALTER SESSION SET EVENTS '10046 trace name context off';
Session altered.
SQL> select value from v$diag_info where name like '%File%';
VALUE -------------------------------------------------------------------------------- /u02/app/oracle/diag/rdbms/oratest/oratest/trace/oratest_ora_23540.trc
SQL> col object_name format a15 SQL> SELECT OBJECT_NAME, 2 OBJECT_ID, 3 DATA_OBJECT_ID, 4 TO_CHAR(OBJECT_ID, 'xxxxxxxxxxxx') HEX_OBJECTID 5 FROM DBA_OBJECTS 6 WHERE OBJECT_NAME IN ('T_INDEX_161113', 'IDX_TEST_LHR');
SQL> SELECT OBJECT_NAME, 2 OBJECT_ID, 3 DATA_OBJECT_ID, 4 TO_CHAR(OBJECT_ID, 'xxxxxxxxxxxx') HEX_OBJECTID, 5 TO_CHAR(DATA_OBJECT_ID, 'xxxxxxxxxxxx') HEX_DOBJECTID 6 FROM DBA_OBJECTS 7 WHERE OBJECT_NAME IN ('T_INDEX_161113', 'IDX_TEST_LHR');
OBJECT_NAME OBJECT_ID DATA_OBJECT_ID HEX_OBJECTID HEX_DOBJECTID --------------- ---------- -------------- ------------- ------------- T_INDEX_161113 77629 77629 12f3d 12f3d IDX_TEST_LHR 77885 77886 1303d 1303e
|
trace文件如下，搜字符串“12f3d”：
|
1、獲取T_INDEX_161113表mode=4 TM鎖 PARSING IN CURSOR #140719831671200 len=59 dep=1 uid=0 oct=26 lid=0 tim=1479709686366785 hv=3620741631 ad='7176cbc8' sqlid='chctu03bx08gz' LOCK TABLE FOR INDEX "IDX_TEST_LHR" IN SHARE MODE NOWAIT END OF STMT PARSE #140719831671200:c=10999,e=29442,p=2,cr=80,cu=0,mis=1,r=0,dep=1,og=4,plh=0,tim=1479709686366785
*** 2016-11-21 14:28:06.366 ksqgtl *** TM-00012f3d-00000000 mode=4 flags=0x401 timeout=0 *** ksqgtl: xcb=0x76209db0, ktcdix=2147483647, topxcb=0x76209db0 ktcipt(topxcb)=0x0
2、獲取T_INDEX_161113表mode=3 DL鎖 *** 2016-11-21 14:28:06.370 ksqgtl *** DL-00012f3d-00000000 mode=3 flags=0x10001 timeout=0 *** ksqgtl: xcb=0x76209db0, ktcdix=2147483647, topxcb=0x76209db0 ktcipt(topxcb)=0x0
3、釋放T_INDEX_161113表DL鎖 *** 2016-11-21 14:28:10.938 ksqrcl: DL,12f3d,0 ksqrcl: returns 0
4、釋放T_INDEX_161113表TM鎖 *** 2016-11-21 14:28:10.947 ksqrcl: TM,12f3d,0 ksqrcl: returns 0
|
三、 create index ... online
|
SQL> conn / as sysdba Connected.
SQL> drop index IDX_TEST_LHR;
Index dropped.
SQL> ALTER SESSION SET EVENTS '10704 trace name context forever,level 10';
Session altered.
SQL> ALTER SESSION SET EVENTS '10046 trace name context forever,level 12';
Session altered.
SQL> CREATE INDEX IDX_TEST_LHR ON T_INDEX_161113(OBJECT_NAME) ONLINE;
Index created.
SQL> ALTER SESSION SET EVENTS '10704 trace name context off';
Session altered.
SQL> ALTER SESSION SET EVENTS '10046 trace name context off';
Session altered.
SQL> select value from v$diag_info where name like '%File%';
VALUE -------------------------------------------------------------------------------- /u02/app/oracle/diag/rdbms/oratest/oratest/trace/oratest_ora_23672.trc
SQL> col object_name format a15 SQL> SELECT OBJECT_NAME, 2 OBJECT_ID, 3 DATA_OBJECT_ID, 4 TO_CHAR(OBJECT_ID, 'xxxxxxxxxxxx') HEX_OBJECTID, 5 TO_CHAR(DATA_OBJECT_ID, 'xxxxxxxxxxxx') HEX_DOBJECTID 6 FROM DBA_OBJECTS 7 WHERE OBJECT_NAME IN ('T_INDEX_161113', 'IDX_TEST_LHR'); OBJECT_NAME OBJECT_ID DATA_OBJECT_ID HEX_OBJECTID HEX_DOBJECTID --------------- ---------- -------------- ------------- ------------- T_INDEX_161113 77629 77629 12f3d 12f3d IDX_TEST_LHR 77887 77887 1303f 1303f
|
trace文件如下，搜字符串“12f3d”：
|
1、獲取T_INDEX_161113表mode=2 TM鎖 *** 2016-11-21 15:14:44.397 ksqrcl: CU,717dfd90,0 ksqrcl: returns 0 ===================== PARSING IN CURSOR #140118279700704 len=46 dep=1 uid=0 oct=26 lid=0 tim=1479712484397029 hv=3395312659 ad='729e1628' sqlid='g95cs0g560r0m' LOCK TABLE "T_INDEX_161113" IN ROW SHARE MODE END OF STMT PARSE #140118279700704:c=1999,e=1893,p=0,cr=19,cu=0,mis=1,r=0,dep=1,og=4,plh=0,tim=1479712484397029
*** 2016-11-21 15:14:44.397 ksqgtl *** TM-00012f3d-00000000 mode=2 flags=0x401 timeout=21474836 *** ksqgtl: xcb=0x761eac90, ktcdix=2147483647, topxcb=0x761eac90 ktcipt(topxcb)=0x0 *** 2016-11-21 14:21:45.055
2、獲取T_INDEX_161113表mode=3 DL鎖 *** 2016-11-21 15:14:44.398 ksqgtl *** DL-00012f3d-00000000 mode=3 flags=0x10001 timeout=0 *** ksqgtl: xcb=0x761eac90, ktcdix=2147483647, topxcb=0x761eac90 ktcipt(topxcb)=0x0
3、獲取T_INDEX_161113表mode=4 OD鎖 *** 2016-11-21 15:14:44.454 ksqgtl *** OD-00012f3d-00000000 mode=4 flags=0x10401 timeout=0 *** ksqgtl: xcb=0x761eac90, ktcdix=2147483647, topxcb=0x761eac90 ktcipt(topxcb)=0x0
4、釋放T_INDEX_161113表DL鎖 *** 2016-11-21 15:14:53.066 ksqrcl: DL,12f3d,0 ksqrcl: returns 0
5、釋放T_INDEX_161113表OD、TM鎖 *** 2016-11-21 15:14:55.327 ksqrcl: OD,12f3d,0 ksqrcl: returns 0
*** 2016-11-21 15:14:55.327 ksqrcl: TM,12f3d,0 ksqrcl: returns 0
|
四、 alter index ... rebuild online
|
SQL> conn / as sysdba Connected.
SQL> ALTER SESSION SET EVENTS '10704 trace name context forever,level 10';
Session altered.
SQL> ALTER SESSION SET EVENTS '10046 trace name context forever,level 12';
Session altered.
SQL> ALTER INDEX IDX_TEST_LHR REBUILD ONLINE;
Index created.
SQL> ALTER SESSION SET EVENTS '10704 trace name context off';
Session altered.
SQL> ALTER SESSION SET EVENTS '10046 trace name context off';
Session altered.
SQL> select value from v$diag_info where name like '%File%';
VALUE -------------------------------------------------------------------------------- /u02/app/oracle/diag/rdbms/oratest/oratest/trace/oratest_ora_23792.trc
SQL> col object_name format a15
SQL> SELECT OBJECT_NAME, 2 OBJECT_ID, 3 DATA_OBJECT_ID, 4 TO_CHAR(OBJECT_ID, 'xxxxxxxxxxxx') HEX_OBJECTID, 5 TO_CHAR(DATA_OBJECT_ID, 'xxxxxxxxxxxx') HEX_DOBJECTID 6 FROM DBA_OBJECTS 7 WHERE OBJECT_NAME IN ('T_INDEX_161113', 'IDX_TEST_LHR');
OBJECT_NAME OBJECT_ID DATA_OBJECT_ID HEX_OBJECTID HEX_DOBJECTID --------------- ---------- -------------- ------------- ------------- T_INDEX_161113 77629 77629 12f3d 12f3d IDX_TEST_LHR 77887 77890 1303f 13042
|
trace文件如下，搜字符串“12f3d”：
|
1、獲取T_INDEX_161113表mode=2 TM鎖 PARSING IN CURSOR #139909890400672 len=55 dep=1 uid=0 oct=26 lid=0 tim=1479715165881556 hv=1263262788 ad='7167d4f8' sqlid='6dh5ubt5nrr24' LOCK TABLE FOR INDEX "IDX_TEST_LHR" IN ROW SHARE MODE END OF STMT PARSE #139909890400672:c=1000,e=1599,p=0,cr=8,cu=0,mis=1,r=0,dep=1,og=4,plh=0,tim=1479715165881555
*** 2016-11-21 15:59:25.881 ksqgtl *** TM-00012f3d-00000000 mode=2 flags=0x401 timeout=21474836 *** ksqgtl: xcb=0x76228ed0, ktcdix=2147483647, topxcb=0x76228ed0 ktcipt(topxcb)=0x0
2、獲取T_INDEX_161113表mode=3 DL鎖 *** 2016-11-21 15:59:25.883 ksqgtl *** DL-00012f3d-00000000 mode=3 flags=0x10001 timeout=0 *** ksqgtl: xcb=0x76228ed0, ktcdix=2147483647, topxcb=0x76228ed0 ktcipt(topxcb)=0x0
3、獲取T_INDEX_161113表mode=4 OD鎖 *** 2016-11-21 15:59:25.884 ksqgtl *** OD-00012f3d-00000000 mode=4 flags=0x10401 timeout=0 *** ksqgtl: xcb=0x76228ed0, ktcdix=2147483647, topxcb=0x76228ed0 ktcipt(topxcb)=0x0
4、釋放T_INDEX_161113表DL鎖 *** 2016-11-21 15:59:30.334 ksqrcl: DL,12f3d,0 ksqrcl: returns 0
5、釋放T_INDEX_161113表OD、TM鎖 *** 2016-11-21 15:59:30.363 ksqrcl: OD,12f3d,0 ksqrcl: returns 0
*** 2016-11-21 15:59:30.363 ksqrcl: OD,1303f,0 ksqrcl: returns 0
*** 2016-11-21 15:59:30.363 ksqrcl: TM,12f3d,0 ksqrcl: returns 0
|
ALTER SESSION SET EVENTS '10704 trace name context forever,level 10';
ALTER SESSION SET EVENTS '10046 trace name context forever,level 12';
--CREATE INDEX IDX_TEST_LHR ON T_INDEX_161113(OBJECT_NAME) ;
--ALTER INDEX IDX_TEST_LHR REBUILD;
--CREATE INDEX IDX_TEST_LHR ON T_INDEX_161113(OBJECT_NAME) ONLINE;
ALTER INDEX IDX_TEST_LHR REBUILD;
ALTER SESSION SET EVENTS '10704 trace name context off';
ALTER SESSION SET EVENTS '10046 trace name context off';
SELECT OBJECT_NAME,
OBJECT_ID,
DATA_OBJECT_ID,
TO_CHAR(OBJECT_ID, 'xxxxxxxxxxxx') HEX_OBJECTID,
TO_CHAR(DATA_OBJECT_ID, 'xxxxxxxxxxxx') HEX_DOBJECTID
FROM DBA_OBJECTS
WHERE OBJECT_NAME IN ('T_INDEX_161113', 'IDX_TEST1_LHR');
select value from v$diag_info where name like '%File%';
運行如下SQL來創建視圖：
CREATE OR REPLACE VIEW VW_SQL_TRACE_NAME_LHR AS
SELECT D.VALUE || '/' || LOWER(RTRIM(I.INSTANCE, CHR(0))) || '_ora_' ||
P.SPID || '.trc' TRACE_FILE_NAME
FROM (SELECT P.SPID
FROM V$MYSTAT M, V$SESSION S, V$PROCESS P
WHERE M.STATISTIC# = '1'
AND S.SID = M.SID
AND P.ADDR = S.PADDR) P,
(SELECT T.INSTANCE
FROM V$THREAD T, V$PARAMETER V
WHERE V.NAME = 'thread'
AND (V.VALUE = '0' OR TO_CHAR(T.THREAD#) = V.VALUE)) I,
(SELECT VALUE FROM V$PARAMETER WHERE NAME = 'user_dump_dest') D;
創建公共同義詞：
CREATE OR REPLACE PUBLIC SYNONYM SYN_TRACENAME_LHR FOR VW_SQL_TRACE_NAME_LHR;
不帶ONLINE的新建或重建索引的SQL語句獲取的是4級TM鎖，它會阻塞任何DML操作。
在Oracle 10g中，帶ONLINE的新建或重建索引的SQL語句在開始和結束的時候獲取的是4級TM鎖，而在讀取表數據的過程中獲取的是2級TM鎖，所以，在Oracle 10g中，即使加上ONLINE也會阻塞其它會話的DML操作。
在Oracle 11g中，帶ONLINE的新建或重建索引的SQL語句在整個執行過程中獲取的是2級TM鎖，并不會阻塞其它會話的DML操作，但是在創建或重建索引的過程中，其它的會話產生的事務會阻塞索引的創建或重建操作，所以必須結束其它會話的事務才能讓創建或重建索引的操作完成。
在Oracle 11g加上ONLINE的情況下：
(1) 過程中會持有OD(ONLINE DDL)、DL(Direct Loader Index Creation)兩種類型的鎖，在Oracle 10g下只有DL鎖沒有OD鎖
(2) 表級鎖TM的持有模式為row-S (SS)，與row-X (SX)類型的鎖互相兼容，因此不會在表級發生阻塞
(3) 阻塞發生在行級鎖申請階段，即請求的share(S)類型的鎖與執行DML的session已經持有的exclusive(X)鎖之間存在不兼容的情況；相比非online方式的表級鎖，鎖的粒度上更加細化，副作用更小
(4) 新增以SYS_JOURNAL_為前綴的IOT表，記錄與索引創建動作同時進行的其它DML操作修改過的記錄，等到索引創建完成前將IOT表里的記錄合并至索引中并刪除IOT表
SELECT * FROM V$LOCK A WHERE A.SID IN (16,27) AND A.TYPE IN ('TX','TM') ORDER BY a.SID,a.TYPE;
SELECT * FROM V$LOCK A WHERE A.SID IN (16,27) ORDER BY a.SID,a.TYPE;
SELECT * FROM DBA_DML_LOCKS D WHERE D.SESSION_ID IN (16,27) ORDER BY d.SESSION_ID;
SELECT * FROM DBA_DDL_LOCKS D WHERE D.SESSION_ID IN (16,27) AND D.name NOT IN ('ALERT_QUE_R','AQ$_ALERT_QT_E','AW_DROP_PROC','DATABASE','DBMS_APPLICATION_INFO','DBMS_BACKUP_RESTORE','DBMS_HA_ALERTS_PRVT','DBMS_OUTPUT','DBMS_PRVT_TRACE','DBMS_RCVMAN','DBMS_SQL','DBMS_STANDARD','DBMS_SYS_SQL','DBMS_TRANSACTION','DBMS_UTILITY','DBMS_XDBZ0','DICTIONARY_OBJ_NAME','DICTIONARY_OBJ_OWNER','PLITBLM','SCHEDULER$_INSTANCE_S','STANDARD','SDO_GEOR_DEF','SQL_TXT');
SELECT A.TADDR,
A.LOCKWAIT,
A.ROW_WAIT_OBJ#,
A.ROW_WAIT_FILE#,
A.ROW_WAIT_BLOCK#,
A.ROW_WAIT_ROW#,
(SELECT D.OWNER || '|' || D.OBJECT_NAME || '|' || D.OBJECT_TYPE
FROM DBA_OBJECTS D
WHERE D.OBJECT_ID = A.ROW_WAIT_OBJ#) OBJECT_NAME,
A.EVENT,
A.P1,
A.P2,
A.P3,
CHR(BITAND(P1, -16777216) / 16777215) ||
CHR(BITAND(P1, 16711680) / 65535) "LOCK",
BITAND(P1, 65535) "MODE",
TRUNC(P2 / POWER(2, 16)) AS XIDUSN,
BITAND(P2, TO_NUMBER('FFFF', 'XXXX')) + 0 AS XIDSLOT,
P3 XIDSQN,
A.SID,
A.BLOCKING_SESSION,
A.SADDR,
DBMS_ROWID.ROWID_CREATE(1, 77669, 8, 2799, 0) REQUEST_ROWID,
(SELECT B.SQL_TEXT
FROM V$SQL B
WHERE B.SQL_ID = NVL(A.SQL_ID, A.PREV_SQL_ID)) SQL_TEXT
FROM V$SESSION A
WHERE A.SID IN (143);
SELECT * FROM v$lock a WHERE a.KADDR='000000007620A7C0';
SELECT * FROM v$transaction a WHERE a.ADDR='000000007620A7C0';
SELECT * FROM V$LOCK_TYPE D WHERE D.TYPE IN ('AE','DL','OD','TO','TX');
SELECT D.OWNER, D.OBJECT_NAME, D.OBJECT_ID, D.OBJECT_TYPE
FROM DBA_OBJECTS D
WHERE D.OBJECT_ID IN (77665, 77629);
SELECT D.PARAMETER1,D.PARAMETER2,D.PARAMETER3 FROM V$EVENT_NAME D WHERE D.NAME='enq: TX - row lock contention';
原文地址：創建-重建索引過程中需要獲取的鎖 作者：redhouser
原文地址：create index...online操作過程中會申請持有哪些鎖 作者：oliseh
About Me
...............................................................................................................................
● 本文作者：小麥苗，只專注于數據庫的技術，更注重技術的運用
● 本文在itpub（http://blog.itpub.net/26736162）、博客園（http://www.cnblogs.com/lhrbest）和個人微信公眾號（xiaomaimiaolhr）上有同步更新
● 本文itpub地址：http://blog.itpub.net/26736162/viewspace-2128896/
● 本文博客園地址：http://www.cnblogs.com/lhrbest/p/6091277.html
● 本文pdf版及小麥苗云盤地址：http://blog.itpub.net/26736162/viewspace-1624453/
● 數據庫筆試面試題庫及解答：http://blog.itpub.net/26736162/viewspace-2134706/
● QQ群：230161599 微信群：私聊
● 聯系我請加QQ好友(646634621)，注明添加緣由
● 于 2016-10-21 09:00 ~ 2016-11-22 22:00 在魔都完成
● 文章內容來源于小麥苗的學習筆記，部分整理自網絡，若有侵權或不當之處還請諒解
● 版權所有，歡迎分享本文，轉載請保留出處
...............................................................................................................................
拿起手機使用微信客戶端掃描下邊的左邊圖片來關注小麥苗的微信公眾號：xiaomaimiaolhr，掃描右邊的二維碼加入小麥苗的QQ群，學習最實用的數據庫技術。
免責聲明：本站發布的內容（圖片、視頻和文字）以原創、轉載和分享為主，文章觀點不代表本網站立場，如果涉及侵權請聯系站長郵箱：is@yisu.com進行舉報，并提供相關證據，一經查實，將立刻刪除涉嫌侵權內容。
