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