brion at pobox
Jan 27, 2003, 8:47 AM
Post #2 of 2
On lun, 2003-01-27 at 05:55, Erik Moeller wrote:
> I occasionally get the following SQL error when loading pages:
> MySQL returned error "1205: Lock wait timeout exceeded;
> Try restarting transaction".
> The user there reports that he has this problem with a very large
> database and persistent connections. My summary of the ideas
> from the thread:
> 1) innodb_lock_wait_timeout in my.cnf could be adjusted.
Attached is a copy of our my.cnf. Anyone who knows what the many magic
numbers do is welcome to suggest improvements!
innodb_lock_wait_timeout is 50; if this is 50 seconds, we sure as heck
do *not* want anything to take that long.
> 2) In certain conditions, when AUTOCOMMIT is set to 0 or a
> table has been locked and not unlocked, every UPDATE/INSERT
> requires a COMMIT to unlock the row after the transaction.
> It may be advised to set AUTOCOMMIT to 1 before every
> action, or to encapsulate our SQL queries in BEGIN/COMMIT
Autocommit is on by default.
I've put a couple of things additionally into begin/commit wrappers, but
not everything. And as I recall, persistent connections can still be
buggered if the connection gets _left_ partway through a transaction.
> 3) Don't use persistent connections.
I've temporarily disabled them to see what happens. I also changed
site_stats back to myisam for now.
Be back from class in a few hours... hopefully the site will still be
-- brion vibber (brion @ pobox.com)