Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question

migrated from stackoverflow.com Feb 13 '12 at 14:13

This question came from our site for professional and enthusiast programmers.

    
What isolation level are you using? Also are you finding the violations are because of rows in Folders or Files? Do you have a constraint on Folders too? –  Martin Smith Oct 13 '11 at 15:19
    
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. –  ladenedge Oct 13 '11 at 15:28
    
There are additional issues around snapshot isolation as well. See Snapshot isolation: A threat for integrity? –  Martin Smith Oct 13 '11 at 15:29

3 Answers 3

up vote 11 down vote accepted

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

Edit: 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

share|improve this answer
    
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. –  ladenedge Oct 13 '11 at 15:18
    
Using an unique indexed view on the joined table might be a very good solution indeed, it solves both issues (properly enforced uniqueness via an unique index and unification of the File and Folders namespaces). –  Remus Rusanu Oct 13 '11 at 15:39
    
A unique constraint on a view will abort an update to one of the view's underlying tables? –  ladenedge Oct 13 '11 at 15:41
4  
Ah, the view does not enforce uniqueness of empty folders (no Files, therefore no join). An additional unique index on Folders would solve that problem. Theoretically the same is required for Files, but I assume that Files have a FK check to make sure they belong to a valid Folder. –  Remus Rusanu Oct 13 '11 at 15:43
    
@Remus Rusanu: yes, they do. Great comment. –  ladenedge Oct 13 '11 at 15:46

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.

share|improve this answer
    
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.) –  ladenedge Oct 13 '11 at 15:24

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.

share|improve this answer
    
Are these hints necessary in any constraint that uses a select? I assume there is a performance penalty to using them? –  ladenedge Oct 13 '11 at 15:20
3  
It will fail under any isolation level. –  Remus Rusanu Oct 13 '11 at 15:22
1  
It's still a RBAR UDF no matter how you try to fudge it. –  gbn Oct 13 '11 at 15:30
1  
@gbn - No disagreement there! –  Martin Smith Oct 13 '11 at 15:31
1  
My comment was that the OP code will fail under any isolation level (it may fail with deadlock). I would shun using hints, the potential for more problems (specially deadlocks) is going to be significant. The problem with locks hints is the behavior when the key being inserted does not exists yet. The solution requires range locks and it can turn ugly (read: deadlocks) quickly. –  Remus Rusanu Oct 13 '11 at 15:34

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.