I believe this won't work on PostgreSQL, which will always say one row has been updated, even if the new value is the same as the old value.

There are numerous ways around that, from less fancy to really fancy.

  1. use SELECT FOR UPDATE, which will lock the row (I'd say that's the normal way)

  session1> BEGIN;
  session1> SELECT * FROM goals WHERE player_id = ? FOR UPDATE
  session2> BEGIN;
  session2> SELECT * FROM goals WHERE player_id = ? FOR UPDATE
  # session2 is now hanging
  session1> UPDATE goals SET completed = true WHERE goal_id = ?
  session1> UPDATE players SET points = points + 1 WHERE player_id = ?
  session1> COMMIT;
  # session2 now proceeds, sees that the goal has been completed, forfeits awarding the reward

  2. use suppress_reduntant_updates_trigger (fun)

  session> CREATE TRIGGER suppress_goal_t BEFORE UPDATE ON goals FOR EACH ROW EXECUTE PROCEDURE suppress_redundant_updates_trigger();
  session> UPDATE goals SET completed = true WHERE goal_id = ?
  UPDATE 1
  session> UPDATE goals SET completed = true WHERE goal_id = ?
  UPDATE 0
  # now you can use the approach mentioned in the article

  3. use true serialisability
  session1> BEGIN;
  session1> SET transaction_isolation TO serializable;
  session1> SELECT * FROM goals WHERE player_id = ?
  session2> BEGIN;
  session2> SET transaction_isolation TO serializable;
  session2> SELECT * FROM goals WHERE player_id = ?
  session1> UPDATE goals SET completed = true WHERE goal_id = ?
  session1> UPDATE players SET points = points + 1 WHERE player_id = ?
  session1> COMMIT;
  session2> UPDATE goals SET completed = true WHERE goal_id = ?
  ERROR:  could not serialize access due to concurrent update
  # session2 now has to rollback the transaction

There's a few more, but I ran out of steam typing ;) Yay, Postgres!