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]

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

IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[tblIDENTITY]') AND type in (N'U'))
        [primaryKey] [int] IDENTITY(1,1) NOT NULL,
        [Comments] [varchar](100) NULL
    ) ON [PRIMARY]

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

        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;


    SELECT @status=@status+1;

    IF @status=10000 BEGIN
        SELECT @status=0;
   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).    



  1. yo DatabaseUserGroup, I have found nice script which is publish by Paul Randal and Joe in sqlskills.com,
    -- Method #4, using the system health session (available in SQL Server 2008 +)
    -- Query is compliments of Jonathan Kehayias and Paul Randal on the below query
    -- (Session contains deadlock event info, locks exceeding a set period, latch waits and more)
    XEventData.XEvent.value ('(data/value)[1]', 'varchar(max)'),
    '', ''),
    '', '')
    AS XML) AS DeadlockGraph
    FROM (SELECT CAST (target_data AS XML) AS TargetData
    FROM sys.dm_xe_session_targets st
    JOIN sys.dm_xe_sessions s ON s.address = st.event_session_address
    WHERE [name] = 'system_health') AS Data
    CROSS APPLY TargetData.nodes ('//RingBufferTarget/event') AS XEventData (XEvent)
    WHERE XEventData.XEvent.value('@name', 'varchar(4000)') = 'xml_deadlock_report';
    you can download code from,

    and nice youtube video,

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

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

  4. DrVoIP is a full service VoIP and ShoreTel IP PBX installation, support and training telephony company. In addition, we offer many free services to support VoIP community to enable business people to make vital decisions when it comes to the installation and support of VoIP telephone systems.create mysql dashboard

  5. Said to be one of the toughest and hardest security audits on the database, the security of the data is high when this is applied.dbdesigner.net

  6. I quite like reading an article that can make people think. Also, thanks for allowing for me to comment! internet providers south africa

  7. You realize that database that your organization depends on? Actually no, not unreasonably one, the other one that is extremely significant? That's right,visit now that one - it's leaving, would you say you are prepared?

  8. Numerous unmistakable data recovery offices have a huge cluster of equipment, programming and instruments for recouping data.
    electronic discovery

  9. Your work here on this blog has been top notch from day 1. You've been continously providing amazing articles for us all to read and I just hope that you keep it going on in the future as well. Cheers! https://serverbrowse.com/

  10. This obviously relies upon the measure of solicitations and traffic that your site will get.Enterprise Class Dedicated Servers

  11. As you know there can be millions of ways to apply this radiance, you should be aware that a polished look also gives remarkable details to your entire outfit. bulk craft glitter

  12. SQL infusion is a procedure that takes advantage of a security weakness happening in the information base layer of a web application. https://onohosting.com/

  13. This article will treat the two jobs conversely; the prescribed procedures depicted here are appropriate to the two jobs. The possibly time an alternate methodology is required is the point at which you play the two parts on the undertaking in which case you will be expected to recognize the two jobs and allocate every job the work they are the most appropriate for.

  14. Gaming is one of the productive activities that ensure the personal developments of individuals for them to impact favorably on the development of their societies. Best Minecraft Servers