Skip to content

Non-Overlapping Dates Constraint

August 30, 2012

If you have a table that represents time-varying info, e.g. with From and To date/time columns, you have a few options with regards to the problem of overlapping dates:

1. Check for overlapping dates in the application layer.

2. Use an off-the-shelf product to generate the appropriate triggers, e.g. Oracle CDM*RuleFrame or Toon Koppelaars’  RuleGen.

3. Roll your own, in the database.

4. Use a different data model that can use a unique constraint.

5. Forget about it.

One reason it’s difficult is that this is an example of a cross-row constraint, i.e. one that cannot merely be checked for the current row by itself. Oracle supports a few cross-row constraints, i.e. Primary Key, Unique and Foreign Key constraints; but it doesn’t natively support arbitrary assertions, which would allow us to easily declare this sort of constraint.

The real challenge comes from the fact that Oracle is a multi-user system and concurrent sessions cannot see the uncommitted data from other sessions; so some form of serialization will be required to ensure that when one session wishes to insert/update the data for a particular entity, no other session is allowed to start working on the same entity until the first session commits (or issues a rollback).

The problem is not new; it’s been around for a long time, and tripped many a new (and old) programmer.

There are two problems with option #1 (code in the application layer): firstly, you have to repeat the code for each different type of client (e.g. you might have a Java UI on one side, as well as some batch script somewhere else); secondly, usually the programmer involved will not understand the concurrency problem mentioned above and will not take it into account.

Option #2 is probably the best, most of the time. The solution is implemented at the database level, and is more likely to work correctly and efficiently.

Option #4 (change the data model) involves not storing the From and To dates, but instead dividing up all time ranges into discrete chunks, and each row represents a single chunk of time. This solution is valid if the desired booking ranges are at predictable discrete ranges, e.g. daily. You can then use an ordinary unique constraint to ensure that each chunk of time is only booked by one entity at any one time. This is the solution described here.

Option #5 (forget about it) is also a viable option, in my opinion. Basically it entails designing the rest of the application around the fact that overlapping date ranges might exist in the table – e.g. a report might simply merge the date ranges together prior to output.

Option #3, where you implement the triggers yourself on the database, has the same advantage as Option #2, where it doesn’t matter which application the data is coming from, the constraint will hold true. However, you have to be really careful because it’s much easier to get it wrong than it is to get right, due to concurrency.

I hear you scoffing, “Triggers?!?”. I won’t comment except to refer you to this opinion, which I couldn’t say it better myself: The fourth use-case for Triggers.

There is another Option #3 using a materialized view instead of triggers; I’ll describe this alternative at the end of this post.

So, here is a small example showing how an overlapping date constraint may be implemented. It is intentionally simple to illustrate the approach: it assumes that the From and To dates cannot be NULL, and its rule for detecting overlapping dates requires that the dates not overlap at all, to the nearest second.

1. Create the tables

CREATE TABLE room
  (room_no NUMBER NOT NULL
  ,CONSTRAINT room_pk PRIMARY KEY (room_no)
  );

CREATE TABLE room_booking
  (room_no NUMBER NOT NULL
  ,booked_from DATE NOT NULL
  ,booked_to DATE NOT NULL
  ,CONSTRAINT room_booking_pk
     PRIMARY KEY (room_no, booked_from)
  ,CONSTRAINT room_booking_fk
     FOREIGN KEY (room_no) REFERENCES room (room_no)
  );

2. Create the validation trigger (note – I’ve used an Oracle 11g compound trigger here, but it could easily be rewritten for earlier versions by using two triggers + a database package):

CREATE OR REPLACE TRIGGER room_booking_trg
  FOR INSERT OR UPDATE OF room_no, booked_from, booked_to
    ON room_booking
  COMPOUND TRIGGER

  TYPE room_no_array IS TABLE OF CHAR(1)
    INDEX BY BINARY_INTEGER;

  room_nos room_no_array;

PROCEDURE lock_room (room_no IN room.room_no%TYPE) IS
  dummy room.room_no%TYPE;
BEGIN
  SELECT r.room_no
  INTO dummy
  FROM room r
  WHERE r.room_no = lock_room.room_no
  FOR UPDATE;
END lock_room;

PROCEDURE validate_room (room_no IN room.room_no%TYPE) IS
  overlapping_booking EXCEPTION;
  dummy CHAR(1);
