Double Booking Prevention

Question

Let's say if I have the following:

create table jobs ( staff_id number, job_name varchar2(1000) not null, start_date date not null, end_date date not null ); 

This is just a table listing the various staff tasks.

Now employees can only do one task at a time, so I am inserting tasks using the following PL / SQL statement. If the found task is found, adding a task is not required (maybe I should report an error here, but for this simplified example, I ignore this):

 create or replace procedure add_job ( p_staff_id number, p_job_name varchar2, p_start_date date, p_end_date date ) as begin insert into jobs ( select p_staff_id, p_job_name, p_start_date, p_end_date from dual where not exists ( select 1 from jobs where staff_id = p_staff_id and (end_date > p_start_date) and (start_date < p_end_date) ) ); end; 

The problem is that if I add two different tasks to two different sessions and then complete, I can double orders. in the following way:

 -- Session 1: add_job(1, 'Help Alice', to_date('2011/08/21 11:00:00', 'YYYY/MM/DD HH24/MI/SS'), to_date('2011/08/21 13:00:00', 'YYYY/MM/DD HH24/MI/SS')); -- Session 2: add_job(1, 'Help Bob', to_date('2011/08/21 12:00:00', 'YYYY/MM/DD HH24/MI/SS'), to_date('2011/08/21 14:00:00', 'YYYY/MM/DD HH24/MI/SS')); -- Session 1: commit; -- Session 2: commit; 

Above, staff_id 1 will be booked from 12:00 to 13:00.

It seems like adding to the top of my procedure:

 lock table jobs in exclusive mode; 

did the trick, I feel like it's a castle too wide. Is there a way to force the oracle to do something more granular. I would prefer not to spoof dbms_lock if possible. This page , alluding to select ... for update , would do the trick, but it gave no details.

Is there a way to stop double reservations without completely locking the table or blocking dbms_lock ? (I use Oracle 10g if that matters).

Answer 1

You obviously need a lock to prevent double bookings. Since you need to lock a specific employee, my advice is that you lock the line in the frame before the insert statement:

 select id from staff where id = p_staff_id for update; 

Thus, a lock only affects one employee (assuming you have row-level locks).

Answer 2

You can use the trigger database to ensure the regularity of regular jobs.

If a matching task for an individual employee is found, this will not allow you to paste, and therefore you will not be able to get overlapping tasks.

In the case when your question is asked, no matter what the task is, the second will fail due to a trigger that checks the inserts.

By the way, locking an ENTIRE table would not be a good solution. In addition, trying to use SELECT FOR UPDATE will not interfere with the two inserts.

Answer 3

In any case, you might consider using materialized views with control constraints. See this old post on my blog for an example of "1". The rule. An employee cannot have overlapping project assignments "very similar to yours."

Caution: maintaining materialized views has performance implications, so do not execute them without proper performance testing.

Answer 4

While tasks are distributed in rounded rather large blocks of time, 1 hour or 30 minutes (1/24/60 * 30)

You can create a new reserved_time_slot table with the primay key on staff_id and time (or date and number number)

If you insert a row for each time interval used: (the example below gets every 30-minute interval between the start and end time and saves it)

 INSERT INTO booked_time_slot SELECT p_staff_id,TO_DATE('2011/08/21 11:00:00', 'YYYY/MM/DD HH24/MI/SS') + ((ROWNUM-1)*(1/24/60*30)) FROM DUAL CONNECT BY TO_DATE('2011/08/21 11:00:00', 'YYYY/MM/DD HH24/MI/SS') + ((ROWNUM-1)*(1/24/60*30)) < TO_DATE('2011/08/21 12:00:00', 'YYYY/MM/DD HH24/MI/SS') 

The primary key stops any duplicates.

Answer 5

if staff_id must be unique, you must generate it sequentially.

and when you insert, you just do it like this:

 insert into jobs ( select seq_staff_id.nextval, p_job_name, p_start_date, p_end_date from dual )