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

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