List:General Discussion« Previous MessageNext Message »
From:Carl Date:March 4 2009 12:37pm
Subject:Re: Select query locks tables in Innodb
View as plain text  
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.

Carl


----- 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> 
> wrote:
>> 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
(select *
	from organization_shift, organization_shift_start,
transaction_event,payment_to_fee_link_event,payment_to_fee_link,
payment_to_fee_link_budget_account_detail_link,fees_budget_account_detail_link,
fees_event, budget_account_detail, 
			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 =
organization_shift.organization_shift_serial
		and organization_shift_start.date_effective >= '2008-01-01'
		and organization_shift_start.date_effective < '2009-03-31'
		#$P!{organizationShiftStartQuery}
		and journal_entry_master.organization_shift_start_serial =
organization_shift_start.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 
		#$P!{itemSerials}
		and person.person_serial = transactions.person_serial
		and payment_to_fee_link_event.transaction_event_serial =
transaction_event.transaction_event_serial
		and payment_to_fee_link_budget_account_detail_link.payment_to_fee_link_event_serial =
payment_to_fee_link_event.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 =
payment_to_fee_link_event.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 )	#
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 =
budget_account_detail.budget_account_detail_serial
		and ptfl_budget_account_detail.budget_account_detail_serial =
payment_to_fee_link_budget_account_detail_link.budget_account_detail_serial
		and budget_account_detail.budget_account_serial =
budget_account_master.budget_account_serial
		and budget_account_master.budget_account_type_serial = 5001
		and journal_entry_master.journal_entry_master_serial =
payment_to_fee_link_budget_account_detail_link.journal_entry_master_serial
		and journal_entry_type.journal_entry_type_serial =
journal_entry_master.journal_entry_type_serial
	group by payment_to_fee_link_event.payment_to_fee_link_event_serial
)


Thread
Select query locks tables in InnodbCarl3 Mar
  • Re: Select query locks tables in InnodbBrent Baisley3 Mar
    • Re: Select query locks tables in InnodbBaron Schwartz3 Mar
  • Re: Select query locks tables in InnodbPerrin Harkins3 Mar
  • Re: Select query locks tables in InnodbCarl4 Mar
  • Re: Select query locks tables in InnodbCarl4 Mar
    • Re: Select query locks tables in InnodbPerrin Harkins4 Mar
  • Re: Select query locks tables in InnodbCarl4 Mar
  • Re: Select query locks tables in InnodbCarl4 Mar
    • Re: Select query locks tables in InnodbBaron Schwartz4 Mar
    • Re: Select query locks tables in InnodbBaron Schwartz4 Mar
  • Re: Select query locks tables in InnodbCarl4 Mar
    • Re: Select query locks tables in InnodbBaron Schwartz5 Mar
  • Re: Select query locks tables in InnodbCarl5 Mar
  • Re: Select query locks tables in InnodbCarl12 Mar
    • Re: Select query locks tables in InnodbBrent Baisley12 Mar
    • Re: Select query locks tables in InnodbPerrin Harkins13 Mar
  • Re: <Solved> Select query locks tables in InnodbCarl25 Mar