Re: SELECT queries Locking Problems
Posted by:
Rick James
Date: April 02, 2009 08:28PM
In PROCESSLIST...
"Sleep" means that the client is still connected, but is not executing any query at the moment. Perhaps it is busy processing someting. Perhaps it failed to disconnect. Your PHP code 'should' call disconnect, although there is usually no harm in not doing it.
"Unauthenticated user" is probably one of the early states of a client logging in.
Show us the INSERT/UPDATE/... query that is running (and presumably has the table locked).
MyISAM does only "table locking". That is, when a write operation starts to act on a table, it locks the table, not allowing anything else to touch the table. If you INSERT one row, that is so brief that it would be hard to ever notice it. If you DELETE 10,000 rows in a single statement, that will keep the lock for a long time. If a SELECT were to run during the DELETE, it would probably get quite confused, possible crash because of pointers being changed by the DELETE. MyISAM uses code that is fast and simple. InnoDB uses complex code to allow SELECT to run while DELETE is running. But even there, you can get into deadlocks, aborted statements, etc.