title: Limit the row of the table to be used by one script at a time
body: <p>I have a table <code>tableName</code> which contains some names to be used:</p>
  
  <pre><code>id    name    in_use
  1     name1   0
  2     name2   0
  </code></pre>
  
  <p>My script get the first name from this table with <code>in_use = 0</code>:</p>
  
  <pre><code>SELECT name FROM tableName WHERE in_use = 0 ORDER BY id
  </code></pre>
  
  <p>and immediately after set the row obtained to <code>in_use = 1</code>:</p>
  
  <pre><code>UPDATE tableName set in_use = 1 WHERE id = '$idObtained'
  </code></pre>
  
  <p>When the script is finishing, it set <code>in_use = 0</code>:</p>
  
  <pre><code>UPDATE tableName set in_use = 0 WHERE id = '$idObtained'
  </code></pre>
  
  <p>The problem is that when the script will be executed more than once in the same instant, two or more selects may get the same name form this table.</p>
  
  <p>My aim is to avoid two scripts to get the same row in their execution.</p>
  
  <p>There is a way to do that with PHP / MySQL?</p>
  
  <p>Does locking the table before the select and unlocking it after the update do that?</p>
  
  <p>The script (the code is only a draft. I'll develop it only if what I need is possible in PHP and MySQL):</p>
  
  <pre><code>&lt;?php
      // ...
  
      // TODO: lock the raw, if found
      $query = "SELECT id, name
      FROM tableName
      WHERE in_use = 0
      ORDER BY id";
      $stmt = $connection-&gt;prepare($query);
      $stmt-&gt;execute();
      $data = $stmt-&gt;fetch();
  
      if(!empty($data['id'])) {
  
          $query = "UPDATE tableName
          SET in_use = 1
          WHERE id = '$data[id]'";
          $stmt = $connection-&gt;prepare($query);
          $stmt-&gt;execute();
  
          // do some with $data['name]
  
          $query = "UPDATE tableName
          SET in_use = 0
          WHERE id = '$data[id]'";
          $stmt = $connection-&gt;prepare($query);
          $stmt-&gt;execute();
  
          // TODO: unlock the raw
      }
  ?&gt;
  </code></pre>
  
  <p>Thanks a lot.</p>