在JAVA端使Oracle存儲過程串行地執行

我們知道給資源上鎖可以使我們串行化地訪問資源，oracle為plsql開發人員提供了DBMS_SQL包用來管理USERL LOCK鎖資源。這種鎖可以使得多個session串行的執行某個存儲過程，還可以用來排他的訪問某個外部設備或服務，甚至可以檢測事務的提交或回滾（提交或回滾時鎖的釋放）。

有人說我在java端調用db的存儲過程，可以使用synchronized lock來串行的調用存儲過程。那就不需要db lock呢？因為當java端應用服務器down的時候，存儲過程已經在執行了，但是可能oracle session（RAC的db）並沒有立即釋放掉。當我們重啟應用服務器後，其實後台的以前的存儲過程還在執行，如果再次調用存儲過程，這就無法保證存儲過程的串行執行了。所以說存儲過程的同步鎖是必須放在Oracle db端的。

DBMS_LOCK包具有下面幾個API，主要說明以下幾個，其他的可以參考oracle相應文檔，我們這裡只用X鎖（排他鎖也稱寫鎖）。

PROCEDURE DBMS_LOCK.ALLOCATE_UNIQUE
　　 (lockname IN VARCHAR2
　　 ,lockhandle OUT VARCHAR2
　　 ,expiration_secs IN INTEGER DEFAULT 864000);

參數 描述 lockname 鎖的名稱 lockhandle 與該名稱相對應的鎖的唯一標識 expiration_secs 這種名稱到鎖的映射的保存時間

當多個session用同樣的名字lockname來獲取唯一標識字符串時，不同的session用同樣名字獲取的lockhandle是相同的，lockname是最大128位的字符串，而且是大小寫敏感的，鎖的名字最好不要用"ORA$"打頭，這種鎖的名稱是被oracle保留的名稱。DBMS_LOCK.ALLOCATE_UNIQUE執行完後就會commit所以不能被trigger調用。所有獲得的映射都為存放在SYS用戶DBMS_LOCK_ALLOCATED視圖中。

FUNCTION DBMS_LOCK.REQUEST
　　 (id IN INTEGER
　　 ,lockmode IN INTEGER DEFAULT X_MODE
　　 ,timeout IN INTEGER DEFAULT MAXWAIT
　　 ,release_on_commit IN BOOLEAN DEFAULT FALSE)
RETURN INTEGER;
FUNCTION DBMS_LOCK.REQUEST
　　 (lockhandle IN VARCHAR2
　　 ,lockmode IN INTEGER DEFAULT X_MODE
　　 ,timeout IN INTEGER DEFAULT MAXWAIT
　　 ,release_on_commit IN BOOLEAN DEFAULT FALSE)
RETURN INTEGER;

參數 描述 id 鎖的唯一標識 lockhandle 由DBMS_LOCK.ALLOCATE_UNIQUE返回的handle lockmode 鎖的模式 timeout 等待時間 release_on_commit COMMIT or ROLLBACK事務時是否釋放鎖 返回值 描述 0 成功申請到鎖 1 超時 2 死鎖 3 參數錯誤 4 已經擁有特定id或handle的鎖 5 不合法的lockhandle

用戶定義的鎖標識必須在 0 到 1073741823. 鎖標識在范圍2000000000 到 2147483647 被oracle公司預先保留。推薦用lockhandle的方法獲得鎖，因為鎖的名稱是比較容易辨別的，也是比較容易描述的。第一種方法不被oracle推薦。

在共享服務器模式和分布式事務時我們最好把release_on_commit設置為true。

FUNCTION DBMS_LOCK.RELEASE
　　 (id IN INTEGER)
RETURN INTEGER;
FUNCTION DBMS_LOCK.RELEASE
　　 (lockhandle IN VARCHAR2)
RETURN INTEGER;

參數 描述 id 鎖的數字標識 lockhandle ALLOCATE_UNIQUE返回的鎖的handle 返回值 描述 0 成功 3 參數錯誤 4 並沒有擁有特定的鎖 5 不合法的lockhandle

