unexplained deadlock

  • Hi,

    I'm using SQL Server 2008.

    i have 2 different statements that lock each other, can you please explain why they are locking each other?

    maybe i miss nolock in the inner join?

    <deadlock victim="process674e508">

    <process-list>

    <process id="process674e508" taskpriority="0" logused="0" waitresource="KEY: 25:72057594045071360 (da0000d37950)" waittime="2024" ownerId="461564310" transactionname="UPDATE" lasttranstarted="2013-03-01T10:27:00.740" XDES="0x81cff970" lockMode="U" schedulerid="32" kpid="12284" status="suspended" spid="133" sbid="0" ecid="0" priority="0" trancount="2" lastbatchstarted="2013-03-01T10:27:00.727" lastbatchcompleted="2013-03-01T10:27:00.727" clientapp=".Net SqlClient Data Provider" hostname="APP77" hostpid="2140" loginname="APPUser" isolationlevel="read uncommitted (1)" xactid="461564310" currentdb="25" lockTimeout="4294967295" clientoption1="673185824" clientoption2="128056">

    <executionStack>

    <frame procname="APP77.dbo.spTableASelectRows" line="54" stmtstart="4768" stmtend="6220" sqlhandle="0x030019003f43800884f8c40073a100000100000000000000">

    WITH TableA_CTE AS

    (

    SELECTTOP (@TOP_HANDLED_ROWS) TableAID

    FROMdbo.TableA WITH (NOLOCK)

    WHEREDATEDIFF(MINUTE, WaitingRoomCreationTime, @curUtcDate) > @wrExpirationMinutes

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

    )

    UPDATETOP (@TOP_HANDLED_ROWS) EC WITH (ROWLOCK)

    SETTableAStatusTypeID= 9,

    LastUpdateTime= @curUtcDate,

    FROMTableA_CTE CTE

    INNER JOIN dbo.TableA EC

    ON EC.TableAID = CTE.TableAID;

    </frame>

    </executionStack>

    <inputbuf>

    Proc [Database Id = 25 Object Id = 142623551] </inputbuf>

    </process>

    <process id="process4931708" taskpriority="0" logused="0" waitresource="KEY: 25:72057594045071360 (1e00262b2a23)" waittime="2029" ownerId="461564333" transactionname="DELETE" lasttranstarted="2013-03-01T10:27:00.770" XDES="0xce53f970" lockMode="U" schedulerid="5" kpid="7044" status="suspended" spid="108" sbid="0" ecid="0" priority="0" trancount="2" lastbatchstarted="2013-03-01T10:27:00.710" lastbatchcompleted="2013-03-01T10:27:00.710" clientapp=".Net SqlClient Data Provider" hostname="APP64" hostpid="2080" loginname="APPUser" isolationlevel="read uncommitted (1)" xactid="461564333" currentdb="25" lockTimeout="4294967295" clientoption1="673185824" clientoption2="128056">

    <executionStack>

    <frame procname="APP77.dbo.spTableASelectRows" line="129" stmtstart="11560" stmtend="12464" sqlhandle="0x030019003f43800884f8c40073a100000100000000000000">

    WITH TableA_CTE AS

    (

    SELECT TOP (@TOP_HANDLED_ROWS) TableAID

    FROM dbo.TableA WITH(NOLOCK)

    WHERETableAStatusTypeID IN (11,12)

    ORDER BY LastUpdateTime ASC

    )

    DELETETOP (@TOP_HANDLED_ROWS) EC WITH (ROWLOCK)

    FROMTableA_CTE CTE

    INNER JOIN dbo.TableA EC

    ON EC.TableAID = CTE.TableAID;

    ---------------------------------------------------------

    </frame>

    </executionStack>

    <inputbuf>

    Proc [Database Id = 25 Object Id = 142623551] </inputbuf>

    </process>

    </process-list>

    <resource-list>

    <keylock hobtid="72057594045071360" dbid="25" objectname="APP77.dbo.TableA" indexname="PK_TableA" id="lockc5768e00" mode="U" associatedObjectId="72057594045071360">

    <owner-list>

    <owner id="process4931708" mode="U"/>

    </owner-list>

    <waiter-list>

    <waiter id="process674e508" mode="U" requestType="wait"/>

    </waiter-list>

    </keylock>

    <keylock hobtid="72057594045071360" dbid="25" objectname="APP77.dbo.TableA" indexname="PK_TableA" id="lock818fee00" mode="U" associatedObjectId="72057594045071360">

    <owner-list>

    <owner id="process674e508" mode="U"/>

    </owner-list>

    <waiter-list>

    <waiter id="process4931708" mode="U" requestType="wait"/>

    </waiter-list>

    </keylock>

    </resource-list>

    </deadlock>

    I see i can't specify NOLOCK in the inner join ..

    can you advise?

  • Remove ROWLOCK hint and index in a TABLE A TableAStatusTypeID column

  • WHY ??

    Can you explain the deadlock? and how removing ROWLOCK will solve this out? the update has TOP of 100 only..

    BTW, what did you say about the index? should I remove an index on TableAStatusTypeID or create another one?

  • You have to create and what is your isolation level

  • read uncommitted. (specified in the XML deadlock)

  • I am assuming you altered the graph before posting since the first query has a syntax error in it:

    WITH TableA_CTE

    AS (

    SELECT TOP (@TOP_HANDLED_ROWS)

    TableAID

    FROM dbo.TableA WITH (NOLOCK)

    WHERE DATEDIFF(MINUTE, WaitingRoomCreationTime, @curUtcDate) > @wrExpirationMinutes

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

    )

    UPDATE TOP (@TOP_HANDLED_ROWS)

    EC WITH (ROWLOCK)

    SET TableAStatusTypeID = 9,

    LastUpdateTime = @curUtcDate/*,*/

    FROM TableA_CTE CTE

    INNER JOIN dbo.TableA EC ON EC.TableAID = CTE.TableAID;

    At any rate, what are you trying to do with these inserts? Why do you have a TOP (without an ORDER BY no less) in the CTE, and then a TOP again in the UPDATE statement?

    I am thinking the first query could be rewritten in a much simpler form, like this:

    UPDATE TOP (@TOP_HANDLED_ROWS)

    dbo.TableA

    SET TableAStatusTypeID = 9,

    LastUpdateTime = @curUtcDate

    WHERE DATEDIFF(MINUTE, WaitingRoomCreationTime, @curUtcDate) > @wrExpirationMinutes

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

    And why all the NOLOCK hints? Do you understand the perils of using NOLOCK? I would recommend getting rid of those immediately and start considering the use of the hint very carefully before you ever use it again. There are few cases when you can safely use the NOLOCK hint and not sacrifice data integrity but I am not sure I would ever agree with using them in the context of a database where the updating of data were occurring, especially from concurrently threads modifying the same sets of data. These two statements look to be mutually exclusive in terms of their filtering but any overlap of data being selected or affected when performing a concurrent update could cause unexpected results in one query or the other.

    There are no special teachers of virtue, because virtue is taught by the whole community.
    --Plato

  • Yes, i just took off a row in the update that can be classified.

    I work with millions of rows so I'm using TOP (+including indexes) to make the query fast and avoid delays.

    I'm processing the rows after the update in our front end servers, so it is not possible for me to process all the rows so i must limit it by using TOP.

    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.

    I'm using ROWLOCK because i want to tell the optimizer to start by using row locks. (I don't want it to lock entire page/table) - again - to avoid deadlocks.

    AS for the deadlock - do you know to say why the statements are waiting for each other? (Even by assuming overlap is possible)

  • For one, NOLOCK is ignored for updates. Think about it.

    Also, even if data is not overlapping it does not mean that pages may not overlap. Page splits from updating records on a page can also complicate matters.

    There are no special teachers of virtue, because virtue is taught by the whole community.
    --Plato

  • i put a NOLOCK of the select statement within the CTE just to fetch the records with the right criteria without any locks. than , in the same CTE transaction i want to update the rows using the Primary key. sounds logical, isn't it?

    i didn't know about the page split , i will read about it .. thanks.

  • You're updating the table you're selecting from and trying to use NOLOCK on so chances are the NOLOCK will not be respected.

    There are no special teachers of virtue, because virtue is taught by the whole community.
    --Plato

  • 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?

  • 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

  • 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, 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
  • 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.

    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)

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

Viewing 15 posts - 1 through 15 (of 19 total)

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