List:General Discussion« Previous MessageNext Message »
From:Jason J. W. Williams Date:December 22 2006 12:59am
Subject:Befuddled Why This Locks
View as plain text  
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_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.

Any help is greatly appreciated.

Thank you in advance.

Best Regards,
Jason
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