title: MySQL - how to check reason for UPDATE rowCount = 0
body: <p>I'm trying to solve some concurrency issues in a database.  The application I'm developing needs to be able to work with MySQL, PostgreSQL and SQLite.  When doing the <code>UPDATE</code> on a row, I check the resulting row count.  There are two cases when the row count can be 0 - <code>a</code>) the row was already updated by another request with the same values or <code>b</code>) the row was deleted by another client. In scenario <code>a</code> there is no issue, but in scenario <code>b</code> the request should fail.  Is there an easy/thread-safe way to tell the difference? It would be great if it could return rowCount=0 for scenario <code>a</code> and throw an actual error for scenario <code>b</code>, like <code>INSERT INTO</code> does if two clients concurrently insert a conflicting row.  The only thing I can think of is to re-query if the row still exists, but this is 1) wasteful and inelegant and 2) still not thread safe - the row could get re-created between the <code>UPDATE</code> and <code>SELECT</code>.  I am using the <code>READ COMMITTED</code> isolation level, so new data can appear during the request.</p>
  <p>I noticed that when doing this via the command line you can tell, at least for MySQL.  If it works with all three then maybe this is just more of a PHP-PDO question.  PDO just has a single <code>rowCount()</code>.  For MySQL at least the problem could be solved with <code>PDO::MYSQL_ATTR_FOUND_ROWS</code> but that doesn't seem to apply to Postgres/SQLite.</p>
  <pre><code>MariaDB [test]&gt; update test set id=72;
  Query OK, 0 rows affected (0.000 sec)
  Rows matched: 1  Changed: 0  Warnings: 0
  </code></pre>
  <p>I have also found that I could follow up every <code>UPDATE</code> with a <code>SELECT FOUND_ROWS()</code> but this seems a bit hacky.  I'd rather avoid two queries if possible.  Ideally I'm looking for a way to have it just report the count of the rows matched, not rows affected.</p>