unexplained deadlock

Question

unexplained deadlock

Viewing 5 posts - 16 through 19 (of 19 total)

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

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

Drop the nolock. It has no effect on a table that is the target of an update, that's documented. It's also plain bad practice in most cases. Optimise the query, tune the indexes and you'll have far fewer problems with locks and deadlocks.

See - http://blogs.msdn.com/b/davidlean/archive/2009/04/06/sql-server-nolock-hint-other-poor-ideas.aspx

It's not just dirty reads, it's duplicate reads, missing rows. In short, the results can be completely inaccurate (I've seen a report where the total was off by 30% as a result of using nolock in the base query)

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 2

GilaMonster (3/3/2013)
aviadavi (3/3/2013)
I'm using NOLOCK because integrity is not that important for me and again - I want good timing and i want to avoid potential deadlock by not locking the rows I select.
So in other words you'd rather get incorrect data fast than correct data slightly slower?
I take it that you ignored all the advice we gave you last time you asked about this query (remove the rowlock, remove the nolock, optimise the query and tune the indexes).
http://www.sqlservercentral.com/Forums/Topic1424131-391-1.aspx

Didn't like our answers so hoped he would get something different in a different thread.

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 3

Curious if the following would work in this situation with proper indexing of the target table.

WITH TableA_CTE AS(

SELECT TOP (@TOP_HANDLED_ROWS)

TableAID,

TableAStatusTypeID,

LastUpdateTime

FROM

dbo.TableA

WHERE

WaitingRoomCreationTime < dateadd(minute, -@wrExpirationMinutes, @curUtcDate)

AND TableAStatusTypeID IN (1,2,3,4) -- CreatePending/Creating/Created/CreatedReportFailed

ORDER BY

WaitingRoomCreationTime asc

)

UPDATE TableA_CTE SET

TableAStatusTypeID= 9,

LastUpdateTime = @curUtcDate;

go

Jeff Moden SSC Guru Points: 1003871 More actions · Answer 4

Lynn Pettis (3/3/2013)
Curious if the following would work in this situation with proper indexing of the target table.
WITH TableA_CTE AS(
SELECT TOP (@TOP_HANDLED_ROWS)
TableAID,
TableAStatusTypeID,
LastUpdateTime
FROM
dbo.TableA
WHERE
WaitingRoomCreationTime < dateadd(minute, -@wrExpirationMinutes, @curUtcDate)
AND TableAStatusTypeID IN (1,2,3,4) -- CreatePending/Creating/Created/CreatedReportFailed
ORDER BY
WaitingRoomCreationTime asc
)
UPDATE TableA_CTE SET
TableAStatusTypeID= 9,
LastUpdateTime = @curUtcDate;
go

More than likely... if you really needed the TOP functionality after you did the proper indexing, which I would think you wouldn't.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 5

Jeff Moden (3/3/2013)
Lynn Pettis (3/3/2013)
Curious if the following would work in this situation with proper indexing of the target table.
WITH TableA_CTE AS(
SELECT TOP (@TOP_HANDLED_ROWS)
TableAID,
TableAStatusTypeID,
LastUpdateTime
FROM
dbo.TableA
WHERE
WaitingRoomCreationTime < dateadd(minute, -@wrExpirationMinutes, @curUtcDate)
AND TableAStatusTypeID IN (1,2,3,4) -- CreatePending/Creating/Created/CreatedReportFailed
ORDER BY
WaitingRoomCreationTime asc
)
UPDATE TableA_CTE SET
TableAStatusTypeID= 9,
LastUpdateTime = @curUtcDate;
go
More than likely... if you really needed the TOP functionality after you did the proper indexing, which I would think you wouldn't.

Well, the TOP functionality allows you to batch the updates instead of trying to do all at once.

Sort of finding myself side tracked here a bit. I have started to put together a rough test, just haven't finished writing the test code to populate the table.