How to avoid InnoDB counter row level locking?

Let's think about a situation where a counter gets updated very often, for example the view counter on a very popular New York Times post. Every time a user refreshes the page, a SQL query updates the counter. It would look something like this:

UPDATE `posts` SET view_counter = view_counter + 1 WHERE id = 123

Now on a very busy site there can be problems with frequent counter row locks. If you send a query like that above, what actually can happen is that InnoDB locks the row so often it produces some deadlocks. It is a pretty inprobable situation in small systems hovever in some high-traffic production evironments, there can be problems with such counters getting out of sync. Ther is a very simple and efficient solution to this problem:

1. Create a table with the first field beeing the counter id/number, second field should be the foreign key to the element you really want to count and of course a field with the counter value (default 0),
2. For every stressed counter, you feel that could get out of sync, create 10-50 rows ( the number is arbitrary) int the created table with remebering which primary key ids are to what counter.

If you inserted the apropriate keys for a counter, now you need to generate a coresponding random id number before updating. Thre read/write operations are based simple trick. When you have more than one counter rows that count the same thing, you just randomly select one of them and increment it. The benefit of having 20 rows to keep the same counter is that the probability of desynchronization is that much reduced.

Now all that's left is getting the numbers added up. We will do that by using a simple aggregate function - GROUP BY + SUM(). Counting 20-50 rows every time, when you want to show the counter, isn't a big load for the database hovewer you still can write a cron that generates a single counter again for select queries.