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