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