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

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.
jstucklex@attglobal.net
==================

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

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.

Ted <r.ted.byers@rogers.com> wrote:
>
> I understand the idea of deadlock. I am just surprised that that
> could hit me in this case.

There is no indication that you hit a deadlock. The error you got says
"lock wait timeout". This means an application waited too long for
exclusive access to a certain resource (probably because that resource
was used by another application). Now you can either increase the lock
wait timeout or find out what the blocking transaction was and make it
faster.

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

This is all too speculative.

Most important question of all: what storage engine are you using for
those tables? MyISAM is notorious for it's lack of record-wise locking.
InnoDB is generally more suited for concurrent writes (or writes
concurring with heavy reads). But it's no silver bullet.

If it is InnoDB (I guess it is) then use the InnoDB lock monitor to
get detailed information on locking conflicts:
http://dev.mysql.com/doc/refman/5.0/...b-monitor.html


Rules of thumb:

1. keep your transactions short
2. don't do heavy number crunching while in a transaction (see 1.)
3. don't start a transaction and go for a coffee (see 1.)
4. keep your transactions short (did I say so already?)


XL
--
Axel Schwenke, Support Engineer, MySQL AB

Online User Manual: http://dev.mysql.com/doc/refman/5.0/en/
MySQL User Forums: http://forums.mysql.com/

On Mar 2, 6:26*pm, Axel Schwenke <axel.schwe...@gmx.de> wrote:
> Ted <r.ted.by...@rogers.com> wrote:
>
> > I understand the idea of deadlock. *I am just surprised that that
> > could hit me in this case.
>
> There is no indication that you hit a deadlock. The error you got says
> "lock wait timeout". This means an application waited too long for
> exclusive access to a certain resource (probably because that resource
> was used by another application). Now you can either increase the lock
> wait timeout or find out what the blocking transaction was and make it
> faster.
>
> > 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.
>
> This is all too speculative.
>
> Most important question of all: what storage engine are you using for
> those tables? MyISAM is notorious for it's lack of record-wise locking.
> InnoDB is generally more suited for concurrent writes (or writes
> concurring with heavy reads). But it's no silver bullet.
>
> If it is InnoDB (I guess it is) then use the InnoDB lock monitor to
> get detailed information on locking conflicts:http://dev.mysql.com/doc/refman/5.0/...b-monitor.html
>
> Rules of thumb:
>
> 1. keep your transactions short
> 2. don't do heavy number crunching while in a transaction (see 1.)
> 3. don't start a transaction and go for a coffee (see 1.)
> 4. keep your transactions short (did I say so already?)
>
> XL
> --
> Axel Schwenke, Support Engineer, MySQL AB
>
> Online User Manual:http://dev.mysql.com/doc/refman/5.0/en/
> MySQL User Forums: *http://forums.mysql.com/

OK. Thanks guys,

I am using InnoDB for everything.

I have not explicitly used transactions in these scripts. I didn't
think I needed them because even when scripts run in parallel, they
can never insert or update the same records (there are no deletes in
these scripts). I assumed that I'd have one session for each script,
but never thought about transactions in these scripts, especially
since I didn't explicitly create any.

Thanks again,

Ted

Axel Schwenke wrote:
> Ted <r.ted.byers@rogers.com> wrote:
>> I understand the idea of deadlock. I am just surprised that that
>> could hit me in this case.
>
> There is no indication that you hit a deadlock. The error you got says
> "lock wait timeout". This means an application waited too long for
> exclusive access to a certain resource (probably because that resource
> was used by another application). Now you can either increase the lock
> wait timeout or find out what the blocking transaction was and make it
> faster.
>
>> 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.
>
> This is all too speculative.
>
> Most important question of all: what storage engine are you using for
> those tables? MyISAM is notorious for it's lack of record-wise locking.
> InnoDB is generally more suited for concurrent writes (or writes
> concurring with heavy reads). But it's no silver bullet.
>
> If it is InnoDB (I guess it is) then use the InnoDB lock monitor to
> get detailed information on locking conflicts:
> http://dev.mysql.com/doc/refman/5.0/...b-monitor.html
>
>
> Rules of thumb:
>
> 1. keep your transactions short
> 2. don't do heavy number crunching while in a transaction (see 1.)
> 3. don't start a transaction and go for a coffee (see 1.)
> 4. keep your transactions short (did I say so already?)
>
>
> XL
> --
> Axel Schwenke, Support Engineer, MySQL AB
>
> Online User Manual: http://dev.mysql.com/doc/refman/5.0/en/
> MySQL User Forums: http://forums.mysql.com/
>

Axel,

Maybe not in the lab this doesn't show any indication of a deadlock.
But I can assure you, in real life this is a typical message you get
from a deadlock condition. I've seen it or it's equivalent from other
databases too many times.

