Mastering PostgreSQL 10

Making use of FOR SHARE and FOR UPDATE

Sometimes, data is selected from the database; then some processing happens in the application and, finally, some changes are made back on the database side. This is a classic example of SELECT FOR UPDATE.

Here is an example:

BEGIN;SELECT * FROM  invoice WHERE  processed = false; 
** application magic  will  happen here  **  
UPDATE invoice SET processed = true  ...  
COMMIT; 

The problem here is that two people might select the same unprocessed data. Changes made to these processed rows will then be overwritten. In short, a race condition will occur.

To solve this problem, developers can make use of SELECT FOR UPDATE. Here is how it works:

BEGIN; SELECT * FROM invoice WHERE processed = false FOR UPDATE; ** application ...