Using the example from the article Using lightweight transactions, the example of such a transaction is given as:
UPDATE cycling.cyclist_name
SET firstname = 'Roxane'
WHERE id = 4647f6d3-7bd2-4085-8d6c-1229351b5498
IF firstname = 'Roxxane';
How is this different from replacing IF with WHERE, as in:
UPDATE cycling.cyclist_name
SET firstname = 'Roxane'
WHERE id = 4647f6d3-7bd2-4085-8d6c-1229351b5498
AND firstname = 'Roxxane';
I am guessing that the second one does not do the read and write in a single step, but rather first selects based on where and then applies the update, while the first one blocks other processes from writing to the DB when the query is running.
