Re: MyISAM concurrent table access
Posted by:
Rick James
Date: January 11, 2012 10:33AM
Looking in from the outside (that is, not reading the code), it appears that MySQL is accessing many tables simultaneously, but in digging a little deeper, I never see a single connection _simultaneously_ accessing two tables, whether the same or different. MySQL sequentially processes virtually everything. For example, a JOIN (even a self-join) will read a row from one table, then read a row from the other (or same) table. In a UNION, it will fetch rows from one sub-select, then the other, then merger the results.
InnoDB has worker threads the run in parallel to handle I/O -- that's not really "one thread hitting multiple tables", at least not at the SQL level.
MyISAM will "lock" multiple tables when a single statement needs to access multiple tables. This, in a brute force way, prevents conflicts with other queries by delaying their start. A MyISAM query works something like this:
1. parse the query
2. sort the list of tables involved (sorting prevents deadlocks)
3. issue one internal "lock" to grab all the tables (with read or write lock, as needed). This step is delayed if some other connection has a conflicting lock.
4. perform the operation
5. release the locks.
(I don't know exactly when the query optimizer gets involved.)
Where are you headed with this question? Why does it matter?
MySQL can evaluate arbitrarily complex SQL statements, giving one the appearance of 'simultaneous'. It's only when you get into the details that you realize that even UNION and PARTITION fail to do useful work in parallel.
If this is about internals, which I assume includes THD, I suggest you get into onto the "internals" email list, not this user-oriented forum.