Mastering PostgreSQL 9.6

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 those 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 ...