From: MAS!
Date: March 11 2008 5:24pm
Subject: lock write and sql_cache
List-Archive: http://lists.mysql.com/mysql/211770
Message-Id: <16D4B6EA-4C15-48B1-8701-B2795B0EDE29@gmail.com>
MIME-Version: 1.0 (Apple Message framework v753)
Content-Type: text/plain; charset=US-ASCII; delsp=yes; format=flowed
Content-Transfer-Encoding: 7bit

does the "lock table" on MySQL (I'm using 5.0.22 or 5.0.45) works  
even for a cached result?

this is my scenario, (I'm using MyIsam tables)

thread 1:
LOCK TABLE foo WRITE;

thread 2:
SELECT * FROM foo ;
(locked)

thread 1:
UNLOCK TABLES;

thread 2:
result of SELECT * ..

and that's is, IMHO, correct; btw, since the result of the select is  
in the cache, it seems I'm unable to 'lock' again the table:

thread 1:
LOCK TABLE foo WRITE;

thread 2:
SELECT * FROM foo ;
I have the result of the select :(

the only way I found to avoid that is use the SQL_NO_CACHE (then  
SELECT SQL_NO_CACHE FROM foo)

is that correct?! there is a way to avoid to specify "SQL_NO_CACHE" ?
or there is something wrong ?!
thank you in avance.
bye bye
marco