Hi,
In InnoDB, the behavior depends on the transaction isolation level.
First, SELECT FOR UPDATE acquires exclusive (write) locks, while SELECT ... LOCK IN SHARE MODE creates shared (read) locks. The MySQL manual InnoDB manual has a tranditional "row lock compatibility" table which shows how read and exclusive locks interact.
There are actually four types of locks: IS, IX, S, X (intention shared, intention exclusive, shared, exclusive) and locks can be taken at the row level or at the table level(ie, LOCK TABLES can be used to lock an entire table with an X lock). IX and IS locks are obtained BEFORE an X or S lock. For example, certain types of ALTER TABLE must lock the entire table for reads and writes. In this case, there will be an X lock on the table, and all IX and IS locks will block, meaning no row locks can be acquired during the alter.
Now that that is covered, you have to think about how locks are actually acquired, which is actually through INDEX TRAVERSAL. You see, rows aren't really locked, but index entries are. This is why it is very important to index your queries well. For example, if you do "SELECT count(*) FROM my_table WHERE order_status='open' FOR UPDATE", and `order_status` is not indexed, then the PRIMARY KEY will be traversed to answer the query. ALL THE ROWS OF THE PK WILL BE LOCKED, because the rows which are traversed are locked.
In REPEATABLE-READ, these locks are HELD FOR THE DURATION OF THE CONNECTION (or until commit/rollback is issued). If you are using READ-COMMITED, then locks THAT DID NOT MATCH, are released after the STATEMENT completes (locks for rows that did match are kept for the duration of the transaction, like all other locks.
READ-COMMITTED has certain advantages too, such as not having to take extra "gap" locks and "next key locks" in order to make sure DML statements such as SELECT .. FOR UPDATE and INSERT .. SELECT return repeatable results (this is not necessary in READ-COMMITTED, or lower).
See: