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.

and "old" is read in val1 after another thread changed it to "new" what is the application making these updates? Prehaps the driver is what is doing the caching?

– Matt Clark
Sep 6 '18 at 21:37

and "old" is read in val1 after another thread changed it to "new"

This shouldn't be possible, and while bugs can happen, this is a very old, often used and tested feature, so it seems more likely that you did something wrong in your code or that it's an external effect (e.g. auto reconnect? connection pooling?). To check what's going on I'd try to log to a log table (with autoincrement) right after the lock, right after val1 and right before and after releasing the lock, and check if there is some unexpected order.

– Solarflare
Sep 6 '18 at 22:20

I've added php code also, to be clear...

– netrunner
Sep 7 '18 at 11:21

@Solarflare log table done and tested (by me, multiclick). In log table I can see I made two more connections after first LOCK, which were able to read and update locked tables - so log order is like lock1, lock2, select1, select2, update1, update2, dostuff1, lock3, dostuff2, unlock1, select3, unlock2, unlock3. So third connection did not perform action, but still interfierd with other locks...

– netrunner
Sep 7 '18 at 12:21

Lock1, lock2 should already not be possible, session 2 should wait right there before it can get the lock. Can you include connection_id() (and the value for val1) in your logs, to exclude that you are either reconnecting or reusing the same connection for what you assume are different sessions. Also, can you check that your table is not a temporary table? (It doesn't seem to make sense from context, but just to be sure). I also assume you are not using a cluster/loadbalancing(replication) setup or something like that.

– Solarflare
Sep 7 '18 at 12:56

Lock1, lock2

connection_id()

1 Answer
1

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

Thanks for contributing an answer to Stack Overflow!

But avoid …

To learn more, see our tips on writing great answers.

Required, but never shown

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

搜尋此網誌

Dfyjkt