RELEASE 函數用來釋放先前申請的鎖。當鎖不用時最好立即釋放，這是很好的習慣。鎖本身就是寶貴的資源，並且可以盡早釋放被鎖住的資源，而且可以有效地避免死鎖。

如何使用這些api，很容易只要在我們的存儲過程之前或者之後調用申請鎖，釋放鎖（或者在事務提交或rollback的時候自動釋放鎖）就可以了，但這樣也帶來了存儲過程代碼的侵入性，每個存儲過程都必須調用申請鎖，釋放鎖。我們可以寫一個wrapper把鎖的申請和釋放包裹起來。類似於模板模式。

create or replace package FRM_TEST_TESTING is
　　 PROCEDURE loop4_specific_round;
end FRM_TEST_TESTING;
create or replace package body FRM_TEST_TESTING is
　 -- Function and procedure implementations
　 --PROCEDURE loop4_specific_round(p_loop_count IN INTEGER) AS
　 PROCEDURE loop4_specific_round AS
　 PRAGMA AUTONOMOUS_TRANSACTION;
　 BEGIN　
　　 FOR r IN 1 .. 60 LOOP
　　　 SYS.dbms_lock.sleep(20);
　　　 DBMS_OUTPUT.PUT_LINE('During testing SP executing. '|| to_char(sysdate,'MM/DD/YYYY HH24:MI:SS'));
　　　 insert into TEST_TEST
　　　　 (name, Creationtime)
　　　 values
　　　　 ('1111', sysdate);
　　 commit;
　　 END LOOP;
　 END loop4_specific_round;
end FRM_TEST_TESTING;
create or replace package frm_test_task_pkg is
　 function　frm_test_task_func(i_lock_name in varchar2, i_procname in varchar2, i_expiration_time Integer default 864000, i_wait_time Integer default DBMS_LOCK.maxwait) return number;
end frm_test_task_pkg;
create or replace package body frm_test_task_pkg is
　 procedure app_task_wrapper_proc(i_procname in varchar2) as
　 PRAGMA AUTONOMOUS_TRANSACTION;
　 cur BINARY_INTEGER := DBMS_SQL.OPEN_CURSOR;
　 fdbk BINARY_INTEGER;
　 begin
　　 DBMS_SQL.PARSE (cur,
　　　 'begin ' || i_procname　|| ';end;',
　　 DBMS_SQL.NATIVE);
　　 fdbk := DBMS_SQL.execute (cur);
　　 DBMS_OUTPUT.put_line('Fetch rows : ' || fdbk);
　　 DBMS_SQL.close_cursor(cur);
　　 commit;
　 end app_task_wrapper_proc;
　 function　frm_test_task_func(i_lock_name in varchar2, i_procname in varchar2, i_expiration_time Integer default 864000, i_wait_time Integer default DBMS_LOCK.maxwait) return number is
　　　 v_result　　 number;
　　　 v_lockhandle varchar2(200);
　　　 v_sid　 pls_integer;
　 begin
　　 dbms_lock.allocate_unique(i_lock_name, v_lockhandle, i_expiration_time);
　　 v_result := dbms_lock.request(v_lockhandle, dbms_lock.x_mode, i_wait_time, true);
　　 select sys_context('USERENV','SID') into v_sid from dual;
　　 dbms_output.put_line (to_char(systimestamp,'HH24:MI:SS.FF3')||': SID '||v_sid||' requests lock');
　　 if v_result <> 0 then
　　 dbms_output.put_line(
　　　　　 case
　　　　　　　 when v_result=1 then 'Timeout'
　　　　　　　 when v_result=2 then 'Deadlock'
　　　　　　　 when v_result=3 then 'Parameter Error'
　　　　　　　 when v_result=4 then 'Already owned'
　　　　　　　 when v_result=5 then 'Illegal Lock Handle'
　　　　　　 end);
　　 else
　　　　　 app_task_wrapper_proc(i_procname);
　 end if;
　　 commit;
　　 return v_result;
　 EXCEPTION
　 WHEN OTHERS
　 THEN
　　　 /**//*
　　　 || Anonymous block inside the exception handler lets me declare
　　　 || local variables to hold the error code information.
　　　 */
　　　 DECLARE
　　　　 error_code NUMBER := SQLCODE;
　　　　 error_msg　VARCHAR2 (300) := SQLERRM;
　　　 BEGIN
　　　　　 DBMS_OUTPUT.put_line(error_code || ': ' ||error_msg);
　　　　　 --RE RAISE ERROR TO CLIENT
　　　　　 raise;
　　　 END; -- End of anonymous block.
　 end frm_test_task_func;
end frm_test_task_pkg;

