How do I consistently increase a counter cache column?

Question

Lets say I have a counter cache that needs to to be incremented on every page load. Say I have 10 web instances. How do I consistently increase a counter cache column?

Consistency is easy with one web instance. but with several instances running, A race conditions may occur.

Here is a quick explanation. Let's say my counter cache column is called foo_counts and its starting value is 0. If 2 web instance are loaded at the same time, both realize the count as 0. When it comes time to increase the count. They both increment the count from 0 to 1.

I looked at http://guides.rubyonrails.org/active_record_querying.html#locking-records-for-update

Any ideas would be greatly appreciated.

Answer 1

You could use increment_counter:

increment_counter(counter_name, id)

Increment a number field by one, usually representing a count.

This does a direct UPDATE in SQL so Model.increment_counter(:c, 11) sends this SQL to the database:

update models set c = coalesce(c, 0) + 1 where id = 11

so you don't have to worry about race conditions. Let the database do its job.

Answer 2

Consider queueing the increments, and having workers do the actual incrementation in the background. You won't have completely up-to-the-millisecond data, but at least it will be accurate.