List:General Discussion« Previous MessageNext Message »
From:Jason J. W. Williams Date:December 22 2006 4:57pm
Subject:Re: Befuddled Why This Locks
View as plain text  
Hi Dan,

I guess I'm curious why this query acquires a read lock. Is it because
its in a transaction?  Thank you very much in advance!

-J

On 12/22/06, Dan Nelson <dnelson@stripped> wrote:
> In the last episode (Dec 21), Jason J. W. Williams said:
> > If someone could suggest some advice/guidance I would be very
> > grateful. I'm trying to determine why the following SELECT query
> > table locks the "bad_behavior" table referenced the query.
> >
> > 'bad_behavior' is MyISAM
> > 'c' is InnoDB
> > 'a' is InnoDB
> >
> > Query:
> > select item_p from (select inet_ntoa(ip) as
> > item_p,sum(if(class_factor>0.75,1,0)) as info,count(*) as count from c
> > join a on c.mid=a.mid where c.date > subdate(now(),interval 6 hour)
> &&
> > ip not in (select address from bad_behavior where score >= 6 ) group
> > by ip) as t1 where info >= 5 && info/count >= 0.75
> >
> > The befuddling part is that the bad_behavior table is table locked
> > (preventing updates/inserts) until the query above ends. The version
> > of MySQL is 5.0.27.
>
> I don't see anything wrong here.  "bad_behavior" is a MyISAM table
> which uses table locks, so when your select is running, it grabs a read
> lock on the table and blocks other writers.  See the chapters at
> http://dev.mysql.com/doc/refman/5.0/en/locking-issues.html for more
> detail and some tips on how to insert data even on read-locked tables.
>
> --
>         Dan Nelson
>         dnelson@stripped
>
Thread
Befuddled Why This LocksJason J. W. Williams22 Dec
  • Re: Befuddled Why This LocksDan Nelson22 Dec
    • Re: Befuddled Why This LocksJason J. W. Williams22 Dec
      • Re: Befuddled Why This LocksDan Nelson22 Dec