5

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.

Ben Holness
  • 2,457
  • 3
  • 28
  • 49
  • Could your PHP be set up to use shared mysql connections? – Barmar Sep 02 '13 at 18:08
  • I will check, but if so, why does the INSERT delay but the SELECT does not? – Ben Holness Sep 02 '13 at 18:09
  • I just tried from the `mysql>` command prompt, and locking worked as you expected. – Barmar Sep 02 '13 at 18:09
  • It looks like it might be related to query caching somehow. I tried in a command prompt too. The first time it worked (the SELECT was delayed), the second time it did not (the SELECT was instant), however as soon as I made a change to the table within the lock session, the SELECT was delayed again. This makes some sort of sense I guess, but it's not what it says it should do. If I WRITE LOCK a table, it should be LOCKED. The current behaviour could break my process. – Ben Holness Sep 02 '13 at 18:26
  • In fact, adding SQL_NO_CACHE to the SELECT query makes it work! – Ben Holness Sep 02 '13 at 18:32

2 Answers2

5

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
Ben Holness
  • 2,457
  • 3
  • 28
  • 49
0

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.

ಠ_ಠ
  • 3,060
  • 28
  • 43
  • According to MySQL the WRITE lock prevents all access to the table. Otherwise what's the different between READ and WRITE locks? As for the same MySQL session, I do not believe this is true, for two reasons. 1: My INSERT was blocked and 2: instead of another browser window, I can do it on another computer/IP and it still happens – Ben Holness Sep 02 '13 at 17:30
  • See http://dev.mysql.com/doc/refman/5.0/en/lock-tables.html: "The session that holds the lock can read and write the table." – ಠ_ಠ Sep 02 '13 at 17:31
  • For READ locks: "The session that holds the lock can read the table (but not write it)." – ಠ_ಠ Sep 02 '13 at 17:32
  • 1
    Why do you think they are the same MySQL session (and if they are, how come the INSERT is delayed?) – Ben Holness Sep 02 '13 at 17:34