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
