Post reply

How to avoid Deadlock while deleting data

  • March 29, 2013 at 11:18 am

    #272285

    The X_table has around 25K rocords (52MB in size), the application deletes records from this table, when it deletes the deadlock occurs, spid 1 hold X lock while deleting and spid 2 request for S lock until the spid 1 completes its delete and vice versa, which leads to deadlock.

    Below is the simple delete statement.

    exec sp_executesql N'delete from X_table where internal_name = @tableName',N'@tableName nvarchar(21)',@tableName=N'Tbl_Deleteme'

    I've attached deadlock graph and estimated plan for delete as well for reference.

    Could you please share your thoughts to mitigate this deadlock issue?

    Thanks in advance...

    Thanks
    Jay
    http://www.sqldbops.com

  • March 29, 2013 at 11:41 am

    #1602110

    I guess there are two workaround for this.

    1. exchange the clustered index. make clustered index on the column Internal_name.

    or

    2. use tablelock hint to avoid deadlock

    by the way, what is fragmentation level of your table?

  • March 29, 2013 at 11:57 am

    #1602113

    Thanks, the current PK index has 23% fragmented.again the current PK has 8 other FKs, removing this PK is bit complicated though...

    Thanks
    Jay
    http://www.sqldbops.com

  • March 29, 2013 at 12:24 pm

    #1602122

    probably you can write a procedure to delete records based on existing eclustered index using a smaller transaction. Will be slower but will avoid deadlock.

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply