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

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