dbms_lock.relase 無法釋放自訂的鎖?

      最近開發人員說使用dbms_lock.allocate_unique自訂的鎖在使用dbms_lock.relase無法釋放，下面來個示範的例子來看看到底怎麼一回事？
 
1、示範鎖不能釋放的情形   

--示範環境goex_admin@GOBO1> select * from v$version where rownum<2;BANNER----------------------------------------------------------------Oracle Database 10g Release 10.2.0.3.0 - 64bit Production--調用包lock_demo來分配一個鎖，關於lock_demo包的代碼見文章尾部goex_admin@GOBO1> DECLARE  2     s   VARCHAR2 (200);  3  BEGIN  4     lock_demo.request_lock (6, s);  5     DBMS_OUTPUT.put_line (s);  6  END;  7  /10737420671073742067151                  ----->得到lock handle0PL/SQL procedure successfully completed--在session 2查看使用者自訂鎖goex_admin@GOBO1> @query_defined_lockNAME           PROGRAM                  SPID     OSUSER    SID PID     TERMINAL   STATUS       LOCKID EXPIRATION-------------- ------------------------ -------- -------- ---- ------- ---------- -------- ---------- -----------------control_lock   sqlplus@SZDB (TNS V1-V3) 30841    robin    1049 14567   pts/0      INACTIVE 1073742067 20130420 18:00:00--在session 2嘗試釋放在session分配的鎖，直接調用包DBMS_LOCKgoex_admin@GOBO1> DECLARE   2    RetVal NUMBER;  3    LOCKHANDLE VARCHAR2(32767);  4    5  BEGIN   6    LOCKHANDLE := '10737420671073742067151';  7    8    RetVal := SYS.DBMS_LOCK.RELEASE ( LOCKHANDLE );  9   10    DBMS_OUTPUT.Put_Line('RetVal = ' || TO_CHAR(RetVal)); 11   12    DBMS_OUTPUT.Put_Line(''); 13   14    COMMIT;  15  END;  16  /RetVal = 4       ----->此處獲得了為4的返回碼即Do not own lock specified by id or lockhandlePL/SQL procedure successfully completed.--在原來的session 1釋放鎖，直接調用包DBMS_LOCK，此時鎖被成功釋放goex_admin@GOBO1> DECLARE   2    RetVal NUMBER;  3    LOCKHANDLE VARCHAR2(32767);  4    5  BEGIN   6    LOCKHANDLE := '10737420671073742067151';  7    8    RetVal := SYS.DBMS_LOCK.RELEASE ( LOCKHANDLE );  9   10    DBMS_OUTPUT.Put_Line('RetVal = ' || TO_CHAR(RetVal)); 11   12    DBMS_OUTPUT.Put_Line(''); 13   14    COMMIT;  15  END;  16  /RetVal = 0                      --------> The lock was released successful.PL/SQL procedure successfully completed.--在session 2查詢不到之前分配的鎖goex_admin@GOBO1> @query_defined_lockno rows selected

2、自訂鎖阻塞的情形

--首先分配一個鎖--注意下面的SQL提示符之前的SID代表不同的session，如1073@GOBO1>，即表示session ID 為1073。以下類同。1073@GOBO1> SET SERVEROUTPUT ON1073@GOBO1> DECLARE  2     s   VARCHAR2 (200);  3  BEGIN  4     lock_demo.request_lock (6, s);  5     DBMS_OUTPUT.put_line (s);  6  END;  7  /107374206710737420671510PL/SQL procedure successfully completed.--在第二個session 1032中嘗試請求鎖並插入資料1032@GOBO1> SET SERVEROUTPUT ON1032@GOBO1> DECLARE  2     s   VARCHAR2 (200);  3  BEGIN  4     lock_demo.request_lock (DBMS_LOCK.ss_mode, s);  5    6     DBMS_OUTPUT.put_line (s);  7    8     INSERT INTO lock_test (action, when)  9          VALUES ('started', SYSTIMESTAMP); 10   11     DBMS_LOCK.sleep (5); 12   13     INSERT INTO lock_test (action, when) 14          VALUES ('ended', SYSTIMESTAMP); 15   16     COMMIT; 17  END; 18  />>10737420671073742067151   --->本行的符號“>>”是有SecureCRT在空閑每300s自動產生的字元0                           --->也就是session 被阻塞PL/SQL procedure successfully completed.--在第三個session 1033中嘗試請求鎖並插入資料1033@GOBO1> SET SERVEROUTPUT ON1033@GOBO1> DECLARE  2     s   VARCHAR2 (200);  3  BEGIN  4     lock_demo.request_lock (DBMS_LOCK.ss_mode, s);  5    6     DBMS_OUTPUT.put_line (s);  7    8     INSERT INTO lock_test (action, when)  9          VALUES ('started', SYSTIMESTAMP); 10   11     DBMS_LOCK.sleep (5); 12   13     INSERT INTO lock_test (action, when) 14          VALUES ('ended', SYSTIMESTAMP); 15   16     COMMIT; 17  END; 18  />>10737420671073742067151  --->本行的符號說明同session 10320PL/SQL procedure successfully completed.--在另外一個session觀察被阻塞的情形--下面的查詢在session 1073的鎖未釋放前執行，可以看到1073的Exclusive鎖阻塞了1032與1033的Row Share1037@GOBO1> @waiting_sess_by_lock       SID USERNAME       OSUSER          TERMINAL     OBJECT_NAME          TY Lock Mode   Req Mode---------- -------------- --------------- ------------ -------------------- -- ----------- -----------      1032 GOEX_ADMIN     robin           pts/4                             UL --Waiting-- Row Share      1033 GOEX_ADMIN     robin           pts/6                             UL --Waiting-- Row Share      1073 GOEX_ADMIN     robin           pts/2                             UL Exclusive--下面釋放session 1073的鎖1073@GOBO1> DECLARE  2    s VARCHAR2(200);  3  BEGIN  4    lock_demo.release_lock(s);  5    dbms_output.put_line(s);  6  END;  7  /0PL/SQL procedure successfully completed.--Author : Robinson--Blog   : http://blog.csdn.net/robinson_0612      1073@GOBO1> select * from lock_test;ACTION     WHEN---------- --------------------------------------------------started    20-JUN-13 05.00.36.845854000 PMended      20-JUN-13 05.00.41.841460000 PMstarted    20-JUN-13 05.00.36.845385000 PMended      20-JUN-13 05.00.41.841064000 PMstarted    20-JUN-13 04.39.46.303529000 PMended      20-JUN-13 04.39.51.345226000 PM

