PL/SQL Procedure: limit number of INSERTS

oracleplsql

I have a procedure that is supposed to take an employee from a certain table and insert it into another table that records who is in a leadership role for a certain period.

The thing is, there can only be a maximum of two employees in leadership roles for a given period.

The primary key in the table is the pair (employee,month) and there is no manual insert, so it's all done trough the procedure.

How do I structure it so that concurrent accesses can't register more than 2 employees in a leadership role for the same month?

I thought about getting a COUNT(*) on the number of rows for that month, but if an insert occurs after the count is registered, it would no longer be valid. Is there any way to control this right on the procedure or do I need to resort a trigger to check after each insert and rollback if the number exceeds 2?

I cannot change the structure of the table, just the procedure.

Best Answer

Ok, the trigger is trivial I think, for DBMS_LOCK, just to give you the idea:

create or replace procedure proc_insert(...) as
  l_count number;
  l_handle varchar2(128);
  l_return number;
begin
  dbms_lock.allocate_unique('leadership_role_insert_lock', l_handle);
  l_return := dbms_lock.request(l_handle);

  if l_return = 0 then
    -- lock successfully acquired
    select count(*) into l_count from leadership_role_table where ...;

    if l_count < 2 then
    -- inserting
      commit;
    else
      -- not inserting
      l_return := dbms_lock.release(l_handle);
      raise_application_error(-20001, 'your error goes here');
    end if;
  else
    -- could not acquire the lock
    raise_application_error(-20001, 'your error goes here');
  end if;

  -- this kind of lock needs an explicit release
  l_return := dbms_lock.release(l_handle);
  exception when others then
    rollback;
    l_return := dbms_lock.release(l_handle);
end;
/

With the above, concurrent calls of dbms_lock.request will be blocked until the previous session releases the lock.

Related Question