All engines allow some degree of parallel activity, except when there
are conflicts. That is, two different connections running two different
queries that touch two different tables can run in parallel. That is,
except for collisions such as memory allocation and disk I/O.
Even on the same table, competing reads on the same table can generally
run in parallel. But if a write (INSERT, DELETE, etc) is in the mix,
then there is a change of a lock that will stall another connection.
InnoDB allows more concurrent access than MyISAM because it locks at the
row level, while MyISAM locks at the table level. "Write" locks tend to
be "exclusive"; this means that a connection that has such a lock (on an
InnoDB row, or a MyISAM table), blocks all other actions on that
row/table. (Yeah, this is overly simplistic, especially in light of
"transaction isolation levels" in InnoDB.)
A single SQL statement never(?) (except for NGD Cluster) uses more than
one thread. (Well, OK, InnoDB has helper threads for I/O.)
On 11/22/11 6:34 AM, Hiromichi Watari wrote:
> Hi Steve, Mark, Stewart and Rick
> Thank you for your feedback.
> From what you are saying, is it fair to say that InnoDB serializes table access
> among competing requests (perhaps to simplify design to support transactions) while MyISAM
> allow concurrent access ?
> My experiment results seem to be confirming the assumptions.
> Thank you all for your help.
Rick James - MySQL Geek