0

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.

3
  • An after insert trigger would result in the infamous ORA-04091 error. Could work with a before insert trigger. Another way: use DBMS_LOCK to serialize the inserts. You can do this in your procedure, but this protects inserts coming from this procedure only. Another way: aggregate materialized view with a check constraint on the COUNT column, an overkill for this task. I would just go with the before insert trigger I guess. But think about updates too. Dec 11, 2016 at 21:35
  • When you say updates, do you mean the SELECT FOR UPDATE OF? How would that work in this context? Doesn't that just lock the rows that get selected? Dec 11, 2016 at 23:32
  • No, I mean you should apply the same precautions for updates as well, unless the rows are never updated in that table. Dec 12, 2016 at 9:44

1 Answer 1

1

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.