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