none
Deadlocks on sql server from select statements?

    Question

  • I am not sure if i am looking correctly at the deadlocks but i see deadlocks between two select statements.These statements are being run through an application

    Below is the table schema from where the select is being performed

    CREATE TABLE [dbo].[CMS_LOCKS7](

    [PARENTID] [int] NOT NU,  --we have a non clustered index on this column
    [CHILDID] [int] NOT NULL, --we have a non clustered index on this column
    [ISMEMBER] [int] NOT NULL, -- we have a non clustered index on this column
    [ORDINAL] [int] NULL,-- we have a non clustered index on this column
    [RELATIONSHIPID] [int] NOT NULL,-- we have a non clustered index on this column
    [VERSION] [int] NULL,
    [KEYID] [int] NULL,
    [DATA] [image] NULL,
     CONSTRAINT [CMS_LOCKS7PK] PRIMARY KEY CLUSTERED 
    (
    [PARENTID] ASC,
    [CHILDID] ASC,
    [RELATIONSHIPID] ASC
    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
    ) ON [PRIMARY]


    • Edited by LuckyDba Friday, June 20, 2014 9:48 PM
    Thursday, February 13, 2014 6:39 PM

Answers

  • You would need to see the whole sequence of operations since each of the SPIDs entered transaction start.  Both of these SPIDs are currently trying to do a select.  But they both are in transaction state and both have done updates.  For example, the sequence might have been

    SPID 57 enters transaction state
    SPID 57 inserts a row with LOCKTABLE.CHILDID = 4308
    SPID 52 enters transaction state
    SPID 52 inserts a row with LOCKTABLE.CHILDID = 4263
    SPID 57 does a select on LOCKTABLE.CHILDID = 4263 -- so it must wait for SPID 52
    SPID 52 does a select on LOCKTABLE.CHILDID = 4308 -- so it must wait for SPID 57  ** DEADLOCK **

    So both SPID's are trying to do a SELECT, and each is blocking the other because of some insert or update (that's where the Mode X locks come from).

    Tom

    • Marked as answer by LuckyDba Thursday, February 13, 2014 11:47 PM
    Thursday, February 13, 2014 7:53 PM
  • And the tables are currently empty.There are no rows to update.

    The processes could have deleted each other's rows, but not having committed these deletes.

    The deadlock graph makes it very clear that both process hold exclusive locks.

    Taking Tom's chain of events and modifying it a bit, the story could be:

    SPID 57 enters transaction state
    SPID 57 deletes the row with LOCKTABLE.CHILDID = 4308
    SPID 52 enters transaction state
    SPID 52 deletes the row with LOCKTABLE.CHILDID = 4263
    SPID 57 does a select on LOCKTABLE.CHILDID = 4263 -- so it must wait for SPID 52
    SPID 52 does a select on LOCKTABLE.CHILDID = 4308 -- so it must wait for SPID 57  ** DEADLOCK **


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    • Marked as answer by LuckyDba Thursday, February 13, 2014 11:47 PM
    Thursday, February 13, 2014 11:02 PM

All replies

  • but i see deadlocks between two select statements

    Hello Lucky,

    Two SELECT statements can never cause a deadlock.

    What's that for a tool and where do you see a deadlock?


    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    Thursday, February 13, 2014 6:53 PM
  • Hello Olaf Helper.

    Its idera sql diagnostic Manager.

    Also the database is on sql server 2012 .


    Thank you


    lucky


    • Edited by LuckyDba Thursday, February 13, 2014 6:57 PM
    Thursday, February 13, 2014 6:55 PM
  • Again: Where do you see a deadlock?


    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    • Marked as answer by LuckyDba Thursday, February 13, 2014 11:47 PM
    • Unmarked as answer by LuckyDba Thursday, February 13, 2014 11:47 PM
    Thursday, February 13, 2014 6:58 PM
  • Sorry i posted an incomplete reply .Please find the images below.I see the deadlocks in blocked sessions



    lucky





    • Edited by LuckyDba Thursday, February 13, 2014 7:17 PM
    Thursday, February 13, 2014 7:08 PM
  • With the information above, I am not sure why and where but, why don't you think about using WITH (NOLOCK) in your queries?

    Good Luck :).. visit www.sqlsaga.com for more t-sql code snippets and bi related how to articles.

    Thursday, February 13, 2014 7:11 PM
  • You would need to see the whole sequence of operations since each of the SPIDs entered transaction start.  Both of these SPIDs are currently trying to do a select.  But they both are in transaction state and both have done updates.  For example, the sequence might have been

    SPID 57 enters transaction state
    SPID 57 inserts a row with LOCKTABLE.CHILDID = 4308
    SPID 52 enters transaction state
    SPID 52 inserts a row with LOCKTABLE.CHILDID = 4263
    SPID 57 does a select on LOCKTABLE.CHILDID = 4263 -- so it must wait for SPID 52
    SPID 52 does a select on LOCKTABLE.CHILDID = 4308 -- so it must wait for SPID 57  ** DEADLOCK **

    So both SPID's are trying to do a SELECT, and each is blocking the other because of some insert or update (that's where the Mode X locks come from).

    Tom

    • Marked as answer by LuckyDba Thursday, February 13, 2014 11:47 PM
    Thursday, February 13, 2014 7:53 PM
  • Thank you tom.But i can only find select statements for session id 57 and 52.i dont find any update statements for these sessions .Please see the picture below

    And the tables are currently empty.There are no rows to update.


    lucky



    • Edited by LuckyDba Thursday, February 13, 2014 8:44 PM
    Thursday, February 13, 2014 8:31 PM
  • And the tables are currently empty.There are no rows to update.

    The processes could have deleted each other's rows, but not having committed these deletes.

    The deadlock graph makes it very clear that both process hold exclusive locks.

    Taking Tom's chain of events and modifying it a bit, the story could be:

    SPID 57 enters transaction state
    SPID 57 deletes the row with LOCKTABLE.CHILDID = 4308
    SPID 52 enters transaction state
    SPID 52 deletes the row with LOCKTABLE.CHILDID = 4263
    SPID 57 does a select on LOCKTABLE.CHILDID = 4263 -- so it must wait for SPID 52
    SPID 52 does a select on LOCKTABLE.CHILDID = 4308 -- so it must wait for SPID 57  ** DEADLOCK **


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    • Marked as answer by LuckyDba Thursday, February 13, 2014 11:47 PM
    Thursday, February 13, 2014 11:02 PM
  • With the information above, I am not sure why and where but, why don't you think about using WITH (NOLOCK) in your queries?

    That sounds like an awfully bad idea. Actually, given the name of the table, I'm not sure that even the normal suggestion of using READ_COMMITTED_SNAPSHOT is a good idea.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Thursday, February 13, 2014 11:04 PM
  • Thank you Erland.I will get in touch with devlopers to see what they are running.


    lucky

    Thursday, February 13, 2014 11:47 PM