Hi, I seem to be experiencing similair problems to what Sam Lam reported earlier this month (Subject: tables locked during long compound INSERT/DELETE?).
What I've observed is that when I'm issuing an UPDATE on a table that also has SELECT queries being executed on it, everything seems to clog up. The UPDATE seems to me to take longer than usual and the SELECTs linger around and pile up and sit in the ``Copying to tmp table'' state until the UPDATE completes. Sometimes this clog lasts for so long that it's better just to kill a bunch of the threads. A quick excerpt from a ``show processlist'' when this happened: ... *************************** Command: Query Time: 0 State: updating Info: UPDATE ... SET ... WHERE ... AND ... AND ... *************************** Command: Query Time: 922 State: Copying to tmp table Info: SELECT DISTINCT(...), ... FROM ... JOIN ... WHERE ... *************************** Command: Query Time: 915 State: Copying to tmp table Info: SELECT DISTINCT(...), ... FROM ... JOIN ... WHERE ... *************************** ... There's ~115 more of those SELECTs hanging around, too. This is under Linux 2.2.19 and MySQL 3.23.41 (yes, we'll update soon, but the ChangeLog doesn't seem to say anything relevant to this problem). Is this a likely a MySQL bug or a configuration error or ...? Thanks. P.S. It appears that the SELECTs don't have to be against the same table that's being UPDATEd, but I haven't pinned that down so it's mere speculation. --------------------------------------------------------------------- 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
