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