T-SQL Unique constraint locked the SQL server

Question

HI ! This is my table:

CREATE TABLE [ORG].[MyTable](   
    ..
    [my_column2] UNIQUEIDENTIFIER NOT NULL  CONSTRAINT FK_C1 REFERENCES ORG.MyTable2 (my_column2),  
    [my_column3] INT NOT NULL CONSTRAINT FK_C2 REFERENCES ORG.MyTable3 (my_column3)
    ..
    )

I've written this constraint to assure that combination my_column2 and my_column3 is always unique.

ALTER TABLE [ORG].[MyTable] ADD
 CONSTRAINT UQ_MyConstraint UNIQUE NONCLUSTERED
 (
    my_column2,
    my_column3
 )

But then suddenly.. The DB stopped responding.. there is a lock or something.. Do you have any idea why? What is bad with the constraint?

What I've already checked is that I have some locked objects when I select * from master.dbo.syslockinfo (joined with sysprocesses). After 10 minutes of inactivity.. this list is empty again and I can access all the objects in database. Weird..

Answer 1

It has to lock the table while checking the data to see if it violates the constraint or not, otherwise some bad data might get inserted while it was doing this

Some operations like rebuilding an index (Of course not using the ONLINE in Enterprise Edition) also will make the table inaccessible while it does this