CODEWITHSUNDEEP
phpmysqllockingtable-locking
Ben Holness
Ben Holness

Reputation: 2707

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

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.

Upvotes: 6

Views: 2666

Answers (2)

Ben Holness
Ben Holness

Reputation: 2707

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

Upvotes: 5

ಠ_ಠ
ಠ_ಠ

Reputation: 3078

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.

Upvotes: 0

Related Questions