synchronized accessing table in Oracle

We have 2 session and 1 table to operate with

If we want to edit, we check lock and throw an error if the table is already busy:

	declare
	v_result number;
	v_lockhandle varchar2(200);
	v_lock_name varchar2(200);
	in_use exception;
	pragma exception_init(in_use, -20001);
	l_message varchar2(200) := 'System waiting while loading';
	begin
	v_lock_name := user || '_queue_lock';
	dbms_lock.allocate_unique(lockname => v_lock_name,
	lockhandle => v_lockhandle);
	v_result := dbms_lock.request(lockhandle => v_lockhandle,
	lockmode => dbms_lock.x_mode,
	timeout => 0,
	release_on_commit => true);
	if v_result != 0 then
	raise_application_error(-20001, l_message);
	end if;
	end;

view raw wait_while_loading.sql hosted with ❤ by GitHub

While load we make lock befor

	declare
	v_result number;
	v_lockhandle varchar2(200);
	v_lock_name varchar2(200);
	begin
	v_lock_name := user || '_queue_lock';
	dbms_lock.allocate_unique(lockname => v_lock_name,
	lockhandle => v_lockhandle);
	v_result := dbms_lock.request(lockhandle => v_lockhandle,
	lockmode => dbms_lock.x_mode,
	timeout => 600,
	release_on_commit => false);
	end;

view raw lockTasksQueue.sql hosted with ❤ by GitHub

And unlock after

	declare
	v_result number;
	v_lockhandle varchar2(200);
	v_lock_name varchar2(200);
	begin
	v_lock_name := user || '_queue_lock';
	dbms_lock.allocate_unique(v_lock_name, v_lockhandle);
	v_result := dbms_lock.release(v_lockhandle);
	end;

view raw unlockTasksQueue.sql hosted with ❤ by GitHub

For working with dbms_lock you need get right:

grant execute on sys.dbms_lock to lock_user;

view raw grant_dbms_lock.sql hosted with ❤ by GitHub

To view locks

	with sess as (select s.sid,
	to_char(s.logon_time, 'dd.mm hh24:mi') logon_time,
	s.username,
	s.module,
	s.command cmd,
	s.last_call_et lc,
	s.seconds_in_wait sw,
	s.process,
	s.status
	from gv$session s join gv$process p
	on p.addr = s.paddr
	and p.inst_id = s.inst_id
	where s.type != 'BACKGROUND'
	and s.username in ('LOCK_USER')),
	blocked_sess as
	(select la.name,
	la.lockid,
	lo.lock_id2,
	to_char(la.expiration, 'dd.mm') exp,
	lo.session_id sid,
	lo.lock_type,
	lo.mode_held,
	lo.mode_requested,
	lo.blocking_others
	from sys.dbms_lock_allocated la join dba_locks lo
	on (la.lockid = lo.lock_id1)
	where la.name not like 'ORA$%')
	select bs.*, s.*
	from sess s left join blocked_sess bs on s.sid = bs.sid
	order by s.logon_time desc, s.username, s.status;

view raw view_lock_1.sql hosted with ❤ by GitHub

select * from v$lock where type='UL';

view raw view_lock_2.sql hosted with ❤ by GitHub

	select (select username
	from v$session
	where sid = a.sid) blocker
	, a.sid
	, ' is blocking '
	, (select username
	from v$session
	where sid = b.sid) blockee
	, b.sid
	from v$lock a
	, v$lock b
	where a.block = 1
	and b.request > 0
	and a.id1 = b.id1
	and a.id2 = b.id2;

view raw one session blocked another.sql hosted with ❤ by GitHub

User defined locking with DBMS_LOCK
DBMS_LOCK.REQUEST (PLS5H3)