Professional Web Applications Themes

Deadlock issue - MySQL

Hi all, Apologies if this has been discussed before - a brief Google search did not reveal anything of note, so I thought I'd go ahead and ask. Just a few days before the go-live, and absolutely expectedly, I am getting errors I have never seen before: Error Code 1213, Transaction Aborted due to Deadlock. I have to use isolation level Serializable, and now my transactions are immediately aborted if they would produce a deadlock. I should add our transactions are very short (but critical). I am somewhat surprised by this, in our concurrency tests we never encountered anything the ...

  1. #1

    Default Deadlock issue

    Hi all,

    Apologies if this has been discussed before - a brief Google search did not
    reveal anything of note, so I thought I'd go ahead and ask.

    Just a few days before the go-live, and absolutely expectedly, I am getting
    errors I have never seen before: Error Code 1213, Transaction Aborted due to
    Deadlock. I have to use isolation level Serializable, and now my transactions
    are immediately aborted if they would produce a deadlock. I should add our
    transactions are very short (but critical).

    I am somewhat surprised by this, in our concurrency tests we never encountered
    anything the like before and I assumed the transaction manager could handle the
    issue, delaying one transaction the tiniest moment until it could acquire a
    lock. But well, go-live is nearing, and it's time for the freaky errors, I guess.

    So my question: is this normal mySQL behaviour? Our DB admin assures me he
    didn't change any timers or make any changes at all to the setup. The only
    change that I made to my model before commencing the new test series was to
    select two more cells than before, but I cannot believe this could be an issue.

    As a workaround, I am catching the 1213 and wait a very short moment before
    re-running the transaction. I still tend to think that the transaction manager
    should be able to resolve such issues itself - does it?

    Thanks for any answers,
    Ralph
    Ralph Guest

  2. #2

    Default Re: Deadlock issue

    Ralph Holz wrote: 

    No, by the time the transaction manage detects a deadlock situation, it
    has already occurred. Just delaying one transaction will not affect the
    deadlock situation. One transaction or the other must be backed out.
     

    That's how every RDB works. Don't now how adding two more columns to
    your test would affect it, but this has been a problem waiting to
    happen. You're lucky it occurred before you went live.
     

    That will work for now, but will increase overhead on the server and, as
    your server gets more loaded, this will get much worse.

    You need to fix your applications. I know it's tough to do just before
    you go live. But would you rather rewrite after you've gone live?

    Things like:

    COMMIT or ROLLBACK as soon as possible. That is, as soon as your
    transaction is complete. Don't do multiple transactions before you
    COMMIT or ROLLBACK.

    When accessing multiple tables in the same transaction (but different
    SQL statements), always access them in the same order. For instance, if
    you need to update tablea and tableb, always update tablea first, then
    tableb (or vice versa). Don't to tablea first in one program and tableb
    first in another.

    Do NOT use LOCK TABLE unless you're going to be updating the entire
    table. I know someone in MySQL suggests using table level locks, but
    quite frankly I doubt they have ever worked on even a mildly loaded
    production. I've been working with RDB's since the mid 80's, starting
    with DB2. I've been involved with some systems which pushed 100K
    access/s. (yes - per second, on mainframes). Anyone in any of these
    systems who used a table lock to bypass a deadlock situation would be
    without a job, even on much lighter loaded systems. It really kills
    concurrency.,

    Here are some other suggestions:

    http://dev.mysql.com/doc/refman/5.1/en/innodb-deadlocks.html


    --
    ==================
    Remove the "x" from my email address
    Jerry Stuckle
    JDS Computer Training Corp.
    net
    ==================
    Jerry Guest

  3. #3

    Default Re: Deadlock issue

    Jerry,

    thanks a lot for your suggestions, much appreciated! I am still a learner in the
    DB business.
     

    I have been wondering about this - all the transaction manager would have to do
    would be to hold back the second transaction since the first one will release
    the locks very soon, and no dead lock would need to occur. That kind of issue
    should be easy to detect in our scenarioa, and easy to negotiate. But I admit I
    know next to nothing about the logic in transaction managers.
     

    I agree. Really strange that this problem did not occur when we did our
    concurrency tests - they were a whole lot more thorough than the tests I did
    last week. But there is a law that says the most interesting problems will occur
    on the last day before the go-live...
     [/ref]
     

    Yes, it's definitely a hot spot. Fortunately, this is ok for us. The application
    in question is a course registration software which will never administrate more
    than a couple of hundred students during the same semester - of whom rarely will
    two ever click the register button at exactly the same time. I don't really
    expect there to be a concurrency problem in practice, but I do believe in doing
    things properly.

    That said - what is the best practice to resolve deadlock and transaction
    collision issues? Especially in this case?
     

    Check!
     

    Check too. :-)
     

    And that too. Seems we're good already...
     

    Again, thanks for your help.

    Ralph
    Ralph Guest

  4. #4

    Default Re: Deadlock issue

    Ralph Holz wrote: 
    >
    >
    > I have been wondering about this - all the transaction manager would have to do
    > would be to hold back the second transaction since the first one will release
    > the locks very soon, and no dead lock would need to occur. That kind of issue
    > should be easy to detect in our scenarioa, and easy to negotiate. But I admit I
    > know next to nothing about the logic in transaction managers.
    >[/ref]
    Not at all.

    The second transaction is trying to get a lock the first one only owns.
    But delaying this won't help. The deadlock occurs because the second
    transaction already has a lock on something else, and the first
    transaction is looking for it. The second transaction must release the
    lock before the first one can proceed.
     
    >
    >
    > I agree. Really strange that this problem did not occur when we did our
    > concurrency tests - they were a whole lot more thorough than the tests I did
    > last week. But there is a law that says the most interesting problems will occur
    > on the last day before the go-live...
    >[/ref]

    Deadlocks are always timing issues. They will not occur if, for
    instance, you only run one transaction at a time. They will only occur
    when you get a critical mix of transactions, and generally occur more
    often under heavier load.
     [/ref]
    >

    >
    >
    > Yes, it's definitely a hot spot. Fortunately, this is ok for us. The application
    > in question is a course registration software which will never administrate more
    > than a couple of hundred students during the same semester - of whom rarely will
    > two ever click the register button at exactly the same time. I don't really
    > expect there to be a concurrency problem in practice, but I do believe in doing
    > things properly.
    >
    > That said - what is the best practice to resolve deadlock and transaction
    > collision issues? Especially in this case?
    >[/ref]

    Check the link I gave you. It gives you several options for resolving
    deadlocks. And google for deadlock.
     
    >
    >
    > Check!
    >

    >
    >
    > Check too. :-)
    >

    >
    >
    > And that too. Seems we're good already...
    >

    >
    >
    > Again, thanks for your help.
    >
    > Ralph[/ref]


    --
    ==================
    Remove the "x" from my email address
    Jerry Stuckle
    JDS Computer Training Corp.
    net
    ==================
    Jerry Guest

Similar Threads

  1. Deadlock?
    By Philip Mak in forum Ruby
    Replies: 3
    Last Post: September 24th, 11:18 AM
  2. Deadlock
    By Lewis in forum IBM DB2
    Replies: 1
    Last Post: July 31st, 05:19 AM
  3. COM Deadlock in W2k but not NT 4.0
    By Steve in forum ASP Components
    Replies: 1
    Last Post: July 24th, 04:27 PM
  4. Deadlock in Solaris 8
    By Indira in forum Sun Solaris
    Replies: 1
    Last Post: July 24th, 02:53 AM
  5. Sql Server Deadlock
    By csaran in forum Microsoft SQL / MS SQL Server
    Replies: 4
    Last Post: July 3rd, 12:54 PM

Bookmarks

Posting Permissions

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

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139