According to its documentation, Postgres re-evaluates all WHERE-clauses within a transaction whenever any other transaction is committed, in order to check if the updated row still matches the search condition:
UPDATE, DELETE, SELECT FOR UPDATE, and SELECT FOR SHARE commands behave the same as SELECT in terms of searching for target rows: they will only find target rows that were committed as of the command start time. However, such a target row might have already been updated (or deleted or locked) by another concurrent transaction by the time it is found. In this case, the would-be updater will wait for the first updating transaction to commit or roll back (if it is still in progress).
If the first updater rolls back, then its effects are negated and the second updater can proceed with updating the originally found row. If the first updater commits, the second updater will ignore the row if the first updater deleted it, otherwise it will attempt to apply its operation to the updated version of the row.
The search condition of the command (the WHERE clause) is re-evaluated to see if the updated version of the row still matches the search condition. If so, the second updater proceeds with its operation using the updated version of the row.
What about SQLite in WAL mode? Does it behave the same way as Postgres and also re-evaluate the WHERE-clause when another updater commits first? I couldn't find anything about this in the SQLite documentation.
Even with WAL mode, in SQLite, there can only be one writer of the entire database file at a time.
If a read transaction attempts to upgrade to a write transaction, and the database has been altered since the transaction, the operation will fail with a "database is locked" error.
You can avoid this if you know ahead of time that you will be writing to the database by starting the transaction with BEGIN EXCLUSIVE TRANSACTION to acquire a write lock immediately, preventing anyone else from making changes while you query and alter the data.