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.<p>There are numerous ways around that, from less fancy to really fancy.<p><pre><code> 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
</code></pre>
There's a few more, but I ran out of steam typing ;) Yay, Postgres!