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. The following example shows how to release a lock.

Recently, developers have said that dbms_lock.allocate_unique custom locks cannot be released when dbms_lock.relase is used. The following example shows how to release a lock.

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
----------------------------------------------------------------
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 the end of the article.
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 handle
0

PL/SQL procedure successfully completed

-- View User-Defined locks in session 2
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 we get the return code of 4, that is, Do not own lock specified by id or lockhandle.

PL/SQL procedure successfully completed.

-- Release the lock in the original session 1 and directly call the DBMS_LOCK package. 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 allocated lock cannot be found in session 2
Goex_admin @ GOBO1> @ query_defined_lock

No rows selected

2. Custom lock Blocking

-- First assign a lock
-- Note that the SID before the following SQL prompt represents different sessions, such as 1073 @ GOBO1>, indicating 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.

-- Try to request the lock and insert data in the second session 1032
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_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 a character automatically generated every s when SecureCRT is idle.
0 ---> that is, the session is blocked.

PL/SQL procedure successfully completed.

-- Try to request the lock and insert data in the third session 1033
1033 @ GOBO1> SET SERVEROUTPUT ON
1033 @ 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 symbol description of this line is the same as session 1032
0

PL/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 Exclusive lock of session 1073 blocks the Row Share of session 1032 and session 1033.
1037 @ GOBO1> @ waiting_sess_by_lock