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