title: PHP / MySQL - how to prevent two requests *Update
body: <p>I have some question ...
  example: a user will buy something for his USD</p>
  
  <ol>
  <li>Check his USD Balance</li>
  <li>Deduct the USD from his account</li>
  <li>Make an Order -> order queue</li>
  <li>user gets his item and the other one gets his USD</li>
  </ol>
  
  <p>Lets say, the users makes 5 requests in the same second (very fast).
  So it is possible (and happen) that 5 requests are running.
  He has only money to buy only from 1 request. Now the requests
  are so fast, that the script checks his balance, but is not so fast, that it deduct the
  money from his account. So the requests will pass two times!
  How to solve it?</p>
  
  <p>I use LOCK in mysql before I start the process:</p>
  
  <ol>
  <li>IS_FREE_LOCK - check is there a lock for this user if not -> 2.</li>
  <li>GET_LOCK - sets the lock</li>
  <li>make the order / transaction</li>
  <li>RELEASE_LOCK - releases the lock</li>
  </ol>
  
  <p>But this does not really work. Is there another way?</p>
  
  <pre class="lang-php prettyprint-override"><code>function lock($id) {
    mysql_query("SELECT GET_LOCK('$id', 60) AS 'GetLock'");
  }
  
  function is_free($id) {
    $query = mysql_query("SELECT IS_FREE_LOCK('$id') AS 'free'");
    $row = mysql_fetch_assoc($query);
    if($row['free']) {
      return true;
    } else {
      return false;
    }
  }
  
  function release_lock($id) {
    mysql_query("SELECT RELEASE_LOCK('$id')");
  }
  
  function account_balance($id) {
    $stmt = $db-&gt;prepare("SELECT USD FROM bitcoin_user_n WHERE id = ?");
    $stmt-&gt;execute(array($id));
    $row = $stmt-&gt;fetch(PDO::FETCH_ASSOC);
  
    return $row['USD'];
  }
  
  if(is_free(get_user_id())) {
    lock(get_user_id());
    if(account_balance(get_user_id()) &lt; str2num($_POST['amount'])) {
      echo "error, not enough money";
    } else {
      $stmt = $db-&gt;prepare("UPDATE user SET USD = USD - ? WHERE id = ?");
      $stmt-&gt;execute(array(str2num($_POST['amount']), get_user_id()));
      $stmt = $db-&gt;prepare("INSERT INTO offer (user_id, type, price, amount) VALUES (?, ?, ?, ?)");
      $stmt-&gt;execute(array(get_user_id(), 2, str2num($_POST['amount']), 0));
  }
  </code></pre>
  
  <p><strong>Update</strong>
  Tested the transaction function with SELECT ... FOR UPDATE
  </p>
  
  <pre><code>$db-&gt;beginTransaction();
  $stmt = $db-&gt;prepare("SELECT value, id2 FROM test WHERE id = ? FOR UPDATE");
  $stmt-&gt;execute(array(1));
  $row = $stmt-&gt;fetch(PDO::FETCH_ASSOC);
  if($row['value'] &gt; 1) {
    sleep(5);
    $stmt = $db-&gt;prepare('UPDATE test SET value = value - 5 WHERE id = 1');
    $stmt-&gt;execute();
    $stmt = $db-&gt;prepare('UPDATE test SET value = value + 5 WHERE id = 2');
    $stmt-&gt;execute();
    echo "did have enough money";
  } else {
    echo "no money";
  }
  $db-&gt;commit();
  </code></pre>