One more note.
Perrin asked if I was using any select... for update. The answer is no,
neither in the select query that seems to be locking the tables nor in the
queries that are processing transactions.
Surprisingly, one of the tables that reports being locked is never accessed
in the report query. It is a foreign key on one of the files that is used.
----- Original Message -----
From: "Baron Schwartz" <baron@stripped>
To: "Brent Baisley" <brenttech@stripped>
Cc: "Carl" <carl@stripped>; <mysql@stripped>
Sent: Tuesday, March 03, 2009 5:50 PM
Subject: Re: Select query locks tables in Innodb
> On Tue, Mar 3, 2009 at 12:35 PM, Brent Baisley <brenttech@stripped>
>> A SELECT will/can lock a table. It almost always does in MyISAM (no
>> insert/updates), almost never does in InnoDB. There is an exception to
>> every rule. The problem is most likely in the 107488 rows part of the
>> query. That's too many rows for InnoDB to keep a version history on so
>> it's likely just locking the table.
> InnoDB does not do lock escalation a la SQL Server etc. I'd look at
> Perrin's suggestions, I think they are likely to be the problem.
> More importantly, what is the query? :-)
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe: http://lists.mysql.com/mysql?unsub=1