Locking mithilfe von DBMS_LOCK

Heute geht's um die Verwendung von DBMS_LOCK zum Sicherstellen von Integritätsbedingungen. Zur Demonstration wollen wir sicherstellen, dass pro Abteilung nur maximal ein Manager existiert. Wie bereit im vergangenen Post wird ein Compound Trigger verwendet:

create trigger check_manager_c_iu for insert or update of job, deptno on emp 
when (new.job = 'MANAGER' or old.deptno != new.deptno)
compound trigger

 type departments_nt is table of dept.deptno%type;
 departments_tab departments_nt := departments_nt();
 
 before each row is
 begin
  departments_tab.extend;
  departments_tab( departments_tab.last ) := :new.deptno;
 end before each row;
 
 after statement is
  v_manager_count integer := 0;
 begin
  departments_tab := set( departments_tab );
  for i in 1 .. departments_tab.count loop
   select 
    count(*)
   into 
    v_manager_count
   from 
    emp 
   where 
    job = 'MANAGER' and deptno = departments_tab(i);
   if ( v_manager_count > 1 ) then
    raise_application_error
    (
     -20010, 
     'No more than one manager allowed in department ' || departments_tab(i) || '.'
    );
   end if;
  end loop;
 end after statement;

end check_manager_c_iu;
/

Der BEFORE EACH ROW Teil speichert die Abteilungen in einer Nested Table, während der AFTER STATEMENT Teil die eigentliche Überprüfung vornimmt. Jedoch besteht immer noch die Möglichkeit, dass die Bedinung verletzt wird.

Zur Demonstration dienen zwei Sessions (T1, T2), welche die folgenden Operationen ausführen:

t₀ T1: INSERT MANAGER 'X' IN DEPARTMENT 40
t₁ T2: INSERT MANAGER 'Y' IN DEPARTMENT 40
t₂ T1: COMMIT;
t₃ T2: COMMIT;

Anmerkung: Wir nehmen an, dass Abteilung 40 vor dem Zeitpunkt t₀ keinen Manager besitzt.

Der INSERT-Befehl zum Zeitpunkt t₀ funktioniert, da noch kein Manager für Abteilung 40 existiert. Jedoch geht auch der zweite INSERT-Befehl von T2 durch, da T1 noch kein COMMIT ausgeführt hat. Nach Zeitpunkt t₃ existieren somit zwei Manager in Abteilung 40; eine Verletzung der geforderten Bedingung.

Eine mögliche Lösung für dieses Problem stellt DBMS_LOCK in Form der Prozedur ALLOCATE_UNIQUE und der Funktion REQUEST bereit. ALLOCATE_UNIQUE ordnet einem Namen eine eindeutige Nummer zu und gibt diese zurück. Die eigentliche Anfrage für diesen benannten Lock wird durch die Funktion REQUEST ausgeführt. Da die Prozedur ALLOCATE_UNIQUE einen impliziten COMMIT durchführt ist eine autonome Transaktion erforderlich.

Das nachfolgende Code Listing enthält eine Funktion, welche ALLOCATE_UNIQUE in einer anderen Transaktion aufruft und den Lock Handle zurückliefert. Die Prozedur REQUEST_LOCK stellt dann die Anfrage für den Lock im exclusive mode.

create function allocate_unique(p_lockname_in in varchar2) return varchar2 
is
 pragma autonomous_transaction;
 v_lockhandle varchar2(128);
begin
 dbms_lock.allocate_unique(
  upper(p_lockname_in),
  v_lockhandle,
  60*10
 );
 return v_lockhandle;
end;
/

create procedure request_lock(p_lockname_in in varchar2)
is
 v_lockhandle varchar2(128);
 v_return_code number;
begin
 v_lockhandle := allocate_unique(p_lockname_in);
 v_return_code := dbms_lock.request(
  lockhandle => v_lockhandle,
  lockmode => dbms_lock.x_mode,
  timeout => 10,
  release_on_commit => true
 );
 if pl_return not in (0,4) then
  raise_application_error
  (
   -20010,
   'Unable to get the requested lock ' || p_lockname_in || '.');
 end if;
end;
/

Eine Möglichkeit besteht nun darin, die Prozedur REQUEST_LOCK im AFTER STATEMENT Teil des Triggers aufzurufen; der Lock trägt den Namen CHECK_MANAGER.

...
 after statement is
  v_manager_count integer := 0;
 begin
  request_lock('CHECK_MANAGER');
  ...
 end after statement;
...

Damit ist das Problem zwar gelöst, jedoch kann das Locking noch granularer gestaltet werden. Dazu wird ein Lock pro Abteilung definiert, wie das folgende Listing zeigt.

...
 after statement is
  v_manager_count integer := 0;
 begin
  departments_tab := set( departments_tab );
  for i in 1 .. departments_tab.count loop
   request_lock( 'CHECK_MANAGER_DEPT_' ||  departments_tab(i) );
   ...
  end loop;
 end after statement;
...

Das ganze soll nun mithilfe von zwei Sessions getestet werden.

T1:

insert into emp (empno, ename, job, mgr, hiredate, sal, deptno)
values (7974, 'CLARKSON', 'MANAGER', 7839, sysdate, 3750, 40);
-- 1 rows inserted

T2:

insert into emp (empno, ename, job, mgr, hiredate, sal, deptno)
values (7975, 'JOHNSON', 'MANAGER', 7839, sysdate, 3500, 40);
-- WAIT (maximal 10 Sekunden, wie in der Prozedur REQUEST_LOCK angegeben)

T1:

commit; -- weniger als 10 Sekunden nach dem INSERT

T2:

--SQL-Fehler: ORA-20010: No more than one manager allowed in department 40.
--ORA-06512: in "EXAMPLE.CHECK_MANAGER_C_IU", Zeile 27
--ORA-04088: Fehler bei der Ausführung von Trigger 'EXAMPLE.CHECK_MANAGER_C_IU'

Das Beispiel und die Verwendung von DBMS_LOCK ist angelehnt an das Buch "Applied Mathematics for Database Professionals" von Lex de Haan und Toon Koppelaars. Dort wird das Ganze noch detaillierter und mit zahrleichen Beispielen samt der notwendigen Theorie behandelt.