July 19, 2013 at 9:42 am
Hi All
I've been asked to look into a dead lock issue and I don't have access to the server so I wanted some second thoughts on the fix prior to handing it over. I've been through the dead lock info captured in the log in order to get where I am now.
The query in question is executed many times an on occasion dead locks. The only query in the deadlock info is the query in question. All locks shown are exclusive and on the clustered index.
Currently the query looks like this:
--first 2 are input paramaters in real life
DECLARE @PKin int,
@UpdateParam int,
--next 2 are declared
@count int,
@UpdateCheck int
--clustered index seek is used here
SELECT @UpdateCheck = ColumnToUpdate
FROM TableToBeUpdated
WHERE PKID = @PKin;
--index seek is used here
SELECT @count = COUNT(*)
FROM TableToCheckIfUpdateNeeded
WHERE @UpdateCheck = AnotherID1
AND AnotherID2 = 123;
--clusterd index update here
IF @count > 0
BEGIN
UPDATE TableToBeUpdated
SET ColumnToUpdate = @UpdateParam
WHERE PKID = @PKin;
END
So I've re-written this so it's a single statement. This allows me to get rid of the declared variables and just use the parameters.
The execution plan is fundamentally the same, although all together now. i.e. same seeks, estimates etc. I'd guess it'll change slightly when in a stored procedure again.
UPDATE TableToBeUpdated
SET ColumnToUpdate = @UpdateParam
FROM TableToBeUpdated as u
INNER JOIN TableToCheckIfUpdateNeeded as cu
ON u.ColumnToUpdate = cu.AnotherID1
WHERE PKID = @PKin
AND
AnotherID2 = 123;
So the question is, will this be any better (apart from looking nicer) with regard to the dead lock issue?
Feel free to point out if I've made an error in the re-write too 😉
The other thought I had was to add a non-clustered index for it to use instead of the initial clustered index seek. It would therefore seek of the non-clustered to get the info, obtain shared locks and switch to exclusive lock at the point of update...at least that's what I think. Any thoughts or advice there please?
Thanks so much for taking the time to help, very much appreciated.
P.S. The optimizer wont use a non-clustered index in the seek part unless it's forced. Thanks
July 20, 2013 at 4:27 pm
It's difficult to comment on the deadlock, since I have not seen the deadlock traces.
But I think the query would better be:
UPDATE TableToBeUpdated
SET ColumnToUpdate = @UpdateParam
FROM TableToBeUpdated as u
WHERE EXISTS (SELECT *
FROM TableToCheckIfUpdateNeeded as cu
WHERE u.ColumnToUpdate = cu.AnotherID1
AND AnotherID2 = 123)
AND PKID = @PKin
[font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]
July 21, 2013 at 3:16 am
Thank you very much for the reply. That's a valid point, I missed the check to see if the update was needed.
The deadlock is happening on the clusterd index. The update is deadlocking susequent reads. I was wondering if forcing the use of a non-clustered covering index for the read may help?
Thanks again!
July 21, 2013 at 3:22 am
MartJ (7/21/2013)
The deadlock is happening on the clusterd index. The update is deadlocking susequent reads.
As I said, I need to see the deadlock trace, to be able to say anything more.
It sounds funny when the you say The update is deadlocking susequent reads, since deadlock is a mutual action. One statement cannot deadlock another, but two statements (or more precisely two processes) can deadlock each other.
[font="Times New Roman"]Erland Sommarskog, SQL Server MVP, www.sommarskog.se[/font]
July 21, 2013 at 4:05 am
Thanks again Erland and sorry for the miss wording of the dead lock issue. I'll try and get the trace up tomorrow.
Have a great weekend.
July 22, 2013 at 3:04 am
I think the query re-write will be enough but I'll post back if not, thanks again for your help, very much appreciated .
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply