Dbms_lock.relase cannot release custom locks?

Recently, developers have said that dbms_lock.allocate_unique custom locks cannot be released when dbms_lock.relase is used. Here is an example to illustrate how it works?
 
1. demonstrate that the lock cannot be released

-- 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 package lock_demo to assign 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/10737420671073742067151 -----> get lock handle0pl/SQL procedure successfully completed -- view the custom lock goex_admin @ gobo1> @ brief 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_lockgoex_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 we get the return code 4, that is, do not own lock specified by ID or lockhandlepl/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_lockno rows selected cannot be found in session 2.

2. Custom lock Blocking

-- Assign a lock first -- note that the SID before the following SQL prompt represents different sessions, for example, 1073 @ gobo1>, indicating that the session ID is 1073. Similar to the following. 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/define 37420671_37420671510pl/SQL procedure successfully completed. -- In the second session 1032, try to request the lock and insert data 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 ('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 data 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 ('enabled', systimestamp); 15 16 commit; 17 end; 18/> 10737420671073742067151 ---> the line symbol is the same as session 10320pl/SQL procedure successfully completed. -- observe the blocking situation in another session -- The following query is executed before the Lock of session 1073 is released, we can see that the 1073 exclusive lock blocks the row Between 1032 and 1033 1037 @ gobo1> @ inclusid username osuser terminal object_name ty lock mode req mode ---------- -------------- detail ---------- detail -- --------------- 1032 goex_robin Admin 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/0pl/SQL procedure successfully completed. -- Author: Robinson -- Blog: http://blog.csdn.net/robinson_0612 1073 @ gobo1> select * From lock_test; action when ---------- starting started 20-jun-13 blocks pmended 20-jun-13 blocks pmstarted 20-jun-13 blocks pmended limit pmstarted limit pmended limit Limit PM

3. Code Used in the demo

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. Summary
A. From the test above, we can see that the locks manually allocated using dbms_lock can only be released in the same session.
B. Use dbms_lock to manage display locks and implicit locks to implement the same functions.
C. For more information, see Oracle dbms_lock.

 

 

More references

DML error logging

PL/SQL --> cursor

PL/SQL --> implicit cursor (SQL % found)

Batch SQL forall statements

Bulk collect clause for batch SQL

Initialization and assignment of PL/SQL Sets

PL/SQL Union arrays and nested tables
PL/SQL variable-length Array
PL/SQL --> PL/SQL records

SQL tuning steps

Efficient SQL statements

Parent cursor, child cursor, and shared cursor

Bind variables and their advantages and disadvantages

Use of the display_cursor function of dbms_xplan

Use of the display function of dbms_xplan

Description of each field module in the execution plan

Use explain plan to obtain the SQL statement execution plan