List:General Discussion« Previous MessageNext Message »
From:Michael Griffith Date:January 19 2001 4:17pm
Subject:temp tables lock unrelated tables
View as plain text  
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?


Thread
temp tables lock unrelated tablesMichael Griffith19 Jan
  • Re: temp tables lock unrelated tablesSinisa Milivojevic19 Jan
  • Re: temp tables lock unrelated tablesMichael Griffith19 Jan
    • Re: temp tables lock unrelated tablesSinisa Milivojevic20 Jan