Oracle Row-X(SX) 鎖 引起的問題 說明

 

Row-X(SX)鎖在Oracle的鎖中層級是3，是行級排它鎖，即在提交前不允許做DML操作 Insert、Update、 Delete、Lock row share。

 

關於Oracle 鎖的說明，更多內容參考：

 

ORACLE 鎖機制

http://blog.csdn.net/tianlesoftware/article/details/4696896

 

這裡要說的的是Row-X（SX）鎖引起的問題，不過這裡部分內容也只是推測，因為之前的沒有留足足夠的證據來說明這個觀點。

 

            之前發生過修改業務系統的一個核心預存程序，導致其他關聯的過程也全部無效的情況，並且還不能直接進行編譯，需要在OS層級kill 進程後才能編譯的情況。

 

Oracle 預存程序 無法編譯 解決方案

http://blog.csdn.net/tianlesoftware/article/details/7412555

 

Oracle shutdown 過程中 DB hang住 解決方案

http://blog.csdn.net/tianlesoftware/article/details/7407587

 

在這兩種情況都是kill 掉相關進程才解決問題，因為業務需要，需要在次修改核心預存程序，為避免出現類似問題，提前做了一些準備工作。

 

1.     在修改之前查看對象持有鎖的情況

Oracle 查看 對象 持有 鎖 的情況

http://blog.csdn.net/tianlesoftware/article/details/6822321

 

在這裡，我們可以看到都是Ros-X（SX）的行級獨佔鎖定。

 

2.     查看session持有這些鎖的session 的情況

 

這個我在之前的指令碼裡加了一個session的狀態，指令碼如下：

 

/* Formatted on 2012/6/6 10:59:49 (QP5 v5.185.11230.41888) */SELECT distinct S.SID SESSION_ID,       S.STATUS,       S.USERNAME,       DECODE (LMODE,               0, ' None ',               1, ' Null ',               2, ' Row-S(SS) ',               3, ' Row-X(SX) ',               4, ' Share',               5, 'S/Row-X (SSX) ',               6, 'Exclusive ',               TO_CHAR (LMODE))          MODE_HELD,       DECODE (REQUEST,               0, ' None ',               1, ' Null ',               2, ' Row-S(SS) ',               3, ' Row-X(SX) ',               4, ' Share',               5, 'S/Row-X (SSX) ',               6, 'Exclusive ',               TO_CHAR (REQUEST))          MODE_REQUESTED,       O.OWNER || ' . ' || O.OBJECT_NAME || ' ( ' || O.OBJECT_TYPE || ' ) '          AS OBJECT_NAME,       S.TYPE LOCK_TYPE,       L.ID1 LOCK_ID1,       L.ID2 LOCK_ID2,       S2.SQL_TEXT  FROM V$LOCK L,       SYS.DBA_OBJECTS O,       V$SESSION S,       V$ACCESS A,       V$SQL S2 WHERE     L.SID = S.SID       AND L.ID1 = O.OBJECT_ID       AND S.SID = A.SID       AND S2.HASH_VALUE = S.SQL_HASH_VALUE       AND A.OBJECT = 'PROC_VALIDATE_RULE_V3';

 

顯示這些session 都是處於killed狀態。 在之前的Blog中：

Oracle killsessin 說明

http://blog.csdn.net/tianlesoftware/article/details/7417058

 

killed狀態的會話，被標註為刪除，表示出現了錯誤，正在復原。這個過程可能需要等待遠程事務的回應或者復原事務，在這個狀態會被標記為killed，並且可能需要等待很長時間，等這些操作完成之後才會kill掉。要釋放這些狀態為killed的session，可以重啟DB，也可以直接在OS 層級kill 進程， windows 下使用ORAKILL 命令，UNIX 直接使用kill 命令。

 

         因為我們編譯過程這些行級獨佔鎖定如果沒有及時釋放，我們的編譯也會一直處於等待狀態，所以我這裡是選擇在OS層級kill 掉這些session。

 

 

3.     確認killed 狀態的session是否使用復原段

 

使用如下SQL：

/* Formatted on 2012/6/7 5:47:42 (QP5 v5.185.11230.41888) */  SELECT s.username,         s.sid,         s.serial#,         t.used_ublk,         t.used_urec,         rs.segment_name,         r.rssize,         r.status    FROM v$transaction t,         v$session s,         v$rollstat r,         dba_rollback_segs rs   WHERE     s.saddr = t.ses_addr         AND t.xidusn = r.usn         AND rs.segment_id = t.xidusn         AND s.sid IN                (850, 968, 991, 1039, 968, 991, 1039, 1009, 732, 850, 732)ORDER BY t.used_ublk DESC;

從查詢結果看，確實在使用，不過session 是一個月之前的session，並且這些session 都是寫log的操作，根據分析，可以直接在作業系統層級kill 掉進程，來釋放相關的鎖。

 

4.     在OS 層級kill 進程

前面已經擷取了對象上持有的session ID，這雷根據Session ID 查出相關的系統SPID. Sql 語句如下：

/* Formatted on 2012/6/7 5:51:01 (QP5 v5.185.11230.41888) */SELECT spid, osuser, s.program  FROM v$session s, v$process p WHERE     s.paddr = p.addr       AND s.sid IN              (850, 968, 991, 1039, 968, 991, 1039, 1009, 732, 850, 732);

 

然後在OS 層級直接kill 掉這些進程就可以了：

 

[oracle@qs-xezf-db1 ~]$ ps -ef|grep 6101

oracle   6101     1  0 May13 ?        00:01:06 oraclexezf (LOCAL=NO)

oracle  16790 16606  0 05:14 pts/2    00:00:00 grep 6101

[oracle@qs-xezf-db1 ~]$ kill -9 6101   

[oracle@qs-xezf-db1 ~]$ ps -ef|grep 6279

oracle   6279     1  0 May13 ?        00:00:57 oraclexezf (LOCAL=NO)

oracle  16824 16606  0 05:14 pts/2    00:00:00 grep 6279

[oracle@qs-xezf-db1 ~]$ kill -9 6279

 

 

5.     檢查

在OS層級kill 掉這些狀態為killed 的session 之後，對象的Row-X（SX）全部釋放，過程對象上的操作也順利進行，沒有出現等待。

這裡注意的是，修改之後會導致一些對象的無效，需要查看並重新編譯這些無效對象。

 

 

 

 

 

 

-------------------------------------------------------------------------------------------------------

著作權，文章允許轉載，但必須以連結方式註明源地址，否則追究法律責任!

Skype:            tianlesoftware

QQ:                 tianlesoftware@gmail.com

Email:             tianlesoftware@gmail.com

Blog:   http://www.tianlesoftware.com

Weibo:            http://weibo.com/tianlesoftware

Twitter: http://twitter.com/tianlesoftware

Facebook: http://www.facebook.com/tianlesoftware

Linkedin: http://cn.linkedin.com/in/tianlesoftware

 

 

-------加群需要在備忘說明Oracle資料表空間和資料檔案的關係，否則拒絕申請----

DBA1 群：62697716(滿);   DBA2 群：62697977(滿)  DBA3 群：62697850(滿)  

DBA 超級群：63306533(滿);  DBA4 群：83829929   DBA5群： 142216823

DBA6 群：158654907    DBA7 群：172855474   DBA總群：104207940