Select and update a row in an atomic operation using PHP

Question

I have a PHP script that uses PDO to connect to a SQLite database. Let's say the database looks like this (simplified):

ID     A    B
1      foo  bar
2      abc  xyz
3      def

Now, let's say that someone accesses this script using http://example.com/example.php?b=ghi. The script should then find the first (lowest ID) row with an empty value in B and insert ghi into B.

Obviously I could just use something like this (let's pretend I have sanitized the GET variable b):

$row = $db->query("SELECT * FROM table WHERE B IS NULL OR B=''")->fetch();
$stmt = $db->prepare("UPDATE table SET B=:b WHERE ID=:id");                                               
$stmt->bindValue(":b", $b);
$stmt->bindValue(":id", $row['ID']);
$stmt->execute();

In my case it would probably work fine in 99.99999% of all cases. But there is a chance of a concurrency problem if two users access the script at the same time. They could get the same row (ID = 3) from the SELECT statement, and then it's anyone's guess which value will be overwritten by the other one.

Is there an easy way to work around this?

https://stackoverflow.com/questions/8688476/locking-a-row-with-sqlite-read-lock and https://medium.com/@gwendal.roue/four-different-ways-to-handle-sqlite-concurrency-db3bcc74d00e and https://www.sqlite.org/faq.html#q5 — Nic3500, Sep 11 '18 at 11:58

score 1 · Accepted Answer · answered Sep 11 '18 at 20:13

1

Just do it all in one single statement. Subqueries are your friend:

UPDATE table SET b = :b
WHERE id = (SELECT id FROM table AS t
            WHERE t.b IS NULL OR t.b = ''
            ORDER BY t.id
            LIMIT 1)

answered Sep 11 '18 at 20:13

Shawn

47,241
3
26
60

Select and update a row in an atomic operation using PHP

1 Answers1