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