List:General Discussion« Previous MessageNext Message »
From:Carl Date:March 4 2009 6:38pm
Subject:Re: Select query locks tables in Innodb
View as plain text  
Under stress (having transaction entered), the query shows that it is still 
locking the tables.  I rewrote the query and tested it step by step but 
could not tell whether tyhe partially complete query was locking tables 
because it ran so fast.  However, when I had all the pieces in the query 
(copy attached), I could easily see it was locking tables using the Server 
Monitor in Navicat.

Explain (copy as text and copy as Excel attached) seems to indicate that it 
is fairly good although the first step does get quite a few rows.

Does anyone have any ideas?

TIA,

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
>
> 

select *
	from payment_to_fee_link_budget_account_detail_link, journal_entry_master,
journal_entry_type,
		payment_to_fee_link_event, payment_to_fee_link, fees, fees_event,
fees_budget_account_detail_link, person, transactions
			left join regs on regs.transactions_serial = transactions.transactions_serial,
		transaction_event, receipt_master, budget_account_detail, budget_account_detail as
ptfl_budget_account_detail, budget_account_master
	where journal_entry_master.organization_serial = 16
		and journal_entry_master.date_effective >= '2008-01-01'
		and journal_entry_master.date_effective < '2009-03-31'
		and journal_entry_type.journal_entry_type_serial =
journal_entry_master.journal_entry_type_serial
		and payment_to_fee_link_budget_account_detail_link.journal_entry_master_serial =
journal_entry_master.journal_entry_master_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 payment_to_fee_link_event.payment_to_fee_link_event_serial =
payment_to_fee_link_budget_account_detail_link.payment_to_fee_link_event_serial
		and payment_to_fee_link.payment_to_fee_link_serial =
payment_to_fee_link_event.payment_to_fee_link_serial
		and transaction_event.transaction_event_serial =
payment_to_fee_link_event.transaction_event_serial 
		and fees.fees_serial = payment_to_fee_link.fees_serial
		and transactions.transactions_serial = fees.transactions_serial
		and person.person_serial = transactions.person_serial
		and receipt_master.receipt_serial = transaction_event.receipt_serial
		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 budget_account_detail.budget_account_detail_serial =
fees_budget_account_detail_link.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_master.budget_account_serial =
budget_account_detail.budget_account_serial
		and budget_account_master.budget_account_type_serial = 5001


Attachment: [application/vnd.ms-excel] temp.XLS
1 SIMPLE journal_entry_master ref PRIMARY,organization,journal_entry_type_serial,date_effective organization 4 const 56926 Using where 1 SIMPLE journal_entry_type eq_ref PRIMARY PRIMARY 4 PRODUCTION.journal_entry_master.journal_entry_type_serial 1 1 SIMPLE payment_to_fee_link_budget_account_detail_link ref journal_entry,budget_account_detail_serial,event,date_effective journal_entry 4 PRODUCTION.journal_entry_master.journal_entry_master_serial 1 Using where 1 SIMPLE ptfl_budget_account_detail eq_ref PRIMARY PRIMARY 4 PRODUCTION.payment_to_fee_link_budget_account_detail_link.budget_account_detail_serial 1 1 SIMPLE payment_to_fee_link_event eq_ref PRIMARY,payment,transaction_event PRIMARY 4 PRODUCTION.payment_to_fee_link_budget_account_detail_link.payment_to_fee_link_event_serial 1 1 SIMPLE payment_to_fee_link eq_ref PRIMARY,fees PRIMARY 4 PRODUCTION.payment_to_fee_link_event.payment_to_fee_link_serial 1 1 SIMPLE transaction_event eq_ref PRIMARY,receipt PRIMARY 4 PRODUCTION.payment_to_fee_link_event.transaction_event_serial 1 1 SIMPLE receipt_master eq_ref PRIMARY PRIMARY 4 PRODUCTION.transaction_event.receipt_serial 1 1 SIMPLE fees eq_ref PRIMARY,transactions PRIMARY 4 PRODUCTION.payment_to_fee_link.fees_serial 1 1 SIMPLE transactions eq_ref PRIMARY,person PRIMARY 4 PRODUCTION.fees.transactions_serial 1 1 SIMPLE person eq_ref PRIMARY PRIMARY 4 PRODUCTION.transactions.person_serial 1 1 SIMPLE regs ref transaction transaction 4 PRODUCTION.transactions.transactions_serial 1 1 SIMPLE fees_event ref PRIMARY,fees,event fees 4 PRODUCTION.payment_to_fee_link.fees_serial 1 Using where 1 SIMPLE fees_budget_account_detail_link ref budget_account_detail_serial,fees_event fees_event 4 PRODUCTION.fees_event.fees_event_serial 1 1 SIMPLE budget_account_detail eq_ref PRIMARY,budget_account PRIMARY 4 PRODUCTION.fees_budget_account_detail_link.budget_account_detail_serial 1 1 SIMPLE budget_account_master eq_ref PRIMARY PRIMARY 4 PRODUCTION.budget_account_detail.budget_account_serial 1 Using where
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