怎麼再Oracle中進行讀鎖（二）

　　這一篇介紹一種更高級的實現方法這使得無論是通過存儲過程還是直接SQL訪問都可以實現讀鎖的機制

　　在Oracle中如何實現讀鎖(一)上一篇文章給出了一種簡單的方法但是缺點也十分明顯就是要求用戶必須采用調用函數的方式才能實現讀鎖

　　很多情況下上面的條件是無法實現的這就要求必須有一種方法對於所有的訪問情況都試用

　　現在面臨兩個難題一個是Oracle的讀不加鎖因此必須自己實現鎖的功能二是如何將鎖的實現添加到SELECT語句中普通的觸發器不會被SELECT所觸發因此通過觸發器來實現這個功能是不現實的

　　對於第一個問題可以通過Oracle的DBMS_LOCK包來實現定制用戶自定義鎖的實現而第二個問題可以利用Oracle的精細訪問控制來實現

　　簡單描述一下思路利用DBMS_LOCKREQUEST過程指定一個ID來獲取獨占鎖其他會話獲取同樣的鎖就會被鎖定

　　
　　SQL> DECLARE
　　 V_LOCK NUMBER;
　　 BEGIN
　　 V_LOCK := DBMS_LOCKREQUEST( RELEASE_ON_COMMIT => TRUE);
　　 END;
　　 /

　　PL/SQL 過程已成功完成

　　會話獲取同樣的鎖就會被鎖定

　　
　　SQL> DECLARE
　　 V_LOCK NUMBER;
　　 BEGIN
　　 V_LOCK := DBMS_LOCKREQUEST( RELEASE_ON_COMMIT => TRUE);
　　 END;
　　 /

　　SQL> COMMIT;

　　提交完成

　　會話才解鎖

　　PL/SQL 過程已成功完成

　　SQL> COMMIT;

　　提交完成

　　利用DBMS_LOCK包可以實現鎖的功能下面就是利用DBMS_RLS包添加精細訪問策略在訪問目標表的時候將鎖添加到查詢語句中簡單的實現如下

　　
　　SQL> SELECT OBJECT_ID FROM USER_OBJECTS WHERE OBJECT_NAME = T;
　　OBJECT_ID
　　
　　
　　SQL> CREATE OR REPLACE FUNCTION F_POLICY(OBJECT_SCHEMA IN VARCHAR OBJECT_NAME IN VARCHAR)
　　 RETURN VARCHAR AS
　　 V_NUM NUMBER;
　　 BEGIN
　　 RETURN DBMS_LOCKREQUEST(  ) IN ( );
　　 END;
　　 /

　　函數已創建

　　SQL> EXEC DBMS_RLSADD_POLICY(USER T MYPOLICY USER F_POLICY);

　　PL/SQL 過程已成功完成

　　利用T的OBJECT_ID作為鎖ID避免和其他對象獲取鎖發生沖突由於DBMS_LOCKREQUEST過程的RELEASE_ON_COMMIT參數要求布爾類型而布爾類型無法在SQL中使用這裡暫時使用默認值FALSE將這個函數作為T表的訪問策略添加成功後訪問T表時Oracle會自動將DBMS_LOCKREQUEST( ) IN ( )放到WHERE語句之後從而實現讀鎖的功能

　　
　　SQL> SELECT COUNT(*) FROM T;
　　COUNT(*)
　　
　　
　　SQL> COMMIT;

　　提交完成

　　會話對T表的查詢被鎖定

　　SQL> SELECT COUNT(*) FROM T;

　　由於沒有指定RELEASE_ON_COMMIT為TRUE會話提交或回滾仍然會占有鎖資源只有斷開會話或明確的釋放鎖資源

　　
　　SQL> SELECT DBMS_LOCKRELEASE() FROM DUAL;
　　DBMS_LOCKRELEASE()
　　
　　

　　會話被解鎖

　　
　　COUNT(*)
　　
　　
　　SQL> SELECT DBMS_LOCKRELEASE() FROM DUAL;
　　DBMS_LOCKRELEASE()
　　
　　

　　解決這個問題的方法是修改函數由於這個函數調用發生在查詢之前因此將鎖定放到函數中結果是一樣的

　　SQL> CREATE OR REPLACE FUNCTION F_POLICY(OBJECT_SCHEMA IN VARCHAR OBJECT_NAME IN VARCHAR)
　　 RETURN VARCHAR AS
　　 V_NUM NUMBER;
　　 BEGIN
　　 V_NUM := DBMS_LOCKREQUEST(   TRUE);
　　 RETURN V_NUM ||  IN ( );
　　 END;
　　 /

　　函數已創建

　　
　　SQL> SELECT COUNT(*) FROM T;
　　COUNT(*)
　　
　　

　　會話嘗試查詢T表

　　SQL> SELECT COUNT(*) FROM T;

　　這時會話可以通過提交或回滾來釋放鎖

　　SQL> COMMIT;

　　提交完成

　　會話獲取鎖資源並查詢T表記錄

　　
　　COUNT(*)
　　
　　
　　SQL> COMMIT;

　　提交完成

　　這樣通過DBMS_LOCK包自定義鎖和DBMS_RLS包設置精細訪問策略實現了Oracle中的讀鎖功能需要注意的是這種方法對於SYS用戶無效因為SYS用戶不受精細訪問策略的影響

　　個人比較欣賞Tom的那句話在Oracle中很少會說不能做什麼而是會有你用多少中選擇來實現這個功能

　　這裡想加一句如果某個功能你在Oracle中無法實現那麼並不意味著在Oracle中無法實現而多半是你對Oracle的功能還不是很了解

From:http://tw.wingwit.com/Article/program/Oracle/201311/18182.html