Using SHOW PROCESSLIST or mysqladmin proc
Every time a table reports status of 'copying to tmp table' all other UPDATE queries are
locked, even in unrelated tables.
Query #1: SELECT * FROM a JOIN B WHERE.... Status: copying to tmp table
Query #2: UPDATE C SET x=x+1 WHERE...... Status: locked
Even though table C is not used is query #2 it is locked until query #1 completed. This
can cause huge problems when hundreds of quick updates are locked for more than a few
Why does a tmp table on one table lock another?
How can I prevent this from happening?