title: PHP concurrency issue, multiple simultaneous requests; mutexes?
body: <p>So I've just realised that PHP is potentially running multiple requests simultaneously. The logs from last night seem to show that two requests came in, were processed in parallel; each triggered an import of data from another server; each attempted to insert a record into the database. One request failed when it tried to insert a record that the other thread had just inserted (the imported data comes with PKs; I'm not using incrementing IDs): <code>SQLSTATE[23000]: Integrity constraint violation: 1062 Duplicate entry '865020' for key 'PRIMARY' ...</code>.</p>
  
  <ol>
  <li>Have I diagnosed this issue correctly?</li>
  <li>How should I address this?</li>
  </ol>
  
  <p>The following is some of the code. I've stripped out much of it (the logging, the creation of other entities beyond the Patient from the data), but the following should include the relevant snippets. Requests hit the import() method, which calls importOne() for each record to import, essentially. Note the save method in importOne(); that's an Eloquent method (using Laravel and Eloquent) that will generate the SQL to insert/update the record as appropriate.</p>
  
  <pre><code>public function import()
  {
          $now = Carbon::now();
          // Get data from the other server in the time range from last import to current import
          $calls = $this-&gt;getCalls($this-&gt;getLastImport(), $now);
          // For each call to import, insert it into the DB (or update if it already exists)
          foreach ($calls as $call) {
              $this-&gt;importOne($call);
          }
          // Update the last import time to now so that the next import uses the correct range
          $this-&gt;setLastImport($now);
  }
  
  private function importOne($call)
  {
      // Get the existing patient for the call, or create a new one
      $patient = Patient::where('id', '=', $call['PatientID'])-&gt;first();
      $isNewPatient = $patient === null;
      if ($isNewPatient) {
          $patient = new Patient(array('id' =&gt; $call['PatientID']));
      }
      // Set the fields
      $patient-&gt;given_name = $call['PatientGivenName'];
      $patient-&gt;family_name = $call['PatientFamilyName'];
      // Save; will insert/update appropriately
      $patient-&gt;save();
  }
  </code></pre>
  
  <p>I'd guess that the solution would require a mutex around the entire import block? And if a request couldn't attain a mutex, it'd simply move on with the rest of the request. Thoughts?</p>
  
  <p>EDIT: Just to note, this isn't a critical failure. The exception is caught and logged, and then the request is responded to as per usual. And the import succeeds on the other request, and then that request is responded to as per usual. The users are none-the-wiser; they don't even know about the import, and that isn't the main focus of the request coming in. So really, I could just leave this running as is, and aside from the occasional exception, nothing bad happens. But if there is a fix to prevent additional work being done/multiple requests being sent of to this other server unnecessarily, that could be worth pursuing.</p>
  
  <p>EDIT2: Okay, I've taken a swing at implementing a locking mechanism with flock(). Thoughts? Would the following work? And how would I unit test this addition?</p>
  
  <pre><code>public function import()
  {
      try {
          $fp = fopen('/tmp/lock.txt', 'w+');
          if (flock($fp, LOCK_EX)) {
              $now = Carbon::now();
              $calls = $this-&gt;getCalls($this-&gt;getLastImport(), $now);
              foreach ($calls as $call) {
                  $this-&gt;importOne($call);
              }
              $this-&gt;setLastImport($now);
              flock($fp, LOCK_UN);
              // Log success.
          } else {
              // Could not acquire file lock. Log this.
          }
          fclose($fp);
      } catch (Exception $ex) {
          // Log failure.
      }
  }
  </code></pre>
  
  <p>EDIT3: Thoughts on the following alternate implementation of the lock:</p>
  
  <pre><code>public function import()
  {
      try {
          if ($this-&gt;lock()) {
              $now = Carbon::now();
              $calls = $this-&gt;getCalls($this-&gt;getLastImport(), $now);
              foreach ($calls as $call) {
                  $this-&gt;importOne($call);
              }
              $this-&gt;setLastImport($now);
              $this-&gt;unlock();
              // Log success
          } else {
              // Could not acquire DB lock. Log this.
          }
      } catch (Exception $ex) {
          // Log failure
      }
  }
  
  /**
   * Get a DB lock, returns true if successful.
   *
   * @return boolean
   */
  public function lock()
  {
      return DB::SELECT("SELECT GET_LOCK('lock_name', 1) AS result")[0]-&gt;result === 1;
  }
  
  /**
   * Release a DB lock, returns true if successful.
   *
   * @return boolean
   */
  public function unlock()
  {
      return DB::select("SELECT RELEASE_LOCK('lock_name') AS result")[0]-&gt;result === 1;
  }
  </code></pre>