List:Internals« Previous MessageNext Message »
From:Rick James Date:November 22 2011 6:05pm
Subject:Re: Concurrent table access from InnoDB
View as plain text  
No.

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.
> Hiromichi
>

-- 
Rick James - MySQL Geek

Thread
Concurrent table access from InnoDBhwatari18 Nov
  • Re: Concurrent table access from InnoDBMySQL)18 Nov
    • Re: Concurrent table access from InnoDBRick James18 Nov
      • Re: Concurrent table access from InnoDBMySQL)18 Nov
        • Re: Concurrent table access from InnoDBHiromichi Watari19 Nov
          • Re: Concurrent table access from InnoDBMark Leith21 Nov
            • Re: Concurrent table access from InnoDBStewart Smith22 Nov
              • Re: Concurrent table access from InnoDBRick James22 Nov
                • Re: Concurrent table access from InnoDBHiromichi Watari22 Nov
                  • Re: Concurrent table access from InnoDBRick James22 Nov
                    • Re: Concurrent table access from InnoDBHiromichi Watari23 Nov
RE: Concurrent table access from InnoDBSteve Hardy21 Nov