Dbms_lock.relase cannot release custom locks

Dbms_lock.relase cannot release custom locks to solve the problem. Recently, developers say that dbms_lock.allocate_unique custom locks cannot be released when dbms_lock.relase is used. The following is an example to illustrate how it works? 1. Demonstrate the case where the lock cannot be released [SQL] -- demo environment goex_admin @ GOBO1> select * from v $ version where rownum <2; BANNER implements Oracle Database 10g Release 10.2.0.3.0-64bit Production -- call the lock_demo package to allocate a lock. For the code of the lock_demo package, see 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/107374206710737420 67151 -----> Get lock handle 0 PL/SQL procedure successfully completed -- view the custom lock goex_admin @ GOBO1> @ query_defined_lock NAME 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 -- try to release the lock allocated in session 2 and directly call the package DBMS_LOCK goex_admin @ GOBO1> DECLARE 2 RetVal NUMBER; 3 LOCKHANDLE VARCHAR2 (32767); 4 5 BEGIN 6 LOCKHANDLE: = '000000'; 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 -----> here, the return code of 4 is obtained, that is, Do not own l. Ock specified by id or lockhandle PL/SQL procedure successfully completed. -- release the lock in the original session 1 and directly call the package DBMS_LOCK. At this time, the lock is successfully released goex_admin @ GOBO1> DECLARE 2 RetVal NUMBER; 3 LOCKHANDLE VARCHAR2 (32767 ); 4 5 BEGIN 6 LOCKHANDLE: = '000000'; 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. -- the previously assigned lock goex_admin @ GOBO1> @ query_defined_lock no rows selected is not found in session 2. Custom lock blocking [SQL] -- first assign a lock -- pay attention to the following SQL the SID before the prompt indicates different sessions, for example, 1073 @ GOBO1> indicates that the session ID is 1073. Similar to the following. 1073 @ GOBO1> set serveroutput on 1073 @ 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 0 PL/SQL procedure successfully completed. -- In the second session 1032, try to request the lock and insert data 1032 @ GOBO1> set serveroutput on 1032 @ GOBO1> DECLARE 2 s VARCHAR2 (200); 3 BEGIN 4 lock_demo.request_lock (DBMS_LOCK.ss_mode, s); 5 6 DBMS_OUTPUT.put_li Ne (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 ('enabled', SYSTIMESTAMP); 15 16 COMMIT; 17 END; 18/> 10737420671073742067151 ---> the line's symbol ">" is a character 0 automatically generated every seconds when SecureCRT is idle ---> that is, the session is blocked by PL/SQL procedure successfully completed. -- In the third session 1033, try to request the lock and insert the data 1033 @ GOBO1> SET SERVEROUT Put on 1033 @ GOBO1> DECLARE 2 s VARCHAR2 (200); 3 BEGIN 4 lock_demo.request_lock (DBMS_LOCK.ss_mode, s); 5 6 rows (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 ('enabled', SYSTIMESTAMP ); 15 16 COMMIT; 17 END; 18/> 10737420671073742067151 ---> the line symbol is equivalent to session 1032 0 PL/S. QL procedure successfully completed. -- observe the blocking situation in another session -- The following query is executed before the Lock of session 1073 is released, the Exclusive Lock of 1073 blocks Row Share 1032 @ GOBO1> @ inclusid username osuser terminal OBJECT_NAME TY Lock Mode Req Mode ---------- ------------ detail ---------- detail -- ------------- 1033 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 -- release session 1073 lock 1073 @ GOBO1> DECLARE 2 s VARCHAR2 (200 ); 3 BEGIN 4 lock_demo.release_lock (s); 5 dbms_output.put_line (s); 6 END; 7/0 PL/SQL procedure successfully completed. 1073 @ GOBO1> select * from lock_test; action when ---------- ---------------------------------------------------- started 20-JUN-13 05.00.36.845854 000 PM ended 20-JUN-13 scheduled PM started 20-JUN-13 scheduled PM ended 20-JUN-13 scheduled PM started 20-JUN-13 scheduled PM ended 20-JUN-13 scheduled PM 3. Code Used in the demo [SQL] CREATE TABLE lock_test (action VARCHAR2 (10), when TIMESTAMP (9); create or replace package GOEX_ADMIN.lock_demo IS v_lockname VARCHAR2 (12): = 'control _ lock'; v_lockhandle VARCHAR2 (2 00); 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) RETUR N VARCHAR2; END lock_demo;/create or replace package body metadata is procedure request_lock (p_ltype in integer, p_retval out integer) is begin if v_lockhandle is null then encode (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; using 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_hand Le; functions decode_req (p_result PLS_INTEGER) RETURN VARCHAR2 IS retval VARCHAR2 (20); begin select decode (p_result, 0, 'success', 1, 'timeout', 2, 'destlock', 3, 'parameter error', 4, 'already owned', 5, 'illegal Lock handle') INTO retval from dual; RETURN retval; END decode_req; then ;------------------------------------------------------- ----- 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. Summary a. FROM the test above, we can see that for the locks manually allocated using dbms_lock, it can only be released in the same session. B. Use dbms_lock to manage display locks and implicit locks to implement the same functions.