[UPHPU] autocommit() locking tables

[Roberto Mello]

On Fri, Oct 10, 2008 at 9:39 AM, Ben Reece <breece at doba.com> wrote:

>
>>  Rusty,
>   A deadlock is usually caused by 2 queries in conflict, not table locks.
>  If you did explicitly lock the tables, you'd probably eliminate the
> deadlocks.  See
> http://www.xaprb.com/blog/2006/08/08/how-to-deliberately-cause-a-deadlock-in-mysql/for a simple example on how a deadlock can happen through normal use.
>   See http://dev.mysql.com/doc/refman/5.0/en/innodb-deadlocks.html for how
> to cope with deadlocks, or
> http://dev.mysql.com/doc/refman/5.0/en/innodb-transaction-model.html for
> more on InnoDB in general.
>
>   We had the same issue a month or so ago, and I took this advice from the
> mySQL page: "Always be prepared to re-issue a transaction if it fails due to
> deadlock. Deadlocks are not dangerous. Just try again."  Basically, whenever
> we get a deadlock, we just re-issue the query up to 10 times before throwing
> a failure notice.
>

That's interesting. We never have deadlocks in our production PostgreSQL
servers, and when I ran Oracle in my previous job I don't recall that being
any more common.

The "deadlocks are not dangerous" statement is particularly worrysome to
me... It indicates to me that InnoDB's locking model often creates
deadlocks. Does InnoDB not implement MVCC?

Roberto

-- 
http://blog.divisiblebyfour.org/