In this example, you might have a goal_completion table to which you append a row when the player completes a goal, instead of changing an existing row. The table could have suitable unique constraints to make sure that each player can only complete a goal once.

This way, it is much harder for your data to accidentally be modified in a way that it becomes wrong.

This also gives you a way to store facts about the completion for free, such as a timestamp for each goal completion. And having data like timestamps is really useful for auditing, debugging and testing.

What are the proper circumstances to use immutable facts? Anytime you need an undo or audit features?

Immutable facts are great if you want to:

1) Avoid a large amount of updates/race conditions on your tables. Immutable facts, being immutable, can be done using purely inserts.

2) Want to keep a trail or context to the data you're storing. This has a benefit of keeping a log that you can later use to trace issues, behaviours or generate statistics. If you're keeping a state in a database column, without external logs you aren't able to check when or why that state changes. Immutable facts keep track of this for you, but require quite a bit more overhead.

I would almost prefer some way to "ghost" a row where without a specific switch the DBMS will never return it from a query.

http://www.postgresql.org/docs/9.2/static/rules-update.html

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

I forgot a very important part of the query:

row_count = Goal.update_all "completed = true", ["player_id = ? AND completed = false", player.id]

If you update where completed = false, the rowcount will only be 1 when the update works.

(I've updated the blog post.)

A good example being asynchronous javascript, where you'd do this to prevent issues like the exposed in the article:

    var some_mutable_condition = false;
    var requested = false;

    if (!some_mutable_condition && !requested){
        requested = true; // ensure one request is performed at most
        $.ajax(/*async code that'll set some_mutable_condition to true*/);
    }

Also, if I remember correctly, this is one of the things that can be solved with an MVCC aware engine.

http://en.wikipedia.org/wiki/Multiversion_concurrency_contro...

With MVCC a transaction should be aborted if another thread modifies the underlying data.

It raises an exception in case you try to update stale data.

http://api.rubyonrails.org/classes/ActiveRecord/Locking/Opti...

but in this specific case `affected_rows` works better as long as you know its reliable.

My solution is basically the same amount of lines, requires no such changes.

I'm not saying a semaphore would have been appropriate here but- For real? A semaphore is exotic? It is an one of the oldest original synchronization methods. Semaphore were developed by Dijkstra in 1965. The author's C-S program failed him.

In the sentence before I explicitly said I was a newbie to this stuff at the time. Obviously it's not exotic to a more experienced developer :)

There are many application developers out there using Rails, PHP or Django who can go years without invoking locks or semaphores due to the strengths of the APIs and frameworks they use.

As they learn and grow as developers, they will be introduced to new problems and solutions. A semaphore can be exotic, not because they've never heard of it before, but because they've not used them very much outside of that one assignment they did in CS 5 years ago.

How about instead of criticizing people's eductions, we encourage them to be mindful about what they don't know, and to learn whatever they can when they get a chance?

But as the field grows there will be more and more material that was traditionally taught, but is now dropped. For example, I could imagine a misguided department dropping a once required OS/Systems class to allow students to choose that as an elective, or maybe a node.js or Ruby elective instead. I think those kind of program changes are serious and need discussion-

http://railscasts.com/episodes/59-optimistic-locking-revised...