BEGIN
  -- check for overlapping date/time ranges
  BEGIN
    SELECT 'X' INTO dummy
    FROM room_booking rb1
        ,room_booking rb2
    WHERE rb1.room_no = validate_room.room_no
    AND rb2.room_no = validate_room.room_no
    AND rb1.booked_from != rb2.booked_from
    AND (
         rb1.booked_from BETWEEN rb2.booked_from
                             AND rb2.booked_to
         OR
         rb1.booked_to BETWEEN rb2.booked_from
                           AND rb2.booked_to
        )
    AND ROWNUM = 1;
    RAISE overlapping_booking;
  EXCEPTION
    WHEN NO_DATA_FOUND THEN
      -- good, no constraint violations
      NULL;
  END;
EXCEPTION
  WHEN overlapping_booking THEN
    RAISE_APPLICATION_ERROR(-20000,
      'Overlapping booking for room #' || room_no);
END validate_room;

PROCEDURE validate_rooms IS
  room_no room.room_no%TYPE;
BEGIN
  room_no := room_nos.FIRST;
  LOOP
    EXIT WHEN room_no IS NULL;
    validate_room (room_no);
    room_no := room_nos.NEXT(room_no);
  END LOOP;
  room_nos.DELETE;
EXCEPTION
  WHEN OTHERS THEN
    room_nos.DELETE;
    RAISE;
END validate_rooms;

BEFORE EACH ROW IS
BEGIN
  -- lock the header record (so other sessions
  -- can't modify the bookings for this room
  -- at the same time)
  lock_room(:NEW.room_no);
  -- remember the room_no to validate later
  room_nos(:NEW.room_no) := 'Y';
END BEFORE EACH ROW;

AFTER STATEMENT IS
BEGIN
  validate_rooms;
END AFTER STATEMENT;

END room_booking_trg;
/

That’s all you need. The trigger locks the header record for the room, so only one session can modify the bookings for a particular room at any one time. If you don’t have a table like “room” in your database that you can use for this purpose, you could use DBMS_LOCK instead (similarly to that proposed in the OTN forum discussion here).

It would not be difficult to adapt this example for alternative requirements, e.g. where the From and To dates may be NULL, or where the overlapping criteria should allow date/time ranges that coincide at their endpoints (e.g. so that the date ranges (1-Feb-2000 to 2-Feb-2000) and (2-Feb-2000 to 3-Feb-2000) would not be considered to overlap). You’d just need to modify the comparison in the query in validate_room to take these requirements into account.

Test case #1

INSERT INTO room (room_no) VALUES (101);
INSERT INTO room (room_no) VALUES (201);
INSERT INTO room (room_no) VALUES (301);

INSERT INTO room_booking (room_no, booked_from, booked_to)
VALUES (101, DATE '2000-01-01', DATE '2000-01-02' - 0.00001);

INSERT INTO room_booking (room_no, booked_from, booked_to)
VALUES (101, DATE '2000-01-02', DATE '2000-01-03' - 0.00001);

INSERT INTO room_booking (room_no, booked_from, booked_to)
VALUES (201, DATE '2000-02-01', DATE '2000-02-05' - 0.00001);

Expected: no errors

Test case #2

INSERT INTO room_booking (room_no, booked_from, booked_to)
VALUES (201, DATE '2000-02-01', DATE '2000-02-02' - 0.00001);

INSERT INTO room_booking (room_no, booked_from, booked_to)
VALUES (201, DATE '2000-02-02', DATE '2000-02-04' - 0.00001);

INSERT INTO room_booking (room_no, booked_from, booked_to)
VALUES (201, DATE '2000-02-03', DATE '2000-02-05' - 0.00001);

INSERT INTO room_booking (room_no, booked_from, booked_to)
VALUES (201, DATE '2000-02-03', DATE '2000-02-06' - 0.00001);

INSERT INTO room_booking (room_no, booked_from, booked_to)
VALUES (201, DATE '2000-01-31', DATE '2000-02-01');

INSERT INTO room_booking (room_no, booked_from, booked_to)
VALUES (201, DATE '2000-01-31', DATE '2000-02-02' - 0.00001);

INSERT INTO room_booking (room_no, booked_from, booked_to)
VALUES (201, DATE '2000-01-31', DATE '2000-02-06' - 0.00001);

INSERT INTO room_booking (room_no, booked_from, booked_to)
VALUES (201, DATE '2000-02-05' - 0.00001, DATE '2000-02-06' - 0.00001);

UPDATE room_booking SET booked_to = '2000-01-02' - 0.00001
WHERE room_no = 101 AND booked_from = DATE '2000-01-02';

Expected: constraint violation on each statement

Test case #3

in session #1:

