Results 1 to 5 of 5
  1. #1
    Join Date
    Sep 2003
    Posts
    2

    Unhappy Unanswered: Deadlock! Please help!

    Hi,
    I am getting deadlock error in my app. Deadlock occurs
    when a select query and an update query are fired at the same
    time. I tried changing table lock mechanism from allpages to
    datapages. But there is no change. Here is the exact scenario -

    I have T1 and T2 tables. Select joins on them for selecting data
    and Update updates both the table.


    Thanks is advance,
    Vyms

  2. #2
    Join Date
    Nov 2002
    Location
    Switzerland
    Posts
    524
    If the change of the locking schema doesn't help you, that's mean that your're really experimenting a deadlock in the used records (and not only the used pages).

    => Improve your transactions (as small as possible, as quick as possible, ...)

  3. #3
    Join Date
    Sep 2003
    Location
    London
    Posts
    12
    Another thing you might want to investigate is using a different isolation level. For instance if you changed your select statement to be "select ... at isolation 0" it should ignore the fact that an update is taking place, and therefore not deadlock (hopefully....).
    The disadvantage with this is that you would be reading "dirty data" - ie it might change due to the update.
    Will

  4. #4
    Join Date
    Sep 2003
    Posts
    2

    Re: Deadlock! Please help!

    I am not sure about your requirements but try to use 'update followed by select' instead of 'select followed by update' if possible, to avoid deadlocks.



    Originally posted by vyms
    Hi,
    I am getting deadlock error in my app. Deadlock occurs
    when a select query and an update query are fired at the same
    time. I tried changing table lock mechanism from allpages to
    datapages. But there is no change. Here is the exact scenario -

    I have T1 and T2 tables. Select joins on them for selecting data
    and Update updates both the table.


    Thanks is advance,
    Vyms

  5. #5
    Join Date
    Sep 2003
    Posts
    2
    My application is a multiuser application and I do not have any
    control on the sequence of sqls fired. But its really ridiculous that
    sybase is causing deadlock between a simple update transaction(single row) and an unchained select transaction.
    When I checked the query plan for the select query I found that Sybase is doing a table scan for the select on the table that has 100% contention which finally causes deadlock.
    I can not simplify/reduce my transactions. One transaction contains an update query that is supposed to modify a single row and other transaction is a select query.

    Thanks for all the replies. More info is appreciated.

    --vyms

Posting Permissions

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