In the Lost Updates section, a more straight forward solution is to use the FOR UPDATE clause in the first select statement. This locks the record and prevents concurrent updates.
When you're incrementing by using UPDATE ... SET value = value + 1, the database holds the locks for the minimum time needed. Everything else is less efficient.
In more complex scenarios, FOR UPDATE is the solution.
When there's a big chance of multiple tasks grabbing the same rows, processing them, then updating them, marking them for update since the beginning is better. E.g. a message queue like structure where messages should be processed only once.
In more complex scenarios, FOR UPDATE is the solution.