|
|
Customers often ask, how to make sure, that a particular PL/SQL-code which runs in several sessions at the same time, process the data for the FIFO paradigm. The answer is: no problem at all. Oracle offers the dbms_lock package which is very helpful there to solve problems arround locking and synchronizing.
The best way to handle it is, to each have a function for set and release a lock. Look at the following examples:
create or replace package body my_package is
[...]
function lockSemaphore(pi_lock_name in varchar2,
po_lock_result out number) return boolean is
PRAGMA AUTONOMOUS_TRANSACTION;
l_lock_handle varchar2(128);
l_lock_duration number := 3600; -- 1h
begin
-- try to get a lock handle
dbms_lock.allocate_unique(pi_lock_name, l_lock_handle);
-- set a lock for the desired time
po_lock_result := dbms_lock.request(l_lock_handle, 6, l_lock_duration); -- 6 = exclusive, wait for ever
-- if the lock is new or the session has already set the same lock return true
if (po_lock_result in (0, 4)) then
return(true);
else
-- otherwise return false
return(false);
end if;
end;
[...]
---
--- releaseSemaphore
---
function releaseSemaphore(pi_lock_name in varchar2,
po_lock_result out number) return boolean is
PRAGMA AUTONOMOUS_TRANSACTION;
l_lock_handle varchar2(128);
begin
-- first, get the lock-handle with the lock-name
dbms_lock.allocate_unique(pi_lock_name, l_lock_handle);
-- try to release the lock
po_lock_result := dbms_lock.release(l_lock_handle);
-- if release is ok, return true
if (po_lock_result = 0) then
return(true);
else
-- otherwise false
return(false);
end if;
end;
[...]
Now you can use the lock-function to manualy set a lock. If more functions want to set the lock at the same time, the calls will be queued (waiting), until the function which is called prior releases the lock. Example:
[...]
declare
l_lock_result number;
l_lock_name varchar2(20);
begin
l_lock_name := 'my lock';
if (my_package.lockSemaphore(l_lock_name, l_lock_result)) then
dbms_output.put_line('Lock success: ' || l_lock_result);
else
dbms_output.put_line('Lock Error: ' || l_lock_result);
end if;
[...] some processing for which it's necessary to run only once at one time
if (my_package.releaseSemaphore(l_lock_name, l_lock_result)) then
dbms_output.put_line('Release success: ' || l_lock_result);
else
dbms_output.put_line('Release Error: ' || l_lock_result);
end if;
end;
[...]