Re: Befuddled Why This Locks
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_factor0.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]
Re: Befuddled Why This Locks
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_factor0.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]
Re: Befuddled Why This Locks
In the last episode (Dec 22), Jason J. W. Williams said: 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! You mean as opposed to a write lock? Only inserts and updates need write locks. Or do you mean why is it locking at all? All selects need to lock the data they're reading to prevent inserts or updates from changing the records out from under the select while its running. MyISAM does it by locking the entire table. -- Dan Nelson [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Befuddled Why This Locks
Hi All, 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_factor0.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. Any help is greatly appreciated. Thank you in advance. Best Regards, Jason -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
