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. For example: 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 seconds Why does a tmp table on one table lock another? How can I prevent this from happening?
- Re: temp tables lock unrelated tables Michael Griffith
- Re: temp tables lock unrelated tables Sinisa Milivojevic
- Re: temp tables lock unrelated tables Michael Griffith
- Re: temp tables lock unrelated tables Sinisa Milivojevic
- temp tables lock unrelated tables Michael Griffith
- Re: temp tables lock unrelated tables Gerald L. Clark
- Re: temp tables lock unrelated tables Richard Ellerbrock
- Re: temp tables lock unrelated tables Michael Griffith
- Re: temp tables lock unrelated tables Sinisa Milivojevic
- Re: temp tables lock unrelated tables Michael Griffith
- query optimization suggestion Michael Griffith