List:General Discussion« Previous MessageNext Message »
From:Carl Date:March 5 2009 10:44am
Subject:Re: Select query locks tables in Innodb
View as plain text  
I really appreciate the time you have taken to help me with this problem.

I will be out of the office until around 1:00PM and will try your 
suggestions.

I did attach a copy of the query but it may have been stripped somewhere 
along the line so I have placed it in line below.

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

TIA,

Carl

----- Original Message ----- 
From: "Baron Schwartz" <baron@stripped>
To: "Carl" <carl@stripped>
Cc: <mysql@stripped>
Sent: Wednesday, March 04, 2009 8:11 PM
Subject: Re: Select query locks tables in Innodb


I don't think it locks the tables.  The behavior may be similar, but I
seriously doubt that's what's happening.  Take a snapshot of SHOW
INNODB STATUS while this is going on.  And use mysqladmin debug and
check the error log.  Then put those in some pastebin and send us the
link.  And realize that you've only given us bits and snippets of
information about this -- you still haven't given us SHOW CREATE TABLE
or even shown us the query that's running.  There's not a lot I can do
to really help you with this other than assume that you are wrong :)

Your version is definitely affected by that bug, which I can't find --
I am using the wrong search terms and can't find the right ones to
find the bug.

5.0.37 is a very buggy version and I would upgrade regardless if I
were you, to the latest 5.0 release.  You might be surprised at how
much that changes things.

Baron

On Wed, Mar 4, 2009 at 3:33 PM, Carl <carl@stripped> wrote:
> Baron,
>
> I am using 5.0.37.
>
> While it may be true that there is a bug that shows tables as being locked
> when they really aren't, I do not think that applies here. I do know that
> when a table shows a status of 'Locked' in the Navicat Server Monitor that
> the transaction which created and is processing the query comes to a
> complete stop until the report query (the one I am trying to straighten 
> out
> or understand) is finished. For example, the report query is reading from
> several files, e.g., receipt_master, if a user tries to check out (which
> requires an insert into the receipt_master table), they are stopped until
> the report query finishes and query on that table shows in Navicat as
> waiting for lock ('Locked'.)
>
> Since the report query is only reading data, I am puzzled why it locks the
> tables. Any ideas?
>
> TIA,
>
> Carl
>
>
> ----- Original Message ----- From: "Baron Schwartz" <baron@stripped>
> To: "Carl" <carl@stripped>
> Cc: <mysql@stripped>
> Sent: Wednesday, March 04, 2009 2:29 PM
> Subject: Re: Select query locks tables in Innodb
>
>
>> Carl,
>>
>> Locked status in SHOW PROCESSLIST and a table being locked are
>> different. There is a bug in MySQL that shows Locked status for
>> queries accessing InnoDB tables in some cases. What version of MySQL
>> are you using?
>>
>> The table is not really locked, you're just seeing that as a side
>> effect of whatever's really happening.
>>
>> Baron
>>
>> On Wed, Mar 4, 2009 at 2:01 PM, Carl <carl@stripped> wrote:
>>>
>>> I did check that all tables are Innodb.
>>>
>>> I was using the Navicat Server Monitor because I know that when I see 
>>> the
>>> monitor reporting a status of locked during an attempted query, that 
>>> user
>>> comes to a complete halt until the lock is cleared (usually by the bad
>>> query
>>> finishing.)
>>
>
>



-- 
Baron Schwartz, Director of Consulting, Percona Inc.
Our Blog: http://www.mysqlperformanceblog.com/
Our Services: http://www.percona.com/services.html

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