CODEWITHSUNDEEP
phpmysqlsqldatabaseunbuffered-queries
pillarOfLight
pillarOfLight

Reputation: 8982

mysql unbuffered query locking doesn't lock

Evidently according to this: http://www.tuxradar.com/practicalphp/9/4/9

Between the time the call to mysql_unbuffered_query() is issued and your processing the last row, the table remains locked by MySQL and cannot be written to by other queries. If you plan to do lengthy processing on each row, this is not good.

So I'm trying to simulate that. Note that using MYSQLI_USE_RESULT is synonymous with using unbuffered query. Also note that there's around 60000 existing entries in the table.

So I have this code: 

 $use_buffer = FALSE;
  $buffer = $use_buffer ? "SQL_BUFFER_RESULT" : "";

  $query = "SELECT $buffer * FROM table";
  $result = mysqli_query($db, $query, MYSQLI_USE_RESULT);
  $inserted = FALSE;
  $i = 0;
  $rand = rand();
  while($r = mysql_fetch_object($result)){
    if(!$inserted){
      mysqli_query($db, 'INSERT INTO table (something, somethingint) VALUES (\'NewValue' . $rand . '\', 1)');
      print('INSERTEDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDDD');
      $inserted = TRUE;
    }
    if(!($i % 500)){
      $j = mysql_fetch_object(mysqli_query($db, "SELECT * FROM table WHERE something = 'NewValue$rand'"));
      print_r($j);
      print('still outputting');
      print_r($r);
    }
    $i++;
    if($i > 5000){
      break;
    }
  }
  mysql_free_result($result);

but then the code managed to insert new row into the table and moreover fetch that new table just fine even though the table is supposed to be locked until i'm done fetching all the rows.

Why is this happening? AM I missing something?

Upvotes: 1

Views: 706

Answers (1)

Bill Karwin
Bill Karwin

Reputation: 562280

The locking referred to in that blog applies only to the MyISAM storage engine.

If your tables are defined to use the InnoDB storage engine (which is the default storage engine since MySQL 5.5), then the principle applies that readers don't block writers.

I.e. a plain SELECT doesn't lock anything in InnoDB.

Re your comment:

MyISAM has a special case for INSERT, where you can insert to the table even if it's otherwise locked by another session, as long as the insert is appending to the end of the data.

https://dev.mysql.com/doc/refman/5.6/en/optimizing-queries-myisam.html says:

MyISAM supports concurrent inserts: If a table has no free blocks in the middle of the data file, you can INSERT new rows into it at the same time that other threads are reading from the table.

Upvotes: 2

Related Questions