SQL Server – Unique Constraint with Non-Unique Data

sql serversql-server-2008-r2unique-constraint

I've inherited this database which has a table defined as such :

CREATE TABLE home_tickets(
    id int IDENTITY(1,1) NOT NULL,
    identifier nvarchar(45) NOT NULL,
    start_date_time datetime NOT NULL,
    revised bit NOT NULL,
    agency_code nvarchar(100) NOT NULL,
    archived tinyint NOT NULL,

    CONSTRAINT PK__home_tic__3213E83F236943A5 PRIMARY KEY CLUSTERED (id ASC) WITH (
        PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON PRIMARY,

    CONSTRAINT UQ__home_tic__969168722645B050 UNIQUE NONCLUSTERED (identifier ASC) WITH (
        PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON PRIMARY
) ON PRIMARY

However when I select from the table given an identifier I am getting multiple rows returned.

select * from home_tickets where identifier = 'TI54815780';


id      identifier  start_date_time         revised archived
415044  TI54815780  2013-10-24 00:00:00.000 0       0
652845  TI54815780  2014-10-24 00:00:00.000 0       0
171642  TI54815780  2012-10-24 00:00:00.000 0       0
945275  TI54815780  2015-10-24 00:00:00.000 1       0
1220327 TI54815780  2016-10-24 00:00:00.000 1       0

Why would this happen with a unique constraint on identifier ?

Best Answer

It seems the underlying constraint index is disabled. You can verify this with:

SELECT is_disabled
FROM sys.indexes
WHERE
    object_id = OBJECT_ID(N'home_tickets')
    AND name = N'UQ__home_tic__969168722645B050';

After the bad values are remediated, rebuild the index to enable the constraint:

ALTER INDEX UQ__home_tic__969168722645B050 ON home_tickets REBUILD;

Related Solutions

Sql-server – SELECT/INSERT Deadlock

On the face of it, this looks like a classic lookup deadlock. The essential ingredients for this deadlock pattern are:

a SELECT query that uses a non-covering nonclustered index with a Key Lookup
an INSERT query that modifies the clustered index and then the nonclustered index

The SELECT accesses the nonclustered index first, then the clustered index. The INSERT access the clustered index first, then the nonclustered index. Accessing the same resources in a different order acquiring incompatible locks is a great way to 'achieve' a deadlock of course.

In this case, the SELECT query is:

SELECT query

...and the INSERT query is:

INSERT query

Notice the green highlighted non-clustered indexes maintenance.

We would need to see the serial version of the SELECT plan in case it is very different from the parallel version, but as Jonathan Kehayias notes in his guide to Handling Deadlocks, this particular deadlock pattern is very sensitive to timing and internal query execution implementation details. This type of deadlock often comes and goes without an obvious external reason.

Given access to the system concerned, and suitable permissions, I am certain we could eventually work out exactly why the deadlock occurs with the parallel plan but not the serial (assuming the same general shape). Potential lines of enquiry include checking for optimized nested loops and/or prefetching - both of which can internally escalate the isolation level to REPEATABLE READ for the duration of the statement. It is also possible that some feature of parallel index seek range assignment contributes to the issue. If the serial plan becomes available, I might spend some time looking into the details further, as it is potentially interesting.

The usual solution for this type of deadlocking is to make the index covering, though the number of columns in this case might make that impractical (and besides, we are not supposed to mess with such things on SharePoint, I am told). Ultimately, the recommendation for serial-only plans when using SharePoint is there for a reason (though not necessarily a good one, when it comes right down to it). If the change in cost threshold for parallelism fixes the issue for the moment, this is good. Longer term, I would probably look to separate the workloads, perhaps using Resource Governor so that SharePoint internal queries get the desired MAXDOP 1 behaviour and the other application is able to use parallelism.

The question of exchanges appearing in the deadlock trace seems a red herring to me; simply a consequence of the independent threads owning resources which technically must appear in the tree. I cannot see anything to suggest that the exchanges themselves are contributing directly to the deadlocking issue.

Sql-server – Unexpected Unique Constraint Violation

Why not just collate that column as case-sensitive? I don't think you need to resort to binary. This seems to work fine, just note that it may affect sorting, comparisons, unions etc:

CREATE TABLE [dbo].[Unit]
(
    [id]                SMALLINT       NOT NULL IDENTITY(1,1),
    [name]              VARCHAR(100)   COLLATE Latin1_General_CS_AS NOT NULL ,

    CONSTRAINT [PK_Unit] PRIMARY KEY CLUSTERED ([id]),
    CONSTRAINT [UQ_Unit_Name] UNIQUE ([name])
);
GO

INSERT INTO dbo.Unit (Name) VALUES('e³m³/day') 
INSERT INTO dbo.Unit (Name) VALUES('e3m3/day')

Results:

1 row(s) affected.
1 row(s) affected.

Now, this works for the example in your question, but whether it works for your entire set of requirements, is hard to say. You may have characters that are different to you but aren't treated differently in terms of case-sensitivity, such as:

INSERT INTO dbo.Unit (Name) VALUES('ß')
INSERT INTO dbo.Unit (Name) VALUES('ss')

Results:

1 row(s) affected.

And then:

Msg 2627, Level 14, State 1, Line 2
Violation of UNIQUE KEY constraint 'UQ_Unit_Name'. Cannot insert duplicate key in object 'dbo.Unit'. The duplicate key value is (ss).
The statement has been terminated.

In that case you may need to use binary after all.

Best Answer

Related Solutions

Sql-server – SELECT/INSERT Deadlock

Sql-server – Unexpected Unique Constraint Violation

Related Question