1

I have a query which takes a very long time to run but produces a new table in the end. The actual joins are not all that slow but it spends almost all of its time in 'copying to tmp table' and during this time the status of all other queries (which should be going to unrelated tables) is 'locked'. I am in the process of optimizing the long query but it is ok for it to take a while since it is an offline process, but it is NOT ok for it to stop all other queries which should not be related to it anyway. Does anyone know why all other unrelated queries would comeback as 'locked' and how to prevent this behavior?

Brian Tompsett - 汤莱恩
  • 5,753
  • 72
  • 57
  • 129
hackartist
  • 5,172
  • 4
  • 33
  • 48
  • MySQL doesn't lock all tables during copy, only the ones it's working with. Showing some queries would help. Perhaps you have some indexing / foreign key issues that lock tables that you believe aren't related. – Mikhail Nov 21 '11 at 21:15

2 Answers2

1

You are right in that "unrelated tables" shouldn't be affected. They shouldn't and to my knowledge they aren't.

There is a lot of information over at MySQL regarding locks, storage engines and ways of dealing with it.

To limit locks I would suggest that you write an application that reads all data needed to do this new table and simply have your application insert values to the new table. This might take longer but it will do it in smaller chunks and have less or no locks.

Good luck!

Andreas Wederbrand
  • 38,065
  • 11
  • 68
  • 78
-1

What is your MySQL Version?

Do you use MyISAM? MyISAM has a big LOCK problems on large SELECT commands.

Do you have a dedicated server? what is your maximum size for in-memory tables (look in my.cnf)?

Moshe L
  • 1,797
  • 14
  • 19