Even better, rearrange your database to store immutable facts instead of mutable state.<p>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.<p>This way, it is much harder for your data to accidentally be modified in a way that it becomes wrong.<p>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.
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!
Really not that much of a subtle bug I'm afraid, the pattern of "mutating the condition itself on success" is error-prone in single-threaded code too.<p>A good example being asynchronous javascript, where you'd do this to prevent issues like the exposed in the article:<p><pre><code> 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*/);
}</code></pre>
I like collecting tales like this one. They are useful for scaring new programmers who think they understand how to safely do multi-thread programming.
If I remember correctly, one of the ways around this is to do a no-op update before querying. So you'd update a field in the goal table to it's original value, which would cause the database to lock the table for the duration of the transaction.<p>Also, if I remember correctly, this is one of the things that can be solved with an MVCC aware engine.<p><a href="http://en.wikipedia.org/wiki/Multiversion_concurrency_control" rel="nofollow">http://en.wikipedia.org/wiki/Multiversion_concurrency_contro...</a><p>With MVCC a transaction should be aborted if another thread modifies the underlying data.
No need for a workaround, you have Optimistic Locking (via lock_version field)<p>It raises an exception in case you try to update stale data.<p><a href="http://api.rubyonrails.org/classes/ActiveRecord/Locking/Optimistic.html" rel="nofollow">http://api.rubyonrails.org/classes/ActiveRecord/Locking/Opti...</a>
Another solution would be to have e.g. a "Completions" table indexed on user and on date. Then the RDBMS wouldn't allow duplicate goal completions for the same user and day. If you wanted to allow up to X completions/day, just add a new column to track that, and limit that column to the range [0, X). I often find that updating rows in response to transactions is a code smell. Each transaction should write one or more rows to one or more tables. Of course, if you have denormalized summary tables, they might have to update, but you only do that when you know you have to.
"I was worried that I’d have to resort to semaphores or some kind of other exotic programming construct."<p>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.
Use Optimistic Locking:<p><a href="http://railscasts.com/episodes/59-optimistic-locking-revised?view=asciicast" rel="nofollow">http://railscasts.com/episodes/59-optimistic-locking-revised...</a>