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
_______________________________________________
UPHPU mailing list
[email protected]
http://uphpu.org/mailman/listinfo/uphpu
IRC: #uphpu on irc.freenode.net
