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.