INSERT INTO room_booking (room_no, booked_from, booked_to)
VALUES (301, DATE '2000-01-01', DATE '2000-02-01' - 0.00001);

in session #2:

INSERT INTO room_booking (room_no, booked_from, booked_to)
VALUES (301, DATE '2000-01-15', DATE '2000-01-16' - 0.00001);

Expected: session #2 will wait until session #1 issues a COMMIT or ROLLBACK. If session #1 COMMITs, session #2 will then report a constraint violation. If session #2 rolls back, session #2 will complete without error.

The No-Trigger option #3: Materialized View

This is similar to a solution proposed by Rob Van Wijk. It uses a constraint on a materialized view to stop overlapping date ranges.

So, instead of the trigger, you would do something like this:

CREATE MATERIALIZED VIEW LOG ON room_booking WITH ROWID;

CREATE MATERIALIZED VIEW room_booking_date_ranges
  REFRESH FORCE ON COMMIT
  AS SELECT 'X' AS dummy
     FROM room_booking rb1
         ,room_booking rb2
     WHERE rb1.room_no = rb2.room_no
     AND rb1.booked_from != rb2.booked_from
     AND (
          rb1.booked_from BETWEEN rb2.booked_from
                              AND rb2.booked_to
          OR
          rb1.booked_to BETWEEN rb2.booked_from
                            AND rb2.booked_to
         );

ALTER TABLE room_booking_date_ranges
  ADD CONSTRAINT no_overlapping_dates_ck
  CHECK ( dummy = 'Z' );

The nice thing about this solution is that it is simpler to code, and seems more “declarative” in nature. Also, you don’t have to worry about concurrency at all.

The constraint is checked at COMMIT-time when the materialized view is refreshed; so it behaves like a deferred constraint, which may be an advantage for some scenarios.

I believe it may perform better than the trigger-based option when large volumes of data are inserted or updated; however it may perform worse than the trigger-based option when you have lots of small transactions. This is because, unfortunately, the query here cannot be a “REFRESH FAST ON COMMIT” (if you know how this could be changed into a REFRESH FAST MV, please let me know!).

 

What do you think? If you see any potential issues with the above solutions please feel free to comment.

EDIT 30/8: added some more test cases

About these ads

From → Oracle, SQL

5 Comments
  1. Narendra permalink

    Jeff,

    Looks like you missed Rob Van Wijk’s subsequent related post… :)

    http://rwijk.blogspot.com.au/2010/01/enq-ji-contention.html

    Basically, he explains another disadvantage of the MV approach, which is overaggressive locking.

    • Hi Narendra,

      Thanks for pointing that out!

      Makes sense, really – with this sort of concurrency problem there has to be a serialization point somewhere – with the trigger approach, serialization occurs at the time of insert/update; with the materialized view approach, serialization occurs at commit time.

      So the benefits of the MV is that it can allow a “deferred”-style constraint, and is probably more efficient if the table is inserted/updated in bulk (e.g. by batch processes) rather than by lots of concurrent sessions running small transactions.

      And the benefits of the trigger approach is that it is as granular in its serialization as you can make it (depending on what you lock), and so is probably more efficient in an environment with many concurrent sessions running small transactions; but the constraint is always enforced at the statement level, which means they cannot be deferred.

  2. Jeff,
    Here you have solution to use REFRESH FAST ON COMMIT :

    CREATE MATERIALIZED VIEW LOG ON room_booking
    WITH SEQUENCE, ROWID (room_no,booked_from,booked_to)
    INCLUDING NEW VALUES;

    CREATE MATERIALIZED VIEW room_booking_date_ranges
    REFRESH FAST ON COMMIT
    AS
    SELECT RB1.ROOM_NO, COUNT(*) AS dummy
    FROM room_booking rb1
    ,room_booking rb2
    WHERE rb1.room_no = rb2.room_no
    AND rb1.booked_from != rb2.booked_from
    AND (
    rb1.booked_from BETWEEN rb2.booked_from
    AND rb2.booked_to
    OR
    rb1.booked_to BETWEEN rb2.booked_from
    AND rb2.booked_to
    )
    GROUP BY rb1.room_no;

    ALTER TABLE room_booking_date_ranges
    ADD CONSTRAINT no_overlapping_dates_ck
    CHECK ( dummy = 0 );

  3. Lisa Tansey permalink

    Looks pretty slick. I am going to give it a whirl. I don’t understand why you need to create the new type and the room_nos array for just a single row trigger?

    • It’s not just a single row trigger. Note the “after statement” bit – it needs the array in case you insert/update multiple rows in a single statement.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

Join 200 other followers

%d bloggers like this: