cannot update record, get stuck

Question

i have an issue with my current database with mysql.

i have over 100 connection waiting on a select record. when i execute:

show processlist;

the select query is a big query and the others are smaler queries and inserts, updates.

i have one database with 100 tables and the select is using 5 joins.

is there a way to temporary stop the process and let the other processes run, once all the processes are completed, then the select can continue running?

Answer 1

i will recommend to let the query do what it needs to do, if you stop any or kill processses or queries you might have data integrity errors which can lead to major errors.

BookOfZeus and tfb785 are right, first of all you probably have indexes errors. the explain will tell you what exactly is the problem and what to look for. for example if you have 5 joins and you get row counts like, 100,000 and 100 and 1 and 1 and 1 you will multiple 100,000 * 100 which can be super slow.

read carefully what the explain tells you and optimize your query based on it.

innodb can be a good option if you if the tables are accessed very often because its row locking insted of table locking for myisam.

I would say first try to optimize your query, maybe you wont need to alter your table engine to fix the issue. if you still have issues then you might consider moving to innodb.

Answer 2

Is there a way to temporary stop the process and let the other processes run, once all the processes are completed, then the select can continue running?

I believe you are using MyISAM as a storage engine.

In MyISAM, the read queries block the concurrent write queries. This makes sure that the writes won't change the data in process and you don't get half of your recordset updated and another half not updated.

There is no way to stop the SELECT query the way you ask: the writes could make the recordset returned inconsisent. While it may be not the actual case in your setup and theoretically writes might not affect the reads (say, different records are read and written), MyISAM is not aware of that and it always blocks whole tables, just in case.

If you need writes not to block reads, switch to InnoDB (though there are cases when writes will block reads too).

Answer 3

You can't stop the running query and to give time to the other queries. But there are three ways to solve your problem:

First way: Change the isolation level http://dev.mysql.com/doc/refman/5.6/en/mysql-acid.html These can also mean that you have to change your storage engine eg. to InnoDB.

Second way: Try to speedup your long running select with indices. This is a so called time memory trade off. You speedup your query with more memory for holding the indices trees.

Third way: Speedup your long running select with rearranging the query (joins and selects). Analyze the cost of all query parts.