All of the locked queries are happening because another query has locked them out. That is, until a certain query finishes, none of the others can execute. See the manual for more info on table locking. You can generally fix this simply by fixing your bad queries (adding proper indexes, etc), but if this will not work, switching to InnoDB may be the most beneficial for you.
http://www.mysql.com/doc/T/a/Table_locking.html Steve Meyers > -----Original Message----- > From: Dan Uyemura [mailto:[EMAIL PROTECTED]] > Sent: Friday, October 19, 2001 3:31 PM > To: mySQL List > Subject: Locked Processes Taking MySQL down.... > > > Hello, > > I am seeing rather frequently our mySQL DB Freezes up. It runs > the back end > of a web site that gets decent traffic. When I check the process list for > the DB, there is a bunch of processes all locking that table > sitting there. > I normally disable connections from the Cold Fusion server, > meaning I do not > let the Cold Fusion server leave an open connection to the database. When > it is done with it's transaction, it disconnects the connection, > so usually > when I check the process list, there is no more than 5 processes > at a time. > > the reason I turned of leaving open connections is because I was seeing a > problem where idle connections would be left in a sleep state and never > disconnected... so I would end up using up all of the connections. once > this would happen, I would have to restart mySQL to purge off the > "zombied" > threads. I have a hunch the connections should be allowed to > stay, and the > CF server should be smart enough to continually use the same > connection, but > since that did not seem to be the case, I disabled it. > > Anyhow, does anyone know why processes would stack up, all with a state of > "locked"? Is there any countermeasure I can take, or something I am doing > wrong? > > Dan Uyemura > IT > GSociety, Inc. > > 7060 Hollywood Blvd. #800 > Hollywood, CA 90028 > > (v) (323) 337-0141 > > GSociety family of companies: > www.gaywired.com > www.lesbianation.com > www.qtmagazine.com > www.dance1.net > www.ghighway.com > > -----Original Message----- > From: Mark Maunder [mailto:[EMAIL PROTECTED]] > Sent: Friday, October 19, 2001 2:01 PM > To: [EMAIL PROTECTED] > Subject: MYSQL 4.0 bug with fulltext (case change) updates > > > Hi, > > I think this is a bug. The script to recreate the problem is included > below. This problem appears consistently as long as there's a fulltext > index and a regular index on the same field and you do an update to > change the case of a single char. It doesn't matter if the fulltext > index includes other fields. If the regular index is removed, then after > the insert (where the case of one char in a field is changed) the table > is briefly marked as crashed, and then seems to automagically fix itself > (which is worrying). > > The bug also occurs when you change the case of a single char in a > varchar field and add a word to the sentence e.g.: > 'experience with c required' changes to 'experience with C is required' > also causes the problem. > > Thanks as always, > > ~mark > ps: If anyone knows of a workaround please let me know. thanks. > > __BEGIN__ > #This works fine on mysql 3 but crashes the table on 4 alpha > CREATE TABLE tester ( > id int(11) auto_increment, > title varchar(100) default '', > PRIMARY KEY (id), > #If you comment out the following index, then the table is briefly > marked as crashed > # but appears to recover. > KEY ind5 (title), > #The fulltext index appears to be the root of this prob. > FULLTEXT KEY FT1 (title) > ) TYPE=MyISAM; > > insert into tester (title) values ('this is a test'); > update tester set title='this is A test' where id=1; > check table tester; > __END__ > > Gives us: > Table Op Msg_type Msg_text > freeusall.tester check warning Table is marked as crashed > freeusall.tester check error Checksum for key: 2 doesn't > match checksum for records > freeusall.tester check error Corrupt > > We're running MySQL 4.0 alpha on Linux kernel 2.4.2 intel. Using MyISAM > tables. > > > > > > --------------------------------------------------------------------- > Before posting, please check: > http://www.mysql.com/manual.php (the manual) > http://lists.mysql.com/ (the list archive) > > To request this thread, e-mail <[EMAIL PROTECTED]> > To unsubscribe, e-mail > <[EMAIL PROTECTED]> > Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php > > > > > --------------------------------------------------------------------- > Before posting, please check: > http://www.mysql.com/manual.php (the manual) > http://lists.mysql.com/ (the list archive) > > To request this thread, e-mail <[EMAIL PROTECTED]> > To unsubscribe, e-mail > <[EMAIL PROTECTED]> > Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php > > --------------------------------------------------------------------- Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
