Oracle dbms_lock package experiment

First look at the interfaces and functions of each package

-- Create or obtain the handle information of the same name lock
Dbms_lock.allocate_unique (
Lockname in varchar2, -- Name of the lock to be obtained
Lockhandle out varchar2, -- returned Lock handle information
Expiration_secs in integer default 864000); -- Automatically releases the lock after the last allocation

Convert lock types
Dbms_lock.convert (
ID in integer |
Lockhandle in varchar2, -- lock ID or Lock handle
Lockmode in integer, -- New lock mode
Timeout in number default maxwait) -- if the lock is not obtained within the specified time, 1 request is returned (timeout)
Return integer;

Release a lock by the lock ID or handle
Dbms_lock.release (
ID in integer)
Return integer;

Dbms_lock.release (
Lockhandle in varchar2)
Return integer;

Apply for lock with handle
Dbms_lock.request (
ID in integer |
Lockhandle in varchar2,
Lockmode in integer default x_mode,
Timeout in integer default maxwait,
Release_on_commit in Boolean default false)
Return integer;
Sleep
Dbms_lock.sleep (
Seconds in number); sleep duration (seconds)

Lab:

Session 1

Declare

V_lock_holder varchar2 (128 );
Begin
Dbms_lock.allocate_unique ('test _ lockname', v_lock_holder, 3600 );

Dbms_output.put_line (v_lock_holder );

End;

######################################## ######################################## #######
10737418641073741864187

Session2

Declare

V_lock_holder varchar2 (128 );
Begin
Dbms_lock.allocate_unique ('test _ lockname', v_lock_holder, 3600 );

Dbms_output.put_line (v_lock_holder );

End;

######################################## ######################################## #######
10737418641073741864187

Comments:
First, create a lock named test_lockname.
The session that returns the reference handle of the lock to obtain the handle can apply for, convert, and release the lock.
If the lock already exists, no error is reported and the handle of the lock is returned.
However, owning a handle does not mean that you can use the lock resources, but you have the right to apply.
It's like you know a person's phone number. You can call it, but it's not necessary to get through.
Then dbms_lock.request is used to apply for a lock.

Session 1

Declare
V_result number;
Begin

V_result: = dbms_lock.request ('20140901', dbms_lock.x_mode, 10, false );
Dbms_output.put_line (v_result );
End;
######################################## ######################################## #######
0

Session 2

Declare
V_result number;
Begin
Dbms_output.put_line (to_char (sysdate, 'hh24: MI: ss '));
V_result: = dbms_lock.request ('20140901', dbms_lock.x_mode, 10, false );
Dbms_output.put_line (v_result );
Dbms_output.put_line (to_char (sysdate, 'hh24: MI: ss '));
End;
######################################## ######################################## #######
14:07:58
1
14:08:08

Session 1

Declare
V_result number;
Begin

V_result: = dbms_lock.request ('20140901', dbms_lock.x_mode, 10, false );
Dbms_output.put_line (v_result );
End;
######################################## ######################################## #######
4

Comments:

When we use seesion1, the first application will be applied soon.
When seesion2 is used for application and the request can be retried for 10 seconds, it will not be returned until 10 seconds later.
When session1 is applied again, the returned value is 4, indicating that the session already has the lock.
The list of values corresponding to dbms_lock.request is as follows:

0 success
1 timeout
2 deadlock
3 parameter error
4 already own lock specified by ID or lockhandle
5 Illegal Lock handle

Session 3

Declare
V_result number;
Begin
V_result: = dbms_lock.release ('20140901 ');
Dbms_output.put_line (v_result );
End;
######################################## ######################################## #######
4

Session1

Declare
V_result number;
Begin
V_result: = dbms_lock.release ('20140901 ');
Dbms_output.put_line (v_result );
End;
######################################## ######################################## #######
0
 
Seesion2

Declare
V_result number;
Begin
Dbms_output.put_line (to_char (sysdate, 'hh24: MI: ss '));
V_result: = dbms_lock.request ('20140901', dbms_lock.x_mode, 10, false );
Dbms_output.put_line (v_result );
Dbms_output.put_line (to_char (sysdate, 'hh24: MI: ss '));
End;

######################################## ######################################## #######
14:26:45
0
14:26:45

Comments
Session3 tries to release a lock held by session1 but fails. The returned value is 4.
Session1 releases the lock held by the user and returns 0 if the lock is successfully released.
At this time, seesion2 will apply for the lock again, then the block will get.

Dbms_lock.release
0 success
3 parameter error
4 do not own lock specified by ID or lockhandle
5 Illegal Lock handle

There is also a conversion method, which also needs to be done by the owner, corresponding to the Return Value List

0 success
1 timeout
2 deadlock
3 parameter error
4 don't own lock specified by ID or lockhandle
5 Illegal Lock handle

Summary:
Although Oracle's dbms_lock package provides a complete lock conversion mechanism. But the lock itself is only a semaphore.
No database object is locked because of the lock.
The general application is limited to ensuring the serialization of some programs.