Michael Griffith writes:
> 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?
>
Hi!
The above should not happen.
You should look at entire processlist.
And check the size of your TMPDIR partition.
Regards,
Sinisa
____ __ _____ _____ ___ == MySQL AB
/*/\*\/\*\ /*/ \*\ /*/ \*\ |*| Sinisa Milivojevic
/*/ /*/ /*/ \*\_ |*| |*||*| mailto:[EMAIL PROTECTED]
/*/ /*/ /*/\*\/*/ \*\|*| |*||*| Larnaka, Cyprus
/*/ /*/ /*/\*\_/*/ \*\_/*/ |*|____
^^^^^^^^^^^^/*/^^^^^^^^^^^\*\^^^^^^^^^^^
/*/ \*\ Developers Team
---------------------------------------------------------------------
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