Home > Data integrity, Development, Oracle Database, Oracle RAC > Distributed lock manager with Oracle database

Distributed lock manager with Oracle database

Here is the package :

create or replace package pkg_dlm is
  
	-- request lock in exclusive mode
	function request_x(
		lock_name       in varchar2,
		lock_timeout    in integer default dbms_lock.maxwait,
		lock_expiration in integer default 864000)
	  return integer;
	 
	-- request lock in shared mode
	function request_s(
		lock_name       in varchar2,
		lock_timeout    in integer default dbms_lock.maxwait,
		lock_expiration in integer default 864000)
	 return integer;
	
	-- upgrade lock from s to x
	function upgrade(
		lock_name       in varchar2,
		lock_timeout    in integer default dbms_lock.maxwait)
	 return integer;
	
	-- downgrade lock from x to s
	function downgrade(
		lock_name       in varchar2,
		lock_timeout    in integer default dbms_lock.maxwait)
	 return integer;
	  
	function release(
		lock_name in varchar2)
	  return integer;
	  
	function get_handle(
		lock_name       in varchar2,
		lock_expiration in integer default dbms_lock.maxwait)
	  return varchar2;
  
end pkg_dlm;
/

create or replace package body pkg_dlm is

	function request_x(
		lock_name       in varchar2,
		lock_timeout    in integer default dbms_lock.maxwait,
		lock_expiration in integer default 864000)
	  return varchar2
	is
	  lock_status number;
	begin
	  lock_status := dbms_lock.request( lockhandle => get_handle(lock_name, lock_expiration), lockmode => dbms_lock.x_mode, timeout => lock_timeout, release_on_commit => false);
	  -- 0,'Success',1,'Timeout',2,'Deadlock',3,'Parameter Error',4,'Already locked by me',5,'Illegal Lock Handle'
	  return lock_status;
	end request_lock;
	
	function request_s(
		lock_name       in varchar2,
		lock_timeout    in integer default dbms_lock.maxwait,
		lock_expiration in integer default 864000)
	  return varchar2
	is
	  lock_status number;
	begin
	  lock_status := dbms_lock.request( lockhandle => get_handle(lock_name, lock_expiration), lockmode => dbms_lock.s_mode, timeout => lock_timeout, release_on_commit => false);
	  -- 0,'Success',1,'Timeout',2,'Deadlock',3,'Parameter Error',4,'Already locked by me',5,'Illegal Lock Handle'
	  return lock_status;
	end request_lock;

	function release(
		lock_name in varchar2 )
	  return varchar2
	is
	  lock_status number;
	begin
	  lock_status := dbms_lock.release( lockhandle => get_handle(lock_name));
	  --0,'Success',3,'Parameter Error',4,'Already released',5,'Illegal Lock Handle'
	  return lock_status;
	end release_lock;

	function upgrade(
		lock_name in varchar2,
		lock_timeout in integer default dbms_lock.maxwait)
	return varchar2
	is
	  lock_status number;
	begin
	  lock_status := dbms_lock.convert(lockhandle => get_handle(lock_name), lockmode => dbms_lock.x_mode, timeout => lock_timeout);
	  --0,'Success',1,'Timeout',2,'Deadlock',3,'Parameter error',4,'Don''t own lock specified by id or lockhandle',5,'Illegal lock handle'
	  return lock_status;
	end;

	function downgrade(
		lock_name in varchar2,
		lock_timeout in integer default dbms_lock.maxwait)
	return varchar2
	is
	  lock_status number;
	begin
	  lock_status := dbms_lock.convert(lockhandle => get_handle(lock_name), lockmode => dbms_lock.s_mode, timeout => lock_timeout);
	  --0,'Success',1,'Timeout',2,'Deadlock',3,'Parameter error',4,'Don''t own lock specified by id or lockhandle',5,'Illegal lock handle'
	  return lock_status;
	end;
	
	function get_handle(
		lock_name       in varchar2,
		lock_expiration in integer default dbms_lock.maxwait)
	  return varchar2
	is
	  pragma autonomous_transaction;
	  lock_handle varchar2(128);
	begin
	  dbms_lock.allocate_unique( lockname => lock_name, lockhandle => lock_handle, expiration_secs => lock_expiration );
	  return lock_handle;
	end get_handle;

end pkg_dlm;
/

Usage :

select pkg_dlm.request_x('testlock') from dual ;
-- do some stuff
select pkg_dlm.release('testlock') from dual ;

Ref :
* http://jeffkemponoracle.com/2005/10/19/user-named-locks-with-dbms_lock/
* http://bluefrog-oracle.blogspot.fr/2013/12/user-defined-locking-with-dbmslock.html
* http://docs.oracle.com/cd/E11882_01/appdev.112/e40758/d_lock.htm

About these ads
  1. No comments yet.
  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

%d bloggers like this: