Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

How to avoid Deadlock while deleting data Expand / Collapse
Author
Message
Posted Friday, March 29, 2013 11:18 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Monday, July 28, 2014 6:03 PM
Points: 417, Visits: 761
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


  Post Attachments 
deadlock.JPG (6 views, 122.69 KB)
deadlock_plan.jpg (3 views, 65.53 KB)
Post #1437027
Posted Friday, March 29, 2013 11:41 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, August 11, 2014 8:57 AM
Points: 184, Visits: 345
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?

Post #1437035
Posted Friday, March 29, 2013 11:57 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Monday, July 28, 2014 6:03 PM
Points: 417, Visits: 761
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
Post #1437038
Posted Friday, March 29, 2013 12:24 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, August 11, 2014 8:57 AM
Points: 184, Visits: 345
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.
Post #1437050
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse