myisam place table-lock on table even when dealing with 'select' query?

Question

i am reading the book High Performance MySQL, it mentions:

performing one query per table uses table locks more efficiently: the queries 
will lock the tables invididually and relatively briefly, instead of locking 
them all for a longer time.

MyISAM places table-lock even when selecting something? can someone explain a little bit?

Answer 1

MyISAM has different kinds of locks. A SELECT operation places a READ LOCK on the table. There can be multiple active read locks at any given time, as long as there are no active WRITE LOCKS. Operations that modify the table, eg. INSERT, UPDATE, DELETE or ALTER TABLE place a WRITE LOCK on the table. Write lock can only be placed on a table when there are no active read locks; If there are active read locks, MyISAM queues the write lock to be activated as soon as all active read locks are expired.

Likewise when there's an active write lock, attempting to place a read lock on a table will queue the lock (and the associated query) until write locks have expired on the table.

Ultimately this all means that:

• You can have any number of active read locks (also called shared locks)
• You can only have one active write lock (also called an exclusive lock)

For more information see: http://dev.mysql.com/doc/refman/5.5/en/internal-locking.html

Answer 2

reko_t provided a good answer, I will try to elaborate on it:

Yes.

• You can have EITHER one writer or several readers
• Except there is a special case, called concurrent inserts. This means that you can have one thread doing an insert, while one or more threads are doing select (read) queries.
  • there are a lot of caveats doing this:
  • it has to be "at the end" of the table - not in a "hole" in the middle
  • Only inserts can be done concurrently (no updates, deletes)
• There is still contention on the single MyISAM key buffer. There is a single key buffer, protected by a single mutex, for the whole server. Everything which uses an index needs to take it (typically several times).

Essentially, MyISAM has poor concurrency. You can try to fake it, but it's bad whichever way you look at it. MySQL / Oracle has made no attempts to improve it recently (looking at the source code, I'm not surprised - they'd only introduce bugs).

If you have a workload with lots of "big" SELECTs which retrieve lots of rows, or are hard in some way, they may often overlap, this may seem ok. But a single row update or delete will block the whole lot of them.