Mailing List Archive: Wikipedia: Wikitech

InnoDB problem with row locking

Index | Next | Previous | View Threaded

e.moeller at fokus

Jan 27, 2003, 6:55 AM

Post #1 of 2 (41 views)
Permalink

InnoDB problem with row locking

I occasionally get the following SQL error when loading pages:
UPDATE LOW_PRIORITY site_stats SET
ss_total_views=(ss_total_views+1),ss_total_edits=(ss_total_edits),
ss_good_articles=(ss_good_articles) WHERE ss_row_id=1
from within function "SiteStatsUpdate::doUpdate".
MySQL returned error "1205: Lock wait timeout exceeded;
Try restarting transaction".

This may directly be related to our hang problems. It appears to be
an InnoDB problem according to a thread in

http://www.faqchest.com/prgm/mysql-l/mysql-02/mysql-0201/mysql-020115/

("InnoDB: Lock wait timeout exceeded")

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.
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
statements.
3) Don't use persistent connections.

These locking problems are most likely to occur with our
site_stats table because it only has one row.

I assume these problems are solvable, as Kuro5hin is a very large
dynamic site with InnoDB based tables. Any other ideas?

Regards,

Erik
--
FOKUS - Fraunhofer Insitute for Open Communication Systems
Project BerliOS - http://www.berlios.de

brion at pobox

Jan 27, 2003, 8:47 AM

Post #2 of 2 (43 views)
Permalink

Re: InnoDB problem with row locking [In reply to]

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
> statements.

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
up. :)

-- brion vibber (brion @ pobox.com)

Attachments:

my.cnf (2.65 KB)

signature.asc (0.18 KB)

Index | Next | Previous | View Threaded

Interested in having your list archived? Contact Gossamer Threads