1

I'm using MySQL 5.1, PHP 5.3 and the table is MyISAM. Here's the DTD of this very fake table:

CREATE TABLE `temp1` (
  `value` varchar(10) DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1

This script basically prints the row count for the table temp1, then inserts a row, then prints the row count again, then sleeps for a second and prints the row count one more time. The INSERTs are done with the write user and the SELECTs are done with the read user.

<?php

$value = "test";
$ro_user = "ro";
$ro_pass = "ro";
$write_user = "write";
$write_pass = "write";
$db_name = "db";

$ro_db = mysql_connect('localhost', $ro_user, $ro_pass);
mysql_select_db($db_name, $ro_db);

$res = mysql_query("SELECT * FROM temp1 WHERE value = '" . mysql_real_escape_string($value) . "'", $ro_db);
var_dump(mysql_num_rows($res));

$write_db = mysql_connect('localhost', $write_user, $write_pass);
mysql_select_db($db_name, $write_db);
mysql_query("INSERT INTO temp1 (`value`)  VALUES ('" . mysql_real_escape_string($value) . "')", $write_db);

$res = mysql_query("SELECT * FROM temp1 WHERE value = '" . mysql_real_escape_string($value) . "'", $ro_db);
var_dump($res);
var_dump(mysql_num_rows($res));

sleep(1);

$res = mysql_query("SELECT * FROM temp1 WHERE value = '" . mysql_real_escape_string($value) . "'", $ro_db);
var_dump($res);
var_dump(mysql_num_rows($res));

The printout are usually something like this:

int(12)
resource(8) of type (mysql result)
int(12)
resource(9) of type (mysql result)
int(13)

This seems to indicate that the INSERT isn't fully complete by the time the second SELECT query is executed. So my questions are:

  • Are MySQL INSERT statements usually asynchronous? It's hard to get information about this, but I thought the answer was no.
  • Are there any MySQL settings which would influence this behavior?
  • Is the table engine significant here?
  • Is there any possibility of caching going on which would cause this? I'm running this on a vagrant box with default settings.

Even weirder, I can't reproduce this behavior in MySQL 5.5 or 5.6, or any InnoDB table. No race condition shows up there.

4
  • - I don't have documentation to back it up, but to my experience all mysql calls from php are synchronous. Imagine the consequences otherwise... - I don't know any setting in mysql that would say otherwise. - I don't think the table engine has anything to do here. - Caching is a possible problem. Try using SELECT SQL_NO_CACHE * ... instead. – Sebas Jul 9 '14 at 17:22
  • SQL_NO_CACHE doesn't affect anything. I added some information at the bottom about what I've tested on. It seems table engine is relevant – noisecapella Jul 9 '14 at 17:37
  • 1
    you're right! forums.mysql.com/read.php?21,37304,37308#msg-37308 – Sebas Jul 9 '14 at 17:50
  • Actually @Sebas, I think that's a misinterpretation. Fluahes to disk are asynchronous in MyISAM but the actual write to the OS (cache)-- where the other query would read from -- isn't. – Michael - sqlbot Jul 15 '14 at 12:01

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.