From: Perrin Harkins
Date: March 4 2009 6:49pm
Subject: Re: Select query locks tables in Innodb
List-Archive: http://lists.mysql.com/mysql/216587
Message-Id: <66887a3d0903041049je9f0354wb5199c947ded9250@mail.gmail.com>
MIME-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit

2009/3/4 Carl <carl@stripped>:
> However, when I had all the pieces in the query
> (copy attached), I could easily see it was locking tables using the Server
> Monitor in Navicat.

I don't know what that is, but I think you'd better look at something
closer to the bone, like SHOW INNODB STATUS.

> Explain (copy as text and copy as Excel attached) seems to indicate that it
> is fairly good although the first step does get quite a few rows.

EXPLAIN isn't really relevant to table locking.  InnoDB tables should
never let readers block writers for a simple SELECT.

> Does anyone have any ideas?

Did you check that your tables are InnoDB?  Are you running some kind
of crazy isolation level?

- Perrin