Thanks to all of you.
The key was the 107488 rows. I restructured the query so that it started
with something smaller and it 1) runs faster (I'm guessing the reduced use
of temp space) and 2) did not seem to cause any locking problems (I will
test this under load today.)
I have attached a copy of the query which has been simplified in a couple of
ways (I don't really want every field from every row selected from every
table.) Also, the constants like organization_serial (16) and dates are
variables in the real version.
The explain now shows:
id table type possible_keys
key len ref rows
1 organization_shift ref PRIMARY, organization organization
4 const 5
1 organization_shift_start ref PRIMARY, organization_shift
organization_shift 4 organization_shift_serial 295
1 journal_entry_master ref PRIMARY, organization_shift_start
organization_shift_start 5 organization_shift_start_serial 52
Note that it now starts with 5 row, expands to 295 rows, etc. not the
100,000+ from before.
Again, thanks for all your help.
----- Original Message -----
From: "Baron Schwartz" <baron@stripped>
To: "Brent Baisley" <brenttech@stripped>
Cc: "Carl" <carl@stripped>; <mysql@stripped>
Sent: Tuesday, March 03, 2009 5:50 PM
Subject: Re: Select query locks tables in Innodb
> On Tue, Mar 3, 2009 at 12:35 PM, Brent Baisley <brenttech@stripped>
>> 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.
> InnoDB does not do lock escalation a la SQL Server etc. I'd look at
> Perrin's suggestions, I think they are likely to be the problem.
> More importantly, what is the query? :-)
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe: http://lists.mysql.com/mysql?unsub=1
# sales from collections
from organization_shift, organization_shift_start,
payments, budget_account_detail as ptfl_budget_account_detail, budget_account_master,
journal_entry_master, journal_entry_type, receipt_master, person, transactions
left join regs on regs.transactions_serial = transactions.transactions_serial
where organization_shift.organization_serial = 16
and organization_shift_start.organization_shift_serial =
and organization_shift_start.date_effective >= '2008-01-01'
and organization_shift_start.date_effective < '2009-03-31'
and journal_entry_master.organization_shift_start_serial =
and receipt_master.receipt_serial = transaction_event.receipt_serial
and transactions.transactions_serial = transaction_event.transactions_serial
and transactions.organization_serial = organization_shift.organization_serial
and person.person_serial = transactions.person_serial
and payment_to_fee_link_event.transaction_event_serial =
and payment_to_fee_link_budget_account_detail_link.payment_to_fee_link_event_serial =
and payment_to_fee_link_budget_account_detail_link.cash_basis_reporting_flag = 'Y'
and payment_to_fee_link.payment_to_fee_link_serial =
and payments.payments_serial = payment_to_fee_link.payments_serial
and payment_to_fee_link_budget_account_detail_link.date_effective >= '2008-01-01'
and payment_to_fee_link_budget_account_detail_link.date_effective < '2009-03-31'
and (payments.payment_type_code_serial in ( 1,2,3,4,5,8,24,6,7,12,13,23,25 ) #
or payment_to_fee_link_budget_account_detail_link.description='Apply available credit
to customer accounts receivable')
and fees_event.fees_serial = payment_to_fee_link.fees_serial
and ( fees_event.transaction_event_description_serial = 13 or
fees_event.transaction_event_description_serial = 2 )
and fees_budget_account_detail_link.fees_event_serial = fees_event.fees_event_serial
and fees_budget_account_detail_link.budget_account_detail_serial =
and ptfl_budget_account_detail.budget_account_detail_serial =
and budget_account_detail.budget_account_serial =
and budget_account_master.budget_account_type_serial = 5001
and journal_entry_master.journal_entry_master_serial =
and journal_entry_type.journal_entry_type_serial =
group by payment_to_fee_link_event.payment_to_fee_link_event_serial