走自己的路

有人說我在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);

當多個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;

用戶定義的鎖標識必須在 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;

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方法中。