Re: The SELECT does.
SELECT ... FROM is a consistent read, reading a snapshot of the database and setting no locks unless the transaction isolation level is set to SERIALIZABLE. For SERIALIZABLE level, this sets shared next-key locks on the index records it encounters.
A consistent read means that InnoDB uses its multi-versioning to present to a query a snapshot of the database at a point in time. The query see the changes made by exactly those transactions that committed before that point of time, and no changes made by later or uncommitted transactions. The exception to this rule is that the query sees the changes made by the transaction itself that issues the query.
That is from MySQL's documentation, and sounds very much like the systems mentioned elseware in this discussion. This is from the InnoDB documentation, other engines might be different. But the above says that a normal SELECT should never issue any locks on the table at all.
The only really bad MySQL locking issue that I'm aware of is this.
A locking read, an UPDATE, or a DELETE generally set record locks on every index record that is scanned in the processing of the SQL query. It does not matter if there are WHERE conditions in the query that would exclude the row from the result set of the query. InnoDB does not remember the exact WHERE condition, but only knows which index ranges were scanned.
That is rather ugly, and could easily cause performance problems.
Jay
