On Wed, Sep 2, 2009 at 7:52 AM, Daniel Kinzler<[email protected]> 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 [email protected]) 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. _______________________________________________ Toolserver-l mailing list ([email protected]) https://lists.wikimedia.org/mailman/listinfo/toolserver-l Posting guidelines for this list: https://wiki.toolserver.org/view/Mailing_list_etiquette
