List:General Discussion« Previous MessageNext Message »
From:Carl Date:March 4 2009 6:54pm
Subject:Re: Select query locks tables in Innodb
View as plain text  
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.

TIA,

Carl


----- 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> 
> wrote:
>> 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
>
> 

Thread
Select query locks tables in InnodbCarl3 Mar
  • Re: Select query locks tables in InnodbBrent Baisley3 Mar
    • Re: Select query locks tables in InnodbBaron Schwartz3 Mar
  • Re: Select query locks tables in InnodbPerrin Harkins3 Mar
  • Re: Select query locks tables in InnodbCarl4 Mar
  • Re: Select query locks tables in InnodbCarl4 Mar
    • Re: Select query locks tables in InnodbPerrin Harkins4 Mar
  • Re: Select query locks tables in InnodbCarl4 Mar
  • Re: Select query locks tables in InnodbCarl4 Mar
    • Re: Select query locks tables in InnodbBaron Schwartz4 Mar
    • Re: Select query locks tables in InnodbBaron Schwartz4 Mar
  • Re: Select query locks tables in InnodbCarl4 Mar
    • Re: Select query locks tables in InnodbBaron Schwartz5 Mar
  • Re: Select query locks tables in InnodbCarl5 Mar
  • Re: Select query locks tables in InnodbCarl12 Mar
    • Re: Select query locks tables in InnodbBrent Baisley12 Mar
    • Re: Select query locks tables in InnodbPerrin Harkins13 Mar
  • Re: <Solved> Select query locks tables in InnodbCarl25 Mar