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
