SEO

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 Holz wrote:
> 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.
>

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.

> 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.
>

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.

> 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

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/...deadlocks.html


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

Jerry,

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

> 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.

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.

> 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.

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...

>> 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

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

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?

> 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.

Check!

> 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.

Check too. :-)

> Do NOT use LOCK TABLE unless you're going to be updating the entire

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

> http://dev.mysql.com/doc/refman/5.1/...deadlocks.html

Again, thanks for your help.

Ralph

Ralph Holz wrote:
> Jerry,
>
> thanks a lot for your suggestions, much appreciated! I am still a learner in the
> DB business.
>
>
>>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.
>
>
> 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.
>
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.

>
>>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.
>
>
> 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...
>

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.

>
>>>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
>
>
>>That will work for now, but will increase overhead on the server and, as
>>your server gets more loaded, this will get much worse.
>
>
> 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 the link I gave you. It gives you several options for resolving
deadlocks. And google for deadlock.

>
>>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.
>
>
> Check!
>
>
>>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.
>
>
> Check too. :-)
>
>
>>Do NOT use LOCK TABLE unless you're going to be updating the entire
>
>
> And that too. Seems we're good already...
>
>
>>http://dev.mysql.com/doc/refman/5.1/...deadlocks.html
>
>
> Again, thanks for your help.
>
> Ralph


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