Ted <r.ted.byers@rogers.com> wrote in
news:06d06dac-9beb-4e35-ae38-431b81e27c1e@c33g2000hsd.googlegroups.com:

> 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-b98da172
> dfb4@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
>> quoted
> text -
>>
>> - 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
>

At the most fundamental level, the database only knows about SQL.
MYSQL InnoDB does not really know or care about stored procedures.

Once again, the 1st DML starts a transaction.
This transaction terminates only with either a COMMIT or ROLLBACK.
You can paint this reality any color you desire, but it remains true.

If you ask the question a different way, you'll get the same answer
again, again, again.