Tuesday, October 8, 2013

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 sequencebecause 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... 

3 comments:

  1. Hi,

    I'm very interested in this article, but there is one thing I don't understand clearly even after I regenerated the
    error using the samples SQL.

    As I understand, a deadlock occurs when two or more different sessions get a share lock on the same resource and hold the lock until each sessions tries to get an exclusive lock.

    In this sample case, I think the two sessions received the same value for the IDENTITY column and, as a result, the sessions tried to update the same row.

    What I'm not clear about is on which object the sessions get shared lock and why it was hold until it was updated though the isolation level was READ COMMITED (at least when I tried the sample).

    I would really appreciate your clarification.

    ReplyDelete
  2. Hi Thank you very much for your comment,
    As I understand your comment since i have used transaction inside the loop we have very less update lock time.

    ReplyDelete