Results 1 to 6 of 6
  1. #1
    Join Date
    Jan 2004
    Posts
    145

    Unanswered: Deadlocks - look to be at random

    Hello,
    We are running sql server 2000. We have a table with about 1.5 million records. About a month ago we started seeing deadlocks from a stored proc that does two successive updates in this table. It is an order line table so the rows being updated would be the items in the customer's cart. So it shouldn't be that many rows being updated. We did however find a few carts with hundreds of items but they hadn't been updated for a year or so.

    The sp that is doing the updating is using the ROWLOCK hint. For one instance I was able capture the blocking spid's locks which were at 70,000 for the table. I believe the majority were KEY locks in the "U" mode. I haven't identified the "resource" yet. There are two fields that are being updated. They have indexes on them but are separate from the clustered index.

    We did a DBREINDEX when we first started seeing the problem and it seemed to fix it for awhile. I'd have to look back to see exactly how long. Anyway it seems pretty random as far as time of day and activity amount.

    I'm not sure if this is enough info. It seems to me there just shouldn't be that many locks. Yesterday I captured a spid that had 300K locks on the table. I looked how many line items in the table the sp should have updated and it was three. Are indexes being updated? Is this why so many locks? If I can provide anymore info please let me know.
    Thanks,
    GG
    Code:
    On Error Goto Hell
    
    Hell:
        Msgbox("An error occurred, but was sent to Hell. You may continue.")

  2. #2
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,799
    Provided Answers: 11
    What is the query plan for the update statement(s)?

  3. #3
    Join Date
    Jan 2004
    Posts
    145
    Hopefully this will do. It seemed easier than posting an image and I didn't want show all the info in a text version of the plan:

    EXECUTE(0%) -> COND(0%) -> UPDATE(0%) -> Clustered Index Update(2%) -> Compute Scalar(0%) -> Top(0%) -> Nested Loops, Left Outer Join(0%) <- Filter(0%) <- Bookmark Lookup(29%) <- Index Seek(1%)

    There is also a Clustered Index Seek that feeds into Nested Loops at a cost of 1%.

    The second update has the same plan as the first minus the Compute Scalar.

    There is a third update the would run by itself if the condition isn't met.

    EXECUTE(0%) -> COND(0%) -> UPDATE(0%) -> Clustered Index Update(2%) -> Top(0%) -> Filter(0%) <- Bookmark Lookup(29%) <- Index Seek(1%)

    So if the condition is met the first two updates run, otherwise the third update is run. I should add that there is a join to another table that has about 40 records for the first two updates.
    GG
    Code:
    On Error Goto Hell
    
    Hell:
        Msgbox("An error occurred, but was sent to Hell. You may continue.")

  4. #4
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,799
    Provided Answers: 11
    Is there a trigger on this table?

    Also, I expect you have already turned on the error trap for deadlocks?
    Code:
    dbcc traceon(-1, 1204)

  5. #5
    Join Date
    Jan 2004
    Posts
    145
    There is no trigger on the table. I haven't turned the trace on yet, but I did run a trace in profiler. One reason I haven't turned the trace on is I've been told to move on, but also it seems the problem has been identified, it is just a matter of figuring out what is causing it. The problem being that all those locks are being requested to update a few rows.

    Also since I last posted we have had no deadlocks. So it is almost like a certain situation causes this, but I'm not sure. If you think the trace will still help I might be able to turn it on at some point.
    thanks,
    GG
    Code:
    On Error Goto Hell
    
    Hell:
        Msgbox("An error occurred, but was sent to Hell. You may continue.")

  6. #6
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,799
    Provided Answers: 11
    it is probably best to only turn it on when you are expecting the problem again. It will tell you what processes were involved in the deadlock, and what tables/indexes were being contested.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •