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/