List:General Discussion« Previous MessageNext Message »
From:Brent Baisley Date:March 3 2009 5:35pm
Subject:Re: Select query locks tables in Innodb
View as plain text  
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.

Is that how many records you want to return? That seems like a lot.
Maybe reworking your query may help. Heck, post the sizeable query.
You've been spending weeks on it.

Brent Baisley

On Tue, Mar 3, 2009 at 10:53 AM, Carl <carl@stripped> wrote:
> I have been wrestling with this problem for a couple of weeks and have been unable to
> find a solution.
>
> The MySQL version is  5.0.37 and it is running on a Slackware Linux 11 box.
>
> The problem:
>
> A query that is selecting data for a report locks the files that it accesses forcing
> users who are attempting to enter transactions to wait until the select query is
> finished.
>
> The query is sizable so I have not included it here (I can if that would be helpful.)
>  Explain shows (abbreviated):
>
> id   select_type    table            
>    type        possible keys        
>                    key_len   ref
>        rows         Extra
> 1    SIMPLE         transactions     ref
>           PRIMARY,person,organization    4  
>          const    107448      *
> 1    SIMPLE         person        
>     eq_ref      PRIMARY            
>                     4  
>  person_serial    1
> 1    SIMPLE         regs        
>         ref           transaction  
>                          
>    4    transactions_serial  1
> 1    SIMPLE         transaction_event ref    
>    PRIMARY, transaction, receipt    4    transactions_serial
>    1
> 1    SIMPLE         receipt_master    ref
>         PRIMARY              
>                   4  
>  receipt_serial            1
>
> The 107448 rows are the transactions for the organization I am reporting.  The
> person is linked directly to the transaction.  During the select query, the person
> table is locked thereby stopping updates to any person in the table.
>
> I have always thought a select is only a read and would, therefore, not lock any
> tables.
>
> Anyone have any ideas?
>
> TIA,
>
> Carl
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