在java端，應用程序只需要調用frm_test_task_func，把需要串行化的存儲過程作為參數傳入。Java端還需要提供db鎖的邏輯名，這樣同步在相同的邏輯名的鎖上的存儲過程會同步執行。在實際存儲過程參數比較復雜的情況下，傳參可能是個問題。

其實還有一種方法，對已有的存儲過程代碼沒有侵入性，申請和釋放db鎖在java端完成，oracle保證了session斷掉後，會釋放session占有的鎖資源。所以如果應用服務器down掉後，session在經過一段時間後會被釋放，鎖資源也會被釋放。現在要考慮的是是否在事務結束的時候自動釋放鎖，考慮到現在申請鎖是由java端完成的，所以釋放鎖也由java端顯式的調用release釋放。

create or replace package body frm_test_task_pkg is
procedure frm_test_lock_acquire(i_lock_name in varchar2, i_expiration_time in Integer default 864000, i_wait_time in Integer default DBMS_LOCK.maxwait, o_result out number, o_lockhandle out varchar2) as
　　　 v_result　　 number;
　　　 v_lockhandle varchar2(200);
　 begin
　　 --acquire a unique lock id
　　 sys.dbms_lock.allocate_unique(i_lock_name, v_lockhandle, i_expiration_time);
　　 --acquire a lock
　　 v_result := sys.dbms_lock.request(v_lockhandle, dbms_lock.x_mode, i_wait_time, false);
　　 --set return values
　　 o_result := v_result;
　　 o_lockhandle := v_lockhandle;
end frm_test_lock_acquire;
function frm_test_lock_release(i_lockhandle in varchar2) return number as
　　　 v_result number;
begin
　　　 --release lock according to lockhandle
　　　 v_result := sys.dbms_lock.release(i_lockhandle);
　　　 return v_result;
end frm_test_lock_release;
end frm_test_task_pkg;

這樣java端需要先調用frm_test_lock_acquire申請鎖，然後執行用戶邏輯的存儲過程，最後在顯式的調用frm_test_lock_release釋放鎖。如果不想每次用到的時候都去申請，釋放鎖，在java端也可以使用模板模式，假設子類實現execute方法來完成需要串行化執行的存儲過程，這時要注意我們可能會將connection傳入到子類的execute方法中，但是子類卻不能將connection關閉掉，因為我們還需要在execute方法執行完後會用它來釋放鎖。當然如果子類真的把物理的connection關閉掉也沒有問題，但是現在我們大都使用connection pool，把connection返回給pool的時候，session的鎖資源並沒有清除。這樣還需要將傳入到子類execute方法的connection封裝一下，或者叫裝飾一下，我們有兩種解決方法：

·oonnection wrapper的close並不關閉連接或者返回給pool。

·connection wrapper的close方法關閉連接，但是需要在關閉連接之前釋放鎖，然後在抽象父類的方法中只需判斷connection是否close掉，connection.isClosed()方法,如果沒有close就調用wrapeer的close方法，既釋放鎖又關閉連接。

總結一下：lock放在java端會出現意外的情況，鎖就必須放在db端，為了避免對已有存儲過程代碼的侵入性，可以使用wrapper存儲過程，由於動態執行存儲過程傳遞參數是個難題，所以還是把鎖的申請，釋放放在了java端，也避免了對已有存儲過程代碼的侵入性，同時又懶得每次都去申請鎖，釋放鎖，可以在java端使用模板模式，但是使用模板模式時又怕子類不小心關了connection，而沒釋放鎖，就將connection封裝了一下，將這個被封裝後的connection傳到了子類的execute方法中。