Why doesn't LOCK TABLES [table] WRITE prevent table reads?

Question

According to http://dev.mysql.com/doc/refman/5.0/en/lock-tables.html if I lock a table for writing in mysql, no-one else should have access until it's unlocked. I wrote this script, loaded either as script.php or script.php?l=1 depending on what you want to do:

if ($_GET['l'])
{
    mysqli_query("LOCK TABLES mytable WRITE");
    sleep(10);
    mysqli_query("UNLOCK TABLES");
}
else
{
    $res=mysqli_query("SELECT * FROM mytable");
    // Print Result
}

If I load script.php?l=1 in one browser window then, while it's sleeping, I should be able to load script.php in another window and it should wait until script.php?l=1 is finished, right?

Thing is, script.php loads right away, even though script.php?l=1 has a write lock. If I try to insert in script.php then it does wait, but why is the SELECT allowed?

Note: I am not looking for a discussion on whether to use LOCK TABLES or not. In fact I am probably going to go with a transaction, I am investigating that now, right now I just want to understand why the above doesn't work.

Answer 1

This happens because of query caching. There is a cache result available that doesn't 'affect' the lock, so the results are returned.

This can be avoided by adding the "SQL_NO_CACHE" keyword to the select:

SELECT SQL_NO_CACHE * FROM mytable

Answer 2

The point of LOCK is so that other sessions do not modify the table while you are using it during your specific session.

The reason that you are able to perform the SELECT query is because that's still considered part of the same MySQL session, even if you open up a new window.