I've encoutered strange problem with MySQL table locks with MyISAM engine.
Let's say I have this type of code:
LOCK TABLES t1 WRITE;
SELECT SQL_NO_CACHE val1 FROM t1 WHERE something; // val1 = old
// some conditions on val1 and logic
UPDATE t1 SET val1 = new WHERE something;
UNLOCK TABLES;
As far as I know, this should prevent any concurrent updates. But it does not. Sometimes it just ignores lock and "old" is read in val1 after another thread changed it to "new". I even use SQL_NO_CACHE to prevent retrieve old data by mistake.
Why is that? How can I prevent update race for sure?
Thanks.
MySQL 5.5.28, MyISAM, PHP 5.2, mysql_ extension (obsolete, but on legacy project)
Edit:
OK, it should not happen in pure SQL, so there is the PHP code:
<?php
$conn = mysql_connect(...);
...
mysql_query("LOCK TABLES t1, t2 WRITE"); //t2 also used
$result = mysql_fetch_array(mysql_query("SELECT last_user, ... FROM t1 WHERE id = XXX"));
if($result["last_user"] != $session_user) { //is last activity from another than current user?
DoStuffWithUser(...); //custom function which uses t2 table
mysql_query("UPDATE t1 SET last_user = ".$session_user." WHERE id = XXX");
}
mysql_query("UNLOCK TABLES");
...
?>
Result is, that DoStuffWithUser() is called more than once for current user.
No special app, drivers, frameworks. Just built-in PHP functions.
It appears, that problem is mainly (I can't be sure if exclusively) when ONE user performs action multiple times - doubleclick, some network glitch, whatever.
OK, this is my song today https://www.youtube.com/watch?v=48rz8udZBmQ
Many thaks to @Solarflare for pushing me right way.
OK, there is mistake somewhere, let's find it. First line, simple lock query, nothing to check, it's so obvious... Oh, wait...
LOCK TABLES t1 **WRITE**, t2 WRITE
Every table has to have lock type set :)