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