Azure Sql Server fails to keep data consistency using a check constraint and a trigger

Question

I'm trying to keep data consistency in my Azure Sql Server database and implemented two scenarios:

1. check constraint
2. insert/update trigger

None of them work and I still able to reproduce a situation when my check is bypassed. The rule is simple - there couldn't be more than one active assignment for a user.

Tasks:
 - Id
 - UserId
 - Status ('Active', 'Done')

User
 - Id
 - ...

Approach #1 - Check Constraints

I've implemented a function to ensure data consistency and applied it as a check constraint

create function [dbo].[fnCheckUserConcurrentAssignment]
(
    @id nvarchar(128),
    @status nvarchar(50), -- not used but required to check constraint
)
returns bit
as
begin

    declare @result bit
    select @result = cast(case when (select count(t.Id) 
                                     from dbo.Tasks t
                                     where t.[UserId] = @id
                                         and t.[Status != 'Done') > 1
                               then 1 
                               else 0 
                          end as bit)

    return @result
end

alter table dbo.Tasks
    with check add constraint [CK_CheckUserConcurrentAssignment]
    check (dbo.fnCheckUserConcurrentAssignment([UserId], [Status]) = 0)

Approach #2 - Trigger

alter trigger dbo.[TR_CheckUserConcurrentAssignment]
on dbo.Tasks
for insert, update
as begin

    if(exists(select conflictId from
                      (select (select top 1 t.Id from dbo.Tasks t 
                               where t.UserId = i.UserId 
                                and o.[Status] != N'Done' 
                                and o.Id != i.Id) as conflictId 
                       from inserted i
                       where i.UserId is not null) conflicts
              where conflictId is not null))
    begin  
        raiserror ('Concurrent user assignment detected.', 16, 1);  
        rollback;
    end

end

If I create lots of assignments in parallel (regularly > 10) then some of them will be rejected by the constraint/trigger, other will be able to concurrently save UserId in the database. As a result by database data will be inconsistent.

I've verified both approaches in Management Studio and it prevents me from corrupting my data. I'm unable to assign multiple 'Active' tasks to a given user.

What is improtant to say, I'm using Entity Framework 6.x at my Backend to save my data (SaveChangesAsync) and every save action is executed in a separate transaction with default Transaction Isolation level ReadCommited

What could be wrong in my approaches and how to keep my data consistent?

Answer 1

It is a classic race condition.

select @result = cast(case when (select count(t.Id) 
                                     from dbo.Tasks t
                                     where t.[UserId] = @id
                                         and t.[Status != 'Done') > 1
                               then 1 
                               else 0 
                          end as bit)

If two threads run the fnCheckUserConcurrentAssignment function at the same time, they will get the same count from Tasks. Then each thread will proceed with inserting the row and the final state of the database would violate your constraint.

If you want to keep your approach with the function in the CHECK constraint, or the trigger, you should make sure that your transaction isolation level is set to SERIALIZABLE. Or use query hints to lock the table. Or use sp_getapplock to serialise calls to your function/trigger.

---

In your case, the check is pretty simple, so it can be implemented without a trigger or function. I'd use a filtered unique index:

CREATE UNIQUE NONCLUSTERED INDEX [IX_UserID] ON [dbo].[Tasks]
(
    [UserID] ASC
) 
WHERE (Status = 'Active')

This unique index would guarantee that there is no two rows with Status = 'Active' which have the same UserID.

---

There is a similar question on dba.se How are my SQL Server constraints being bypassed? with more detailed explanations. They mention another possible solution - indexed views, which boils down again to unique index.