Results 1 to 11 of 11

Thread: DeadLock Issue

  1. #1
    Join Date
    Mar 2004
    Location
    NC
    Posts
    13

    Unanswered: DeadLock Issue

    I serveral triggers in a table that is accessed by mutilple users in the application I am writing. I have come across a deadlock issue and have tried to resolve the issue by breaking down the triggers into many much smaller trans with no success. In general terms, can some one suggest some technique I am missing that I can try to avoid this issue .

  2. #2
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    There are 3 techniques that can be used to help you avoid deadlocks. Number one is to ensure the same order of access to objects within a transaction. This comes from the definition of the deadlock itself:

    TableA has a lock placed by user1 who's trying to access TableB,while user2 has a lock on TableB and trying to access tableA.

    To break this vicious circle you need to structure all transactions in such a way that TableA is always accessed first.

    Number two is to make your transactions as short as possible, without violation business logic and business requirements of course.

    Number three - and here there will be a lot of screaming, yelling, calling names, - but reality will prove them all wrong,- lower transaction isolation level.
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  3. #3
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    rdjabarov and I disagree on that last point. I keep insisting on correct answers, he prefers easy answers.

    In reality, if what you need is a quick answer the rand() function will often get you there orders of magnitude faster than you can possibly get the correct answer, with no locking issues at all.

    -PatP

  4. #4
    Join Date
    Jun 2004
    Location
    Far away from heaven
    Posts
    194
    Number three - and here there will be a lot of screaming, yelling, calling names, - but reality will prove them all wrong,- lower transaction isolation level.
    When the users are screaming that: WE CAN'T COMPLETE OUR WORK, IT'S TOO SLOW AND WHATE THE HECK IS THIS timeout-expired IN OUR APPLICATION?
    I don't see anything rising in the PERFMON at my dbserver but LOCKREQUEST/SEC (constantly above thousand figures) and high processor queue length due to blocking.
    What now!
    Same is the case when there's deadlock error, sp_lock contains more than hundereds of rows.
    I'm just venting and cryin for the "Number Three advice"

    One more thing that's rather strange and confusing:
    why isn't there any READPAST isolation level. I guess it's by default in ORACLE.
    [user1]
    BEGIN TRAN
    UPDATE mytable SET a=b
    ..
    [user2]
    select * from mytable
    all previously committed rows r returned.

    But in SQL, user2 waits , rather have to put select * from with (readpast) from mytable.
    I guess [readpast] is better than [nolock]



    Howdy!

  5. #5
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    OK, Pat, it's all circumstantial, let's agree at least on something (otherwise one of us will have to choose a different forum, and that's not what I intend to do, nor do I encourage for you to consider). When my app references Countries+States+Zips tables while displaying information to 100+ simalteneous users at the call center, don't you think it would be an overkill to use READ COMMITTED default isolation level for SQL? What are the chances that a call is placed to a person that lives in a country that has just been discovered, recorded in Countries table, associated States entries made, and their postal system delivered zip mapping to the call center, probably along with a list citizens that immediately received telephone service and became potential customers? It borders with absurdity, but that's what I feel when I see so much passion in your postings about NOLOCK.

    EDITED: And how can RAND() help you?
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  6. #6
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Setting connection level lock handling to allow permissive locking (lock handling like what NOLOCK gives) is one thing, to override the locking for a specific table within a transaction (actually using NOLOCK) is entirely different even though both situations affect the handling of locks.

    Setting the transaction isolation level for the spid says that locking isn't important for this thread (spid) and is usually quite safe. You won't inadvertantly clobber important data due to a mangled or misprocessed read/write combination either within or between threads. This is a fine solution for the kind of problem you proposed where multiple users might need browse access to the same data.

    Setting the lock handling for a specific table to a lower level than the thread that uses it requires a lot of knowledge about the entire system involved in order to have a prayer of being safe. I've had to do this before, but it isn't anything I'm comfortable with and wouldn't recommend for anyone that I didn't really, really hate. It takes ongoing monitoring to prevent a rogue process from upsetting the delicate balance that it depends upon.

    I don't have any problem with setting the transaction isolation level, but am really, really wary of using NOLOCK explicitly. There are cases where it can be quite necessary... These are not the kind of solutions that I recommend for the long haul. They might fill a particular need under very specific circumstances, but they are dangerous.

    -PatP

  7. #7
    Join Date
    Jun 2004
    Location
    Far away from heaven
    Posts
    194
    And how about EXPLICITLY specifying READPAST in queries, ain't it better.?

  8. #8
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Quote Originally Posted by thebeginner
    And how about EXPLICITLY specifying READPAST in queries, ain't it better.?
    I can't see any way that it is better from the standpoint of avoiding lost data... You can do as you wish, just be forewarned that I've done exactly what you are describing and really, REALLY regret it. Indiscriminantly ignoring locking is an easy solution, but like most easy solutions, it can be VERY dangerous.

    If you set the transaction isolation level down, then everything in that transaction is processed at the lower level. I don't know of any way to hurt yourself that way. Setting some (even one) table to a lower locking level than the transaction isolation level is dangerous from the standpoint of "lost" data.

    -PatP

  9. #9
    Join Date
    Jul 2003
    Location
    San Antonio, TX
    Posts
    3,662
    Pat, relax, there are more important things in life...like beer Go get one, willya?!
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  10. #10
    Join Date
    Jun 2004
    Location
    Far away from heaven
    Posts
    194
    If you set the transaction isolation level down, then everything in that transaction is processed at the lower level. I don't know of any way to hurt yourself that way. Setting some (even one) table to a lower locking level than the transaction isolation level is dangerous from the standpoint of "lost" data.
    No doubt the teacher is always right

    and guru, i love you!

  11. #11
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Quote Originally Posted by rdjabarov
    Pat, relax, there are more important things in life...like beer Go get one, willya?!
    No can do this week. I'm at training, so I'm always the designated driver. This week needs "Commando Driving 301" skill almost all the time in Chicago and the burbs. They've done some quite "creative" things on both 88 and 294, making getting from place to place a whole new experience!

    Last night I actually made better time on Ogden (from 294 to Naperville Road!) than a friend did on I-88 !!!

    -PatP

Posting Permissions

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