[UPHPU] autocommit() locking tables

[Ben Reece]

Rusty Keele wrote:
> Hi,
>
>   I am trying to find out if using mysqli::autocommit() automatically locks the DB tables that are being used by a query.
>   
>   Here's my problem: I am looking over some PHP code that is causing intermittent dead lock conditions in a MySQL DB (using InnoDB engine.)  Basically the code creates an array of SQL queries (inserts and updates), turns autocommit off, runs all the queries, then commits them:
>
>             $conn->autocommit(false);
>             $this->PersistCache();
>             $conn->commit();
>
>       The PersistCache() method just loops through the array and executes each query.  I have looked through the code and don't see an explicit LOCK statement anywhere, so I'm thinking that maybe autocommit() is somehow locking the tables that are being updated.  I have checked out the PHP documentation and it says nothing about locking.  Any ideas?
>
> Thanks,
> -Rusty
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.

Ben