CODEWITHSUNDEEP
mysqlmyisam
netrunner
netrunner

Reputation: 41

MySQL lock tables ignored

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.

Upvotes: 1

Views: 173

Answers (1)

netrunner
netrunner

Reputation: 41

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 :)

Upvotes: 2

Related Questions