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 magic will happen here ** 
UPDATE invoice SET processed = true ... 
COMMIT;

The SELECT FOR UPDATE will lock rows just like an UPDATE would. This means that no changes can happen concurrently. All locks will be released on commit as usual.

If one SELECT...