Ted <r.ted.byers@rogers.com> wrote in
news:fd6e497b-1f1d-4e02-9bc3-638e29c1162e@e23g2000prf.googlegroups.com:

> On Mar 2, 4:10*pm, Jerry Stuckle <jstuck...@attglobal.net> wrote:
>> Ted wrote:
>> > I get the following error:
>>
>> > ERROR 1205 (HY000) at line 20: Lock wait timeout exceeded; try
>> > restarting transaction
>>
>> > This ONLY happenes when I start 2 to 4 concurrent processes. *The
>> > scripts involved typically take half a day to a day to complete
>> > (they're rather intensive analysis and simulation scripts), but
>> > they should not interact. *While they do write to the same tables,
>> > they spend no more than 5% of their time writing to the tables.
>> > *Rather, most of their time is spent either reading data or number
>> > crunching. I would think that since the machine in question has a
>> > quad core processor, it should be able to handle four concurrent
>> > sessions even though these sessions will each max out one core.
>> > *For some reason, running a single script at a time maxes out one
>> > core leaving the other three mostly idle. *Running four maxes out
>> > all of the cores, but inevitably the above error kills one session
>> > at a time until there is only one left, which runs to completion.
>>
>> > What can I do to fix this so I can successfully run four of my
>> > scripts at a time?
>>
>> > BTW: If it matters, this is with MySQL 5.0.45 running on Windows
>> > 2003 server (both 64 bit).
>>
>> > Thanks
>>
>> > Ted
>>
>> Sounds like you're getting into a deadlock situation. It doesn't
>> matter what percentage of time any transaction spends writing to the
>> database; deadlocks can occur any time two or more transactions are
>> updating two or more tables each, unless care is taken in how the
>> updates are performed
> .
>>
>> First of all, try this wikipedia article for an explanation on
>> deadlocks - it's pretty accurate:
>> *http://en.wikipedia.org/wiki/Deadlock
>>
>> It also gives some good hints on avoiding deadlocks, such as ensuring
>> all updates are done in the same order in all transactions.
>>
>> --
>> =================
>> Remove the "x" from my email address
>> Jerry Stuckle
>> JDS Computer Training Corp.
>> jstuck...@attglobal.net
>> ==================- Hide quoted text -
>
>>
>> - Show quoted text -
>
> OK. There should be no locks requested when reading the tables since
> none of the tables being read are ever changed. There are five tables
> being written to, but we can guarantee that they are written to in the
> same order. After all, the script code itself is identical between
> errant processes. They are merely given different parameters that
> result in them working with different subsets of the data.
>
> I understand the idea of deadlock. I am just surprised that that
> could hit me in this case. The tables that are written each have an
> autoincremented primary key, which I suppose creates the potential
> for contention, but I would have thought that row level locks would
> suffice to prevent problems with deadlock. Why would it be necessary
> to lock the whole table when all that is happening is a row or two is
> added in a given session?
>
> Are there any options I could set within my SQL scripts, or as
> commandline arguments, that could help resolve this problem? Can
> individual stored procedures, or portions thereof, be protected so
> that they can't be interrupted once started? After all, I know which
> stored procedures and individual statements do the writing, and each
> should only take a few milliseconds to complete once started. How
> long can it take to insert half a dozen records into a table with only
> five columns? Or is it a question of tinkering with how locks on
> tables, records or autoincremented keys are handled. If so, how?
> Where do I look in the manual?
>
> Thanks
>
> Ted
>

It might depend upon which engine type is being used for tables being
written & if/when COMMIT is being issued.