March 29, 2013 at 11:18 am
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
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
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
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
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy