Deadlocked on SQL Server

Deadlocked on SQL Server

                                                                                              This week we have face Deadlocked issue on our SQL Server database environment.This is very interesting scenario, But before start topic I want to share Microsoft Tech net  document with you all,      

I have attached print screen of above MS Tech-net document. This is what Microsoft says about IDENTITY columns. Personally I was very interested about the way Microsoft handle auto increment number in SQL Server table compare to Oracle table (Oracle sequence) because its(SQL Server)  very easy. Remember easy way always doubtful :).

Well, Microsoft saying they can not guarantee above condition on IDENTITY column. Let me explain why they says like that,

First you need to create following table,

USE [tempdb]
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[tblIDENTITY]') AND type in (N'U'))
    DROP TABLE [dbo].[tblIDENTITY];
GO

IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[tblIDENTITY]') AND type in (N'U'))

BEGIN

    CREATE TABLE [dbo].[tblIDENTITY](
        [primaryKey] [int] IDENTITY(1,1) NOT NULL,
        [Comments] [varchar](100) NULL
    ) ON [PRIMARY]

END

GO

After create above table you need to execute following SQL statement with multiple sessions(different SPID's(Ex: 3 sessions or more)),

DECLARE @status int=1;
DECLARE @primaryKey TABLE([primaryKey] int);

WHILE  @status>0 BEGIN
    BEGIN TRAN;

        INSERT INTO tblIDENTITY(Comments) OUTPUT INSERTED.primaryKey INTO @primaryKey VALUES(NULL);
       
        UPDATE tblIDENTITY SET Comments=GETDATE() WHERE ([primaryKey] IN(SELECT [primaryKey] FROM @primaryKey));

        DELETE FROM @primaryKey;

    COMMIT TRAN;

    SELECT @status=@status+1;

    IF @status=10000 BEGIN
        SELECT @status=0;
    END

END

GO

   Hope you will face following Deadlocked error on two or many sessions,

Msg 1205, Level 13, State 45, Line 16
Transaction (Process ID 55) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.

 Ok... now you will realize why Microsoft purposely added above not guarantee conditions on IDENTITY column. Personally I'm not happy the way Microsoft treated IDENTITY column. 
 And if you face this problem then you need to make-sure IDENTITY column is UNIQUE( PRIMARY KEY or UNIQUE constraint or UNIQUE index).    

Cheers...