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
