CODEWITHSUNDEEP
sql-server-2012
mulyG
mulyG

Reputation: 13

SQL Server - Check Constraint "not working"

I have a question about an issue with SQL Server CHECK constraint "not working":

We're using a SQL Server 2012 Enterprise instance. We have a table that describes events on an entity (where entities are a different table with FK) and a time-frame (start time, end time).

We do not allow two events on the same entity with partially overlapping time-frame, and for that purpose we added a check constraint in the DB that should fail on such cases.

This is the check constraint function:

CREATE FUNCTION [dbo].[DoesConflictingEventExist]
(
  @existing_event_id int,
  @entity_id bigint,
  @start_time datetime,
  @end_time datetime 
)
RETURNS bit
AS
BEGIN
    return case 
        when exists (select * from Events er1
                     where 
                          er1.EntityId = @entity_id
                          and not (er1.EndTime <= @start_time 
                                   or @end_time <= er1.StartTime)
                          and er1.Id <> @existing_event_id) then 1
        else 0
    end
END

Occasionally, maybe when a lot of concurrent load is on the DB, somehow event records with the same entityId and overlapping times do manage to get added to the DB and no error is thrown.

When we run the query inside the above function, it does find those duplicate events...

Here is an example of duplicate events that were added to the DB:

Event1(id 2691604): 
entityID 8095119352335255831, starttime 2015-07-05 15:02:43.000 endtime 2016-06-30 13:28:41.000
Event2(id 2691605):
entityID 8095119352335255831, starttime 2015-07-05 15:03:19.000 endtime 2016-06-30 13:28:41.000

We are thinking of switching from check constraint to an "instead of insert" trigger, because check constraints are checked after the fact while the records are already in DB, but still - as we got no error from the constraint, we are not sure if we won't see the same problem with triggers (if it's a concurrency / isolation problem, it won't go away probably).

Any clues?

Upvotes: 1

Views: 546

Answers (1)

usr
usr

Reputation: 171178

Imagine two transactions doing the same concurrently:

  1. Insert conflicting row
  2. Run check constraint
  3. Commit

Step 2 does not see the uncommitted row of the other transaction.

Raise the isolation level of the check constraint function to SERIALIZABLE and make sure that the query plan touches few rows so that little data is locked.

Upvotes: 1

Related Questions