[UPHPU] autocommit() locking tables
breece at doba.com
Fri Oct 10 09:39:17 MDT 2008
Rusty Keele wrote:
> 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:
> 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?
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
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
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.
More information about the UPHPU