a) InnoDB
or
b) Use Mysql's "Handler" Function:
http://dev.mysql.com/doc/mysql/en/handler.html
Quote:
There are several reasons to use the HANDLER interface instead of normal SELECT statements:
*
HANDLER is faster than SELECT:
o
A designated storage engine handler object is allocated for the HANDLER ... OPEN. The object is reused for the following HANDLER statements for the table; it need not be reinitialized for each one.
o
There is less parsing involved.
o
There is no optimizer or query-checking overhead.
o
The table doesn't have to be locked between two handler requests.
o
The handler interface doesn't have to provide a consistent look of the data (for example, dirty reads are allowed), so the storage engine can use optimizations that SELECT doesn't normally allow.
|
Basically what happens is that instead of one query locking the tables eg on a join the query is executed independently for each row and thus avoids locking.
this would be the professional solution ;)