11

We have found a handful of rows in our DB that violate an active constraint. How is this possible?

The constraint is active, as we can't just manually add a row that bypasses this constraint. However, when we run CHECKCONSTRAINTS(Files), we find that it has been bypassed on a handful of occasions during our test runs. The rows in question were all created within a half-second of one another, suggesting some kind of race condition.

Here's the constraint being applied to the table. The rule is meant to ensure name uniqueness in a given parent folder:

ALTER TABLE Files ADD CONSTRAINT UniqueNameInParentFolder CHECK
    CheckUniqueNameInFolder(ParentFoldersID, Name) = 1;

This constraint calls a function that looks like this:

-- first check for the new name in the Folders table
IF ((SELECT COUNT(*) FROM Folders 
     WHERE ParentFoldersID = @FoldersID AND Name = @Name) = 0)
BEGIN 
    -- then check for it in the Files table
    IF ((SELECT COUNT(*) FROM Files 
         WHERE ParentFoldersID = @FoldersID AND Name = @Name) <= 1)
        RETURN 1
END
RETURN 0

Individual rows are added inside transactions, so I'm having a hard time understanding how duplicate rows are sneaking past this constraint.

Improve this question
1
  • Yes, we have an analogous constraint on Folders are are seeing a similar number of violations there as well. We are currently using READ_COMMITTED_SNAPSHOT. Commented Oct 13, 2011 at 15:28

3 Answers 3

Reset to default
24

Check constraints based on UDFs are rubbish. Concurrency, RBAR, isolation etc as you've found out. Some links:

The safest way for SQL Server in this case would be to use standard constraints such as unique and foreign keys. I can't see why you check the folders table for a constraint on the files table though.

To prevent a file and a folder having the same name in a given parent folder only, use an indexed view. Duplicate files or duplicate folders requires table level uniqueness.

CREATE VIEW CheckUnique
WITH SCHEMABINDING
AS
SELECT fo.ParentFoldersID, fo.Name
FROM
   Folders fo
   JOIN
   File fi ON fo.ParentFoldersID  = fi.ParentFoldersID AND fo.Name = fi.Name
GO
CREATE UNIQUE CLUSTERED INDEX IXCU_CheckUnique ON CheckUnique (ParentFoldersID, Name)
GO

Or a trigger.

But never a UDF in a check constraint

Improve this answer
1
  • In our case, folders and files of the same name may not exist in the same parent folder, so unfortunately I don't think unique/fk constraints are sufficient. Commented Oct 13, 2011 at 15:18
15

That CheckUniqueNameInFolder function is hardly checking anything. Plenty of duplicates can be added under that constraint check. It has two distinct SELECTS that are run sequentially (therefore the condition checked by the first select can be invalidated by the time the second one runs) and, in any case, the constraint tells, at best, that there was no duplicate when the check occurred, in no way does it tell that there is no duplicate when the insert/update occur. Since the checks do not lock the keys being validated in U or X mode, multiple inserts can occur concurrently, do the check, find no duplicate and all proceed to insert the same entry.

The only way to properly enforce a unique constraint is using an unique constraint.

Create a computed column with the full path of your 'files' and enforce uniqueness on the entire path with a UNIQUE constraint, or perhaps use an UNIQUE constraint on (ParentFolderID, Name). Don't store Folders and Files separately, use a common table for both Folders and Files (eg. Entries) since they occupy the same namespace.

Improve this answer
1
  • 1
    Yes, for some reason we assumed the transaction would handle the problems we're seeing, but you're right that there is no locking. (Merging the tables is a pretty big redesign for us at this point, but an interesting idea nonetheless.) Commented Oct 13, 2011 at 15:24
6

Running this inside a transaction at default read committed level will fail under load.

The reads aren't mutually exclusive and serialized so two concurrent transactions can both read that the row does not exist. You could add UPDLOCK,ROWLOCK,HOLDLOCK hints to the SELECT.

Improve this answer
0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.