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 <[EMAIL PROTECTED]> 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 [EMAIL PROTECTED]
-- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
