On Wed, Sep 2, 2009 at 7:52 AM, Daniel Kinzler<daniel(a)brightbyte.de> wrote:
Today we had a problem with the toolserver.namespace
table being blocked on
sql-s1. After some digging, I think I have identified the cause: there was a
very long running solect on that table. By itself, that wouldn't be a problem.
But once a day, the table gets updated from the master copy on zedler - so the
slow select blocked that update, and the pending update blocked any further
select. This way, all tools trying to use toolserver.namespace on sql-s1 were
effectively dead.
To avoid this, please don't run very slow queries on the toolserver.* tables. If
you have an idea how this kind of lockout can be avoided, perhabs be a smarter
way to copy the table from the master, I'd be happy to hear it. Currently, i
sumpl create a copy with mysqldump and then improt it.
(copied from my e-mail to admins(a)toolserver.org)
That doesn't normally happen with InnoDB, AFAIK. Selects don't block
updates; the selects just read the old, un-updated rows as the update
proceeds, using MVCC. Uncommitted updates don't even necessarily
block ordinary selects AFAIK -- again, the select can just get a
consistent read of the old version. See here for details (and other
pages in the section):
http://dev.mysql.com/doc/refman/5.1/en/innodb-locks-set.html
http://dev.mysql.com/doc/refman/5.1/en/innodb-lock-modes.html
So an ordinary select should not take out any locks, and therefore not
block any other statement at all from occurring except maybe things
like ALTER TABLE. However, selects do take out locks if used with
LOCK IN SHARE MODE or FOR UPDATE, or if they're run at SERIALIZABLE
isolation level. I would guess one of those was the culprit, without
being able to see the exact query.