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 <c...@etrak-plus.com> 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 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=arch...@jab.org
