Results 1 to 6 of 6
  1. #1
    Join Date
    Jan 2006
    Posts
    21

    Unanswered: Transaction (Process ID 106) was deadlocked

    ive seen this Deadlock Error message out on the internet being discussed, but no solution being offered.
    i have a windows service that's running Select Statements [one at a time] - so unless there's some command in sql server that would re-run these - it could be a problem for me.
    now if im running this select proc manually - of course i see the message and re-run the process, but how can this be accomplished programatically.
    see msg below:

    Transaction (Process ID 106) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.

    thanks for any help on this
    rik

  2. #2
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    Have you identified the 2 operations deadlocking eachother? Is there one that has a lower priority? have you looked at SET DEADLOCK_PRIORITY? do you care about dirty reads in your SELECT statements? Have you looked at the NOLOCK query hint?
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

  3. #3
    Join Date
    Jan 2006
    Posts
    21
    no Sir, i havent. i'm pretty new to sql server - so, i guess i'd be a newbie. i'll research the items you listed and see what i can find.
    i was a little confused since these are just selects and no transactions are taking place, and that's where i thought locks came into place.
    i'll check these out.
    thanks again
    rik

  4. #4
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    also look at sp_lock, sp_who and sp_who2 and profiler. if you can post your code and ddl.
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

  5. #5
    Join Date
    Dec 2003
    Posts
    61

    deadlock

    Did you resolve the problem?

    I am facing the similar situation and was curious if had resolved it.

  6. #6
    Join Date
    Mar 2005
    Location
    Netherlands
    Posts
    280

    Traceflag

    You can get detailed information about the objects and spids involved in the deadlock if you use the traceflag 1205 (DBCC TRACEON (1205), or make it a startup parameter of the instance). It must be on when the deadlock occurs and you can read about it afterwards in the errorlog.

    We manage hundreds of databases and we get the odd deadlock now and again. If the transaction management and error handling are in order than there's no harm done. When a lot of users are handling the same data it sometimes can happen that two sessions want to access the same that the other has locked. It's almost impossible to reproduce the deadlock, thus impossile to trace. You get a deadlock, it's a fact of life, the application should able to handle it robustly.

    We've had a few occasions when a new application or release deadlocked ten times or more a day. It was reproducable (just sit and wait ) so we set the traceflag and ran a trace with Profiler. After analizing the enormous amount of data we got we got an idea about what was going wrong.

    After you've pinpointed the problem there are a few things you can do:
    - Rewrite the code that's causing the problem. Try to change the order in which selects or DML are done in such a way it can't deadlock
    - Keep transactions as short as possible. Selects can be done before the BEGIN TRAN.
    - Use indexes and other performance enhancers to speed things up. The faster things are done the smaller the chance of a deadlock.
    - Use the NOLOCK hint, but use it wisely! Try to think of how bad it is if you get inconsistent or non existing (when a ROLLBACK occurs) data. So with financial reports it's usually a bad idea to use it. With search results it probably doesn't matter so much.

    Well this is my two cents! I realize some of the opinions stated here are subjective. So if anybody's got another I'd like to here it.

    Lex

Posting Permissions

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