On Wed, Sep 2, 2009 at 7:52 AM, Daniel Kinzlerdaniel@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@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.