3、示範中用的代碼

CREATE TABLE lock_test (action VARCHAR2(10),when   TIMESTAMP(9));CREATE OR REPLACE PACKAGE GOEX_ADMIN.lock_demoIS   v_lockname     VARCHAR2 (12) := 'control_lock';   v_lockhandle   VARCHAR2 (200);   v_result       PLS_INTEGER;   -- obtain a lock   PROCEDURE request_lock (p_ltype INTEGER, p_retval OUT INTEGER);   -- release an existing lock   PROCEDURE release_lock (p_retval OUT INTEGER);   -- view the stored handle   FUNCTION see_handle      RETURN VARCHAR2;   -- decode lock request   FUNCTION decode_req (p_result PLS_INTEGER)      RETURN VARCHAR2;   -- decode lock release   FUNCTION decode_rel (p_result PLS_INTEGER)      RETURN VARCHAR2;END lock_demo;/CREATE OR REPLACE PACKAGE BODY GOEX_ADMIN.lock_demoIS   PROCEDURE request_lock (p_ltype IN INTEGER, p_retval OUT INTEGER)   IS   BEGIN      IF v_lockhandle IS NULL      THEN         DBMS_LOCK.allocate_unique (v_lockname, v_lockhandle);         p_retval := DBMS_LOCK.request (v_lockhandle, p_ltype);      END IF;      DBMS_OUTPUT.put_line (TO_CHAR (v_lockhandle));   END request_lock;   ------------------------------------------------------------   PROCEDURE release_lock (p_retval OUT INTEGER)   IS   BEGIN      IF v_lockhandle IS NOT NULL      THEN         p_retval := DBMS_LOCK.release (v_lockhandle);      END IF;   END release_lock;   ------------------------------------------------------------   FUNCTION see_handle      RETURN VARCHAR2   IS   BEGIN      IF v_lockhandle IS NOT NULL      THEN         RETURN v_lockhandle;      ELSE         RETURN 'Not Allocated';      END IF;   END see_handle;   ------------------------------------------------------------   FUNCTION decode_req (p_result PLS_INTEGER)      RETURN VARCHAR2   IS      retval   VARCHAR2 (20);   BEGIN      SELECT DECODE (p_result,  0, 'Success',  1, 'Timeout',  2, 'Deadlock',                 3, 'Parameter Error',  4, 'Already owned',  5, 'Illegal Lock Handle')        INTO retval        FROM DUAL;      RETURN retval;   END decode_req;   ------------------------------------------------------------   FUNCTION decode_rel (p_result PLS_INTEGER)      RETURN VARCHAR2   IS      retval   VARCHAR2 (20);   BEGIN      SELECT DECODE (p_result,  0, 3,  'Parameter Error', 4,  'Already owned', 5,  'Illegal Lock Handle')      INTO retval FROM DUAL;      RETURN retval;   END decode_rel;------------------------------------------------------------END lock_demo;/

4、小結 
a、從上面的測試可知，對於使用dbms_lock手動分配的鎖，只能在同一session釋放。
b、使用dbms_lock顯示鎖管理與隱式鎖管理能夠實現相同的功能。
c、文章參考: Oracle DBMS_LOCK

 

 

更多參考

DML Error Logging 特性 

PL/SQL --> 遊標

PL/SQL --> 隱式遊標(SQL%FOUND)

批量SQL之 FORALL 語句

批量SQL之 BULK COLLECT 子句

PL/SQL 集合的初始化與賦值

PL/SQL 聯合數組與巢狀表格
PL/SQL 變長數組
PL/SQL --> PL/SQL記錄

SQL tuning 步驟

高效SQL語句必殺技

父遊標、子遊標及共用遊標

綁定變數及其優缺點

dbms_xplan之display_cursor函數的使用

dbms_xplan之display函數的使用

執行計畫中各欄位各模組描述

使用 EXPLAIN PLAN 擷取SQL語句執行計畫