我們知道給資源上鎖可以使我們串行化地訪問資源，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方法中。