The biggest reason for a lock timeout is a deadlock of some type.
Transactions don't normally lock the same results for any length of time
unless there is a deadlock condition.

Of course, there are exceptions - they occur maybe 0.1% of the time.

--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstucklex@attglobal.net
==================

Ted <r.ted.byers@rogers.com> wrote in
news:59d109d7-5a6d-4c20-8080-b98da172dfb4@e60g2000hsh.googlegroups.com:

> On Mar 2, 6:26*pm, Axel Schwenke <axel.schwe...@gmx.de> wrote:
>> Ted <r.ted.by...@rogers.com> wrote:
>>
>> > I understand the idea of deadlock. *I am just surprised that that
>> > could hit me in this case.
>>
>> There is no indication that you hit a deadlock. The error you got
>> says "lock wait timeout". This means an application waited too long
>> for exclusive access to a certain resource (probably because that
>> resource was used by another application). Now you can either
>> increase the lock wait timeout or find out what the blocking
>> transaction was and make it faster.
>>
>> > 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.
>>
>> This is all too speculative.
>>
>> Most important question of all: what storage engine are you using for
>> those tables? MyISAM is notorious for it's lack of record-wise
>> locking. InnoDB is generally more suited for concurrent writes (or
>> writes concurring with heavy reads). But it's no silver bullet.
>>
>> If it is InnoDB (I guess it is) then use the InnoDB lock monitor to
>> get detailed information on locking
>> conflicts:http://dev.mysql.com/doc/ref
> man/5.0/en/innodb-monitor.html
>>
>> Rules of thumb:
>>
>> 1. keep your transactions short
>> 2. don't do heavy number crunching while in a transaction (see 1.)
>> 3. don't start a transaction and go for a coffee (see 1.)
>> 4. keep your transactions short (did I say so already?)
>>
>> XL
>> --
>> Axel Schwenke, Support Engineer, MySQL AB
>>
>> Online User Manual:http://dev.mysql.com/doc/refman/5.0/en/
>> MySQL User Forums: *http://forums.mysql.com/
>
> OK. Thanks guys,
>
> I am using InnoDB for everything.
>
> I have not explicitly used transactions in these scripts. I didn't
> think I needed them because even when scripts run in parallel, they
> can never insert or update the same records (there are no deletes in
> these scripts). I assumed that I'd have one session for each script,
> but never thought about transactions in these scripts, especially
> since I didn't explicitly create any.
>

InnoDB is a transaction aware engine.

Any DML (INSERT, UPDATE, DELETE) starts a transaction.
Subsequent DML are part of the same transaction.
The transaction continues until the next COMMIT or ROLLBACK is issued.

While there is a downside, one option is to periodic COMMITs.

On Mar 2, 10:28*pm, "Ana C. Dent" <anaced...@hotmail.com> wrote:
> Ted <r.ted.by...@rogers.com> wrote innews:59d109d7-5a6d-4c20-8080-b98da172dfb4@e60g2000hsh.googlegroups.com:
>
>
>
>
>
> > On Mar 2, 6:26*pm, Axel Schwenke <axel.schwe...@gmx.de> wrote:
> >> Ted <r.ted.by...@rogers.com> wrote:
>
> >> > I understand the idea of deadlock. *I am just surprised that that
> >> > could hit me in this case.
>
> >> There is no indication that you hit a deadlock. The error you got
> >> says "lock wait timeout". This means an application waited too long
> >> for exclusive access to a certain resource (probably because that
> >> resource was used by another application). Now you can either
> >> increase the lock wait timeout or find out what the blocking
> >> transaction was and make it faster.
>
> >> > 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.
>
> >> This is all too speculative.
>
> >> Most important question of all: what storage engine are you using for
> >> those tables? MyISAM is notorious for it's lack of record-wise
> >> locking. InnoDB is generally more suited for concurrent writes (or
> >> writes concurring with heavy reads). But it's no silver bullet.
>
> >> If it is InnoDB (I guess it is) then use the InnoDB lock monitor to
> >> get detailed information on locking
> >> conflicts:http://dev.mysql.com/doc/ref
> > man/5.0/en/innodb-monitor.html
>
> >> Rules of thumb:
>
> >> 1. keep your transactions short
> >> 2. don't do heavy number crunching while in a transaction (see 1.)
> >> 3. don't start a transaction and go for a coffee (see 1.)
> >> 4. keep your transactions short (did I say so already?)
>
> >> XL
> >> --
> >> Axel Schwenke, Support Engineer, MySQL AB
>
> >> Online User Manual:http://dev.mysql.com/doc/refman/5.0/en/
> >> MySQL User Forums: *http://forums.mysql.com/
>
> > OK. *Thanks guys,
>
> > I am using InnoDB for everything.
>
> > I have not explicitly used transactions in these scripts. *I didn't
> > think I needed them because even when scripts run in parallel, they
> > can never insert or update the same records (there are no deletes in
> > these scripts). *I assumed that I'd have one session for each script,
> > but never thought about transactions in these scripts, especially
> > since I didn't explicitly create any.
>
> InnoDB is a transaction aware engine.
>
> Any DML (INSERT, UPDATE, DELETE) starts a transaction.
> Subsequent DML are part of the same transaction.
> The transaction continues until the next COMMIT or ROLLBACK is issued.
>
> While there is a downside, one option is to periodic COMMITs.- Hide quotedtext -
>
> - Show quoted text -

Now that is interesting. Does it matter if the subsequent DML are in
different stored procedures?

Wait a minute, the bulk of my analyses are performed using DML
statements. That could mean really really long transactions, even
though these just read data and plug the results of specific
intermediate calculations into a temporary table, especially for
larger datasets.

Would the recommendation be to add explicit START TRANSACTION and
COMMIT statements?

Thanks.

Ted

Ted <r.ted.byers@rogers.com> wrote in
news:06d06dac-9beb-4e35-ae38-431b81e27c1e@c33g2000hsd.googlegroups.com:

> On Mar 2, 10:28*pm, "Ana C. Dent" <anaced...@hotmail.com> wrote:
>> Ted <r.ted.by...@rogers.com> wrote
>> innews:59d109d7-5a6d-4c20-8080-b98da172
> dfb4@e60g2000hsh.googlegroups.com:
>>
>>
>>
>>
>>
>> > On Mar 2, 6:26*pm, Axel Schwenke <axel.schwe...@gmx.de> wrote:
>> >> Ted <r.ted.by...@rogers.com> wrote:
>>
>> >> > I understand the idea of deadlock. *I am just surprised that
>> >> > that could hit me in this case.
>>
>> >> There is no indication that you hit a deadlock. The error you got
>> >> says "lock wait timeout". This means an application waited too
>> >> long for exclusive access to a certain resource (probably because
>> >> that resource was used by another application). Now you can either
>> >> increase the lock wait timeout or find out what the blocking
>> >> transaction was and make it faster.
>>
>> >> > 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.
>>
>> >> This is all too speculative.
>>
>> >> Most important question of all: what storage engine are you using
>> >> for those tables? MyISAM is notorious for it's lack of record-wise
>> >> locking. InnoDB is generally more suited for concurrent writes (or
>> >> writes concurring with heavy reads). But it's no silver bullet.
>>
>> >> If it is InnoDB (I guess it is) then use the InnoDB lock monitor
>> >> to get detailed information on locking
>> >> conflicts:http://dev.mysql.com/doc/ref
>> > man/5.0/en/innodb-monitor.html
>>
>> >> Rules of thumb:
>>
>> >> 1. keep your transactions short
>> >> 2. don't do heavy number crunching while in a transaction (see 1.)
>> >> 3. don't start a transaction and go for a coffee (see 1.)
>> >> 4. keep your transactions short (did I say so already?)
>>
>> >> XL
>> >> --
>> >> Axel Schwenke, Support Engineer, MySQL AB
>>
>> >> Online User Manual:http://dev.mysql.com/doc/refman/5.0/en/
>> >> MySQL User Forums: *http://forums.mysql.com/
>>
>> > OK. *Thanks guys,
>>
>> > I am using InnoDB for everything.
>>
>> > I have not explicitly used transactions in these scripts. *I didn't
>> > think I needed them because even when scripts run in parallel, they
>> > can never insert or update the same records (there are no deletes
>> > in these scripts). *I assumed that I'd have one session for each
>> > script, but never thought about transactions in these scripts,
>> > especially since I didn't explicitly create any.
>>
>> InnoDB is a transaction aware engine.
>>
>> Any DML (INSERT, UPDATE, DELETE) starts a transaction.
>> Subsequent DML are part of the same transaction.
>> The transaction continues until the next COMMIT or ROLLBACK is
>> issued.
>>
>> While there is a downside, one option is to periodic COMMITs.- Hide
>> quoted
> text -
>>
>> - Show quoted text -
>
> Now that is interesting. Does it matter if the subsequent DML are in
> different stored procedures?
>
> Wait a minute, the bulk of my analyses are performed using DML
> statements. That could mean really really long transactions, even
> though these just read data and plug the results of specific
> intermediate calculations into a temporary table, especially for
> larger datasets.
>
> Would the recommendation be to add explicit START TRANSACTION and
> COMMIT statements?
>
> Thanks.
>
> Ted
>

At the most fundamental level, the database only knows about SQL.
MYSQL InnoDB does not really know or care about stored procedures.

Once again, the 1st DML starts a transaction.
This transaction terminates only with either a COMMIT or ROLLBACK.
You can paint this reality any color you desire, but it remains true.

If you ask the question a different way, you'll get the same answer
again, again, again.