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: rhel4 mysql max rpm question
Hi Yong, all, Yong Lee wrote: Hi All, I noticed that there are RPM downloads for RHEL4 but that these do not contain the ndb storage engine (ie: these are standard builds). As such, I've resolved to just use the generic x86 rpm bundles that are offered but I'm curious if the ndb storage engine will ever get included into a RHEL4 RPM bundle? First: When asking, please tell us what version you refer to. Very often, the correct answer depends on the MySQL version affected. Second: I won't make a prediction, but I am not aware of any plans to change that. In what way is it important for you to have a RPM geared specifically to RHEL 4, as opposed to a generic one ? Jörg -- Joerg Bruehe, Senior Production Engineer MySQL AB, www.mysql.com -- 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]
