MySQL - how to check reason for UPDATE rowCount = 0-CodePudding

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 UPDATE on a row, I check the resulting row count. There are two cases when the row count can be 0 - a) the row was already updated by another request with the same values or b) the row was deleted by another client. In scenario a there is no issue, but in scenario b 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 a and throw an actual error for scenario b, like INSERT INTO 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 UPDATE and SELECT. I am using the READ COMMITTED isolation level, so new data can appear during the request.

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 rowCount(). For MySQL at least the problem could be solved with PDO::MYSQL_ATTR_FOUND_ROWS but that doesn't seem to apply to Postgres/SQLite.

MariaDB [test]> update test set id=72;
Query OK, 0 rows affected (0.000 sec)
Rows matched: 1  Changed: 0  Warnings: 0

I have also found that I could follow up every UPDATE with a SELECT FOUND_ROWS() 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.

CodePudding user response：

It turns out that the behavior I'm looking for is already the default in PostgreSQL. The manual states "The count is the number of rows updated, including matched rows whose values did not change".

For MySQL, the desired behavior can be had with PDO::MYSQL_ATTR_FOUND_ROWS.

For Sqlite, the PDO documentation states that rowCount() "returns "0" (zero) with the SQLite driver at all times", but this does not seem to be true. I ran a test script and it seems to already work the same as PostgreSQL. My system is running libsqlite 3.35.5, PHP 7.4.27.

<?php

$db = new \PDO("sqlite:test.db");
$db->query("delete from test");

$result = $db->query("insert into test values(65)");
echo "insert: count: ".$result->rowCount()."\n";

$result = $db->query("update test set id=55 where id=65");
echo "update changed: count: ".$result->rowCount()."\n";

$result = $db->query("update test set id=55 where id=55");
echo "update not changed: count: ".$result->rowCount()."\n";

$result = $db->query("update test set id=55 where id=65");
echo "update not existing: count: ".$result->rowCount()."\n";

yields...

insert: count: 1
update changed: count: 1
update not changed: count: 1
update not existing: count: 0

CodePudding user response：

I am not sure that this solution could help you or not:

Lets assume you have a table like this:

And you want to manage paralell update and deletes by different clients. So my suggestion would be change table structure. And make it client spesific:

So each client could just update/insert based on related data. And Create a view which just shows ID and Value - which works based on the latest status and filter out deleted records -

Query of view could be:

select ID, VALUE
  from (
        select ID, 
               VALUE,
               STATUS,
               ROW_NUMBER() OVER(PARTITION BY ID ORDER BY STATUS_DATE DESC) RN 
          from table m
       )dt
 where 1 = 1
   and rn = 1
   and STATUS <> 'Deleted'

Maybe it is not a good solution for your work. Just an idea maybe it could help.