Copy of adp-gmbh.ch: Synchronize sessions so that they starting an action at the same time
This example is copied from adp-gmbh.ch.
Three sessions are needed for this experiment.
Let's say, we want two sessions to do a task concurrently, that is, both tasks should start at the same time.
This is ideally solved with dbms_lock.
The following table will be filled by these two tasks:
create table lock_test (
action varchar(10),
when date
);
In the first session, an exclusive lock (indicated by the x_mode flag) is requested:
declare
v_lockhandle varchar2(200);
v_result number;
begin
dbms_lock.allocate_unique('control_lock', v_lockhandle);
v_result := dbms_lock.request(v_lockhandle, dbms_lock.x_mode);
if v_result <> 0 then
dbms_output.put_line(
case
when v_result=1 then 'Timeout'
when v_result=2 then 'Deadlock'
when v_result=3 then 'Parameter Error'
when v_result=4 then 'Already owned'
when v_result=5 then 'Illegal Lock Handle'
end);
end if;
end;
/
The following block is identical for the two other sessions. Similarly to the first session, a lock is requested, however, this time, the lock is a shared lock (indicated by the ss_mode flag).
According to the lock compatibility chart, a shared lock cannot be aquired if another session holds an exclusive lock. So, if a session executes the following block, it will stop execution on the request() call until the session holding the exclusive lock releases that lock.
Ok, the following block is started within two different sessions:
declare
v_result number;
v_lockhandle varchar2(200);
begin
dbms_lock.allocate_unique('control_lock', v_lockhandle);
v_result := dbms_lock.request(v_lockhandle, dbms_lock.ss_mode);
if v_result <> 0 then
dbms_output.put_line(
case
when v_result=1 then 'Timeout'
when v_result=2 then 'Deadlock'
when v_result=3 then 'Parameter Error'
when v_result=4 then 'Already owned'
when v_result=5 then 'Illegal Lock Handle'
end);
end if;
insert into lock_test values ('started', sysdate);
dbms_lock.sleep(5);
insert into lock_test values ('ended' , sysdate);
commit;
end;
/
Now, the first session can release the exclusive lock:
declare
v_lockhandle varchar2(200);
v_result number;
begin
dbms_lock.allocate_unique('control_lock', v_lockhandle);
v_result := dbms_lock.release(v_lockhandle);
if v_result <> 0 then
dbms_output.put_line(
case
when v_result=1 then 'Timeout'
when v_result=2 then 'Deadlock'
when v_result=3 then 'Parameter Error'
when v_result=4 then 'Already owned'
when v_result=5 then 'Illegal Lock Handle'
end);
end if;
end;
/
As soon as the exclusive lock is released, both sessions that waited on aquiring the shared lock can resume their execution. This time, according to the lock compatibility chart, it is possible for two (or more) sessions to hold a shared lock. This, after all, is why it is called shared.
The two sessions will return after five seconds. Let's see what they've written into lock_test:
select to_char(when,'dd.mm.yyyy hh24:mi:ss'), action from lock_test order by when;
Both sessions started at the same time!
Locking one or all tables
create or replace package tq84_lock_test authid definer as -- {
function i_want_to_read (tab varchar2) return varchar2;
function i_want_to_write(tab varchar2) return varchar2;
procedure i_am_done_with (tab varchar2);
function prevent_all_modifications return varchar2;
procedure enable_all_modifications;
end tq84_lock_test; -- }
/
create or replace package body tq84_lock_test as -- {
-- Private functions {
function lockhandle(id varchar2) return varchar2 is -- {
lckhnd varchar2(128);
begin
sys.dbms_lock.allocate_unique( -- {
lockname => id,
lockhandle => lckhnd
); -- }
return lckhnd;
end lockhandle; -- }
function lock_(id varchar2, mode_ integer) return boolean is -- {
--
-- Returns true if lock could be acquired, and
-- false if lock is held by another session.
--
req_res integer;
begin
req_res := sys.dbms_lock.request ( -- {
lockhandle => lockhandle(id),
lockmode => mode_,
timeout => 0
); -- }
if req_res = 1 then return false; end if;
if req_res not in (0, 4) then -- {
raise_application_error(-20800, 'unpexected result when trying to lock ' || id || ': ' || req_res);
end if; -- }
return true;
end lock_; -- }
procedure release_(id varchar2) is -- {
req_res integer;
begin
req_res := dbms_lock.release(lockhandle(id));
if req_res not in (0, 4) then raise_application_error(-20800, 'unexpected result ' || req_res || ' when trying to release ' || id); end if;
end release_; -- }
-- }
function i_want_to_read (tab varchar2) return varchar2 is -- {
req_res integer;
begin
if not lock_('all', sys.dbms_lock.s_mode) then -- {
return 'All tables are locked';
end if; -- }
if lock_(tab, sys.dbms_lock.s_mode) then -- {
return 'OK, table ' || tab || ' is now locked in shared mode';
end if; -- }
return 'Could not acquire lock for table ' || tab;
end i_want_to_read; -- }
function i_want_to_write(tab varchar2) return varchar2 is -- {
req_res integer;
begin
if not lock_('all', sys.dbms_lock.s_mode) then -- {
return 'All tables are locked';
end if; -- }
if lock_(tab, sys.dbms_lock.x_mode) then -- {
return 'OK, table ' || tab || ' is now locked in exclusive mode';
end if; -- }
return 'Could not acquire lock for table ' || tab;
end i_want_to_write; -- }
procedure i_am_done_with(tab varchar2) is -- {
begin
release_( tab );
release_('all');
end i_am_done_with; -- }
function prevent_all_modifications return varchar2 is -- {
begin
if lock_('all',sys.dbms_lock.x_mode) then return 'ALL tables are locked'; end if;
return 'Cannot exclusively lock ALL tables';
end prevent_all_modifications; -- }
procedure enable_all_modifications is -- {
begin
release_('all');
end enable_all_modifications; -- }
end tq84_lock_test; -- }
/
alter session set events 'immediate crash';
select sys_context('userenv', 'sid') from dual;
set serveroutput on
-- T1
begin dbms_output.put_line(tq84_lock_test.i_want_to_read ('T1')); end;
/
begin dbms_output.put_line(tq84_lock_test.i_want_to_write('T1')); end;
/
begin tq84_lock_test.i_am_done_with ('T1'); end;
/
-- T2
begin dbms_output.put_line(tq84_lock_test.i_want_to_read ('T2')); end;
/
begin dbms_output.put_line(tq84_lock_test.i_want_to_write('T2')); end;
/
begin tq84_lock_test.i_am_done_with ('T2'); end;
/
alter session set events 'immediate crash';
select sys_context('userenv', 'sid') from dual;
set serveroutput on
begin dbms_output.put_line(tq84_lock_test.prevent_all_modifications); end;
/
begin tq84_lock_test.enable_all_modifications; end;
/
select
case lck.lmode
when 4 then 'S'
when 6 then 'X'
else to_char(lck.lmode)
end lm,
round(lck.ctime/100) since_s,
ses.username,
dla.name,
ses.sid
FROM
v$lock lck join
v$session ses on lck.sid = ses.sid join
sys.dbms_lock_allocated dla on dla.lockid = lck.id1
where
1=1
-- lck.type = 'UL' and
-- dla.name = 'T1'
;
The blue and green session
blue.sql
prompt
prompt The "blue" session
prompt ==================
prompt
variable lockhandle varchar2(100)
variable x_mode number
exec :x_mode := dbms_lock.x_mode
exec dbms_lock.allocate_unique('tq84-lock', :lockhandle)
exec dbms_output.put_line ('lockhandle ' || :lockhandle || ' allocated');
select case dbms_lock.request(:lockhandle, :x_mode)
when 0 then 'Success'
when 1 then 'Timeout'
when 2 then 'Deadlock'
when 3 then 'Parameter error'
when 4 then 'Already own lock'
when 5 then 'Illegal lock handle' end from dual;
prompt Go now to the green session and press enter
prompt then press enter here to commit the blue session
accept x prompt " "
commit;
prompt commit issued.
prompt The green session should still be blocked.
prompt Press enter here to terminate the blue
prompt session. This should unblock the green
prompt session.
accept x prompt " "
exit;
green.sql
prompt
prompt The "green" session
prompt ===================
prompt
prompt the Blue session has allocated a lock
prompt press enter to try to allocate the same lock
accept x prompt "in the green session as well."
prompt
variable lockhandle varchar2(100)
variable x_mode number
exec :x_mode := dbms_lock.x_mode
exec dbms_lock.allocate_unique('tq84-lock', :lockhandle)
exec dbms_output.put_line ('lockhandle ' || :lockhandle || ' allocated');
select case dbms_lock.request(:lockhandle, :x_mode)
when 0 then 'Success'
when 1 then 'Timeout'
when 2 then 'Deadlock'
when 3 then 'Parameter error'
when 4 then 'Already own lock'
when 5 then 'Illegal lock handle' end from dual;
exit;
run.bat
@set connection_string=rene/rene
@start session_blue.bat
@start session_green.bat
session_blue.bat
@rem echo %connection_string%
@color 1f
@sqlplus -S %connection_string% @blue
session_green.bat
@rem echo %connection_string%
@color 2f
@sqlplus -S %connection_string% @green
stop_sessions/blue.sql
declare
lock_handle_stop varchar2(100);
lock_handle_running varchar2(100);
success number;
dummy number;
begin
-- We indicate that we're running
dbms_lock.allocate_unique('running', lock_handle_running);
success := dbms_lock.request(lock_handle_running, dbms_lock.s_mode, 0);
dbms_lock.allocate_unique('stop-iteration', lock_handle_stop);
loop
-- We try to allocate a shared lock on lock_handle_stop.
--
--
-- If another session already holds the lock exclusively,
-- request will return 1 (that is: timeout).
--
-- We wait 0 seconds, so that we immediatly can
-- exit the loop.
success := dbms_lock.request(lock_handle_stop, dbms_lock.s_mode, 0.1);
-- We also release the lock immediatly, so that we give
-- another session the chance to lock it exclusively:
dummy := dbms_lock.release(lock_handle_stop);
if success = 1 then -- timeout has occured
exit;
end if;
-- Wait for 5 to 10 seconds
--
-- This would be the "critical part".
dbms_lock.sleep(dbms_random.value(5,10));
end loop;
-- We're not really running anymore, so
-- we release the according lock, so that
-- the "green session" can enslock on this ure we're
-- not running anymore by successfully
-- requesting an exclusive lock
dummy := dbms_lock.release(lock_handle_running);
end;
/
exit
stop_sessions/green.sql
prompt
prompt press enter to stop the blue sessions
accept x prompt " "
declare
lock_handle_stop varchar2(100);
lock_handle_running varchar2(100);
success number;
dummy number;
begin
dbms_lock.allocate_unique('running' , lock_handle_running);
dbms_lock.allocate_unique('stop-iteration', lock_handle_stop );
-- We try to request an exclusive lock.
-- As soon as we got it, we know that the other jobs are not
-- in the "critical part"
--
-- We try 60 seconds only
success := dbms_lock.request(lock_handle_stop, dbms_lock.x_mode, 60);
if success = 1 then
dbms_output.put_line('Timeout, could not stop other jobs');
else
success := dbms_lock.request(lock_handle_stop, dbms_lock.x_mode, 60);
-- Now that we have exclusively gotten the running lock (which means that
-- the "critical part" can only be left, but not entered anymore, we
-- also want the running lock exclusively which we get as soon as all
--"blue" jobs have indicated they're leaving the processing.
success := dbms_lock.request(lock_handle_running, dbms_lock.x_mode, 60);
dbms_output.put_line('Other jobs should now be terminated, success: ' || success);
end if;
dummy := dbms_lock.release(lock_handle_stop);
end;
/
exit
stop_sessions/run.bat
@set connection_string=rene/rene
@rem --------------------------------
@rem start four sessions
@start session_blue.bat
@start session_blue.bat
@start session_blue.bat
@start session_blue.bat
@rem --------------------------------
@rem start another session to stop the
@rem executions of the other four
@rem sessions:
@start session_green.bat
stop_sessions/session_blue.bat
@color 1f
@sqlplus -S %connection_string% @blue
stop_sessions/session_green.bat
@color 2f
@sqlplus -S %connection_string% @green