Oracle blocking repeated inserts

Question

Consider a system that has multiple requests for course enrollments coming in. We need a way to block duplicate enrollments in the system. I created a trigger as follows, but when I get two requests from different connections at the same time (ms apart) they are both inserted. What am I doing wrong

create trigger enrollment_duplicates
before insert
on enrollment
for each row
begin
    select count(*) 
      into cnt 
      from enrollment 
     where user = :new.user 
       and course = :new.course 
       and status = 'Enrolled';
    if cnt > 0 then
        raise_application_error(-20001, 'User already enrolled in course');
    end if;
end;

EDIT:

This is easy enough if we make user/course a unique constraint, but it's not. They can re-enroll depending on the status.

Answer 1

You need a unique index. If you are saying that there can be only one Enrolled row but many rows with other statuses, you can create a function-based index

CREATE UNIQUE INDEX idx_stop_multiple_enrolls
    ON enrollment( (case when status = 'Enrolled' 
                         then user 
                         else null 
                      end),
                   (case when status = 'Enrolled' 
                         then course 
                         else null 
                      end) );

This takes advantage of the fact that Oracle does not include values in the index when all the columns are NULL so the index only has entries for rows where the status is Enrolled.

Note that USER is a reserved word (there is a built-in function USER) so I'm assuming that your actual column is named something different.

Answer 2

I hesitate to add an answer to this, esp as Justin has already answered with an approach that meets your specific question. But I suspect that you probably have other business logic spread around (in triggers or on middleware/app side), so the following may help you or someone else.

One possible approach is to use Transactional APIs (xapis). Note that this is not the same as Table APIs (tapis), where even select access is hidden in pl/sql layers. Xapis would encapsulate just the transactional (ins/upd/del) requirements of the system, and end users would call a procedure to do something like "enroll a student", for example. See this Ask Tom article for more on Xapi approach.

How much business logic is used in the xapi would depend on a lot of factors, but I'd keep it simple. For your specific question (to serialize inserts to the enrollment table), you can do this fairly easily in pl/sql, something like:

create table enrollment
(
  id number,
  username varchar2(50),
  course varchar2(50),
  status varchar2(50),
  created_date date default sysdate not null
);

create index enrollment_idx
on enrollment(username, course)
logging
noparallel;

create or replace package enroll_pkg as
  err_already_enrolled      constant number := -20101;
  err_already_enrolled_msg  constant varchar2(50) := 'User is already enrolled';
  err_lock_request          constant number := -20102;
  err_lock_request_msg      constant varchar2(50) := 'Unable to obtain lock';
  enroll_lock_id            constant number := 42;

  function is_enrolled(i_username varchar2, i_course varchar2) return number;
  procedure enroll_user(i_username varchar2, i_course varchar2);

end;
/

create or replace package body enroll_pkg as

  -- returns 1=true, 0=false
  function is_enrolled(i_username varchar2, i_course varchar2) return number is
    l_cnt number := 0;
  begin

    -- run test if user is enrolled in this course
    select decode(count(1),0,0,1)
    into l_cnt
    from enrollment
    where username=i_username
    and course=i_course
    and status = 'ENROLLED';

    -- testing locks here
    --dbms_lock.sleep(5);

    return l_cnt;
  end;

  procedure enroll_user(i_username varchar2, i_course varchar2)
  is
    l_lock_result number;
    l_username enrollment.username%type;
    l_course enrollment.course%type;
  begin

    -- try to get lock (serialize access)  
    l_lock_result := dbms_lock.request(enroll_lock_id, dbms_lock.x_mode, 10, true);
    if (l_lock_result <> 0) then
      raise_application_error(err_lock_request,err_lock_request_msg || ' (' || l_lock_result || ')');
    end if;

    -- simple business rule: uppercase names & course
    l_username := upper(trim(i_username));
    l_course := upper(trim(i_course));

    if (is_enrolled(l_username, l_course) > 0) then
      raise_application_error(err_already_enrolled,err_already_enrolled_msg);
    end if;

    -- do other business logic checks, update other tables, logging, etc...

    -- add enrollment
    insert into enrollment(id,username,course,status) values
    (enroll_seq.nextval,l_username,l_course,'ENROLLED');

    commit;

    -- release lock
    l_lock_result := dbms_lock.release(enroll_lock_id);

  end;

end;
/

And to enroll a user, you would call:

exec enroll_pkg.enroll_user('Joe Smith','Biology');

If you went this route, you would typically remove insert/update/delete privs from users directly, and grant them execute on the xapi instead. Also note that I only briefly tested the above code, but it should illustrate the approach.