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