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 ««12

unexplained deadlock Expand / Collapse
Author
Message
Posted Sunday, March 3, 2013 8:37 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, March 10, 2014 3:19 AM
Points: 16, Visits: 40
OK.
chance that NOLOCK will be ignored is other thing than NOLOCK to be harmful. so I can keep this NOLOCK anyway, what do I have to lose here?

How do you suggest to update rows with a mode of "I don't care if few rows where not updated" - something like "readpast" .
do you suggest to use temporary table for that that will have the data by using select with (READPAST, ROWLOCK, UPDLOCK) and than update it?
Post #1425961
Posted Sunday, March 3, 2013 8:51 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 7:21 AM
Points: 7,135, Visits: 12,749
aviadavi (3/3/2013)
OK.
chance that NOLOCK will be ignored is other thing than NOLOCK to be harmful. so I can keep this NOLOCK anyway, what do I have to lose here?

In this specific case, maybe nothing. But if you are using here and are as nonchalant about using it as you seem to be then if you do not change your approach towards NOLOCK you have a lot to lose, e.g. the confidence of your users, someones life if you're working in a medical system, a ton of money of you're working in a financial system, all of which could lead to the loss of your job or worse.

How do you suggest to update rows with a mode of "I don't care if few rows where not updated" - something like "readpast" .
do you suggest to use temporary table for that that will have the data by using select with (READPAST, ROWLOCK, UPDLOCK) and than update it?

I also do not condone the use of READPAST; I place it in the same category as NOLOCK. If you're this bent on allowing dirty reads or skipping rows then look into enabling the READ_COMMITTED_SNAPSHOT isolation mode.

Using a temp table to select [potentially dirty] rows using NOLOCK and READPAST and then joining that temp table back to the base table to do the update may help you avoid some issues but writers will always block writers. So, like I said, even if your logical filters are mutually exclusive, if you incur any page splits on update you could still encounter some blocking during concurrent updates even with a ROWLOCK hint.


__________________________________________________________________________________________________
There are no special teachers of virtue, because virtue is taught by the whole community. --Plato
Post #1425963
Posted Sunday, March 3, 2013 10:20 AM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 8:31 AM
Points: 40,456, Visits: 36,912
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



Gail Shaw
Microsoft Certified Master: SQL Server 2008, MVP
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

Post #1425974
Posted Sunday, March 3, 2013 1:00 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 12:24 PM
Points: 35,611, Visits: 32,203
The first query in the deadlock will very likely have a full table or index scan because of the non-SARGable criteria you wrote for the wait-time. Because you used a CTE in an attempt to quickly isolate rows, you also have the problem of executing a SELECT, an UPDATE, and a JOIN instead of just doing a quick update and getting out. That would probably explain the deadlock, as well, because you're not doing just an UPDATE.

I recommend you rewrite the first query as the following. You can do the TOP thing if you insist, but I don't believe it will be necessary once you have the index I recommend in a bit in place.

 UPDATE dbo.TableA
SET TableAStatusTypeID = 9
WHERE WaitingRoomCreationTime <= DATEADD(mi,-@wrExpirationMinutes,@curUtcDate)
AND TableAStatusTypeID IN (1,2,3,4)
;

Once that's done, it would really help the performance if you put an index on the WaitingRoomCreationTime. It may be a hindrence to add TableAstatusTypeID to that index because then you'd be updating an index column but you'd have to try both ways to see which the optimizer thinks is best. With either index, I would add the TableAID as the final column of the index (not as an INCLUDE, either) so that you can make the index UNIQUE which will prevent SQL Server from adding a rather lengthy uniquifier to the index and make the row lookup that will occur a bit faster. SQL Server LOVES unique indexes.

You have a similar problem with your second query (the DELETE). In essence, you're first doing a SELECT followed by a joined delete all in one query. I'd rewrite it as follows.

 DELETE FROM dbo.TableA
WHERE TableAStatusTypeID IN (11,12)
;

If you really need to do it in segments according to TOP (@TOP_HANDLED_ROWS), then use a Temp Table to isolate the rows instead of a CTE to get the SELECT part of it all out of the transaction. Like this...

 SELECT TOP (@TOP_HANDLED_ROWS) 
TableAID
INTO #MyHead
FROM dbo.TableA
WHERE TableAStatusTypeID IN (11,12)
ORDER BY TableAID
;
DELETE FROM dbo.TableA
WHERE TableAID IN (SELECT TableAID FROM #MyHead)
;

Notice in the above that we're doing deletes in the same order as what I expect the Clustered Index will be. That moves most of the deletes away from what is usually the most active section of the table (the most recent IDs) and keeps from having a shotgun effect which scatters the deletes across a larger part of the table.

For those about to jump on me about the use of "IN", you'll find that "IN" is frequently faster than an actual INNER JOIN. I'd offer proof of that in the form of code but I don't want to hijack this thread with such proof.

Of course, all of this is just a suggestion because I don't actually have your data to experiement with. I can only tell you that I've solved many a deadlock using the methods I've described. Your mileage may vary and you'll need to experimennt. My methods are not a panacea


--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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1425995
Posted Sunday, March 3, 2013 1:47 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, March 10, 2014 3:19 AM
Points: 16, Visits: 40
thank you all.

Jeff, I really need to do it in segments according to TOP (@TOP_HANDLED_ROWS).
according to your suggestion, i'm still a bit afraid to leave it without any NOLOCK at the select part (i'm sorry i'm repeating myself all the time but i really don't care about dirty reads, i just want to avoid shared locks as well - because I had some deadlocks involving shared locks as well once).

maybe using READ_COMMITTED_SNAPSHOT as opc-three suggested will be good as well..
Post #1426007
Posted Sunday, March 3, 2013 1:56 PM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Today @ 8:31 AM
Points: 40,456, Visits: 36,912
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 2008, MVP
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

Post #1426013
Posted Sunday, March 3, 2013 3:10 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 4:56 AM
Points: 20,815, Visits: 32,748
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

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Post #1426021
Posted Sunday, March 3, 2013 3:48 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 4:56 AM
Points: 20,815, Visits: 32,748
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





Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Post #1426028
Posted Sunday, March 3, 2013 5:20 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 12:24 PM
Points: 35,611, Visits: 32,203
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1426048
Posted Sunday, March 3, 2013 5:52 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 4:56 AM
Points: 20,815, Visits: 32,748
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.



Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Post #1426054
« Prev Topic | Next Topic »

Add to briefcase ««12

Permissions Expand / Collapse