Mysql – counter table in thesql

database-designMySQL

i was going through the book: High performance MySql 2nd edition. In chapter 4 it says:

An application that keeps counts in a table can run into concurrency
problems when updating the counters. Such tables are very common in
web applications.

To keep things as simple as possible, suppose you have a counter table
with a single row that just counts hits on your website:

mysql> CREATE TABLE hit_counter ( cnt int unsigned not null ) ENGINE=InnoDB;

Each hit on the website updates the counter:

mysql> UPDATE hit_counter SET cnt = cnt + 1;

The problem is that this single row is effectively a global “mutex”
for any transaction that updates the counter. It will serialize those
transactions. You can get higher concurrency by keeping more than one
row and updating a random row.

Below is the change:

mysql> CREATE TABLE (slot tinyint unsigned not null primary key, cnt int unsigned not null ) ENGINE=InnoDB;

Prepopulate the table by adding 100 rows to it. Now the query can just
choose a random slot and update it:

mysql> UPDATE hit_counter SET cnt = cnt + 1 WHERE slot = RAND() *
100;

To retrieve statistics, just use aggregate queries:

mysql> SELECT SUM(cnt) FROM hit_counter;

Here comes my doubt:

As there are two different queries one which increment the cnt and other which calculate the sum of cnt than how come it will give correct result to two different threads executing simultaneously? For example t1 fires the first query to increase the cnt , before it fetches the sum t2 also updates the cnt than the 2nd query for t1 will fetch wrong result. Please clarify.

if you dont have the book please refer this.

Best Answer

It's the transaction isolation that guarantees consistent results.

In your example, if you are concerned about possible changes from other threads between the first and the second query, you should enclose your queries in a single transaction: this is implicit if you have autocommit disabled, otherwise can be explicitly declared:

START TRANSACTION;
UPDATE hit_counter SET cnt = cnt + 1 WHERE slot = RAND() * 100;
SELECT SUM(cnt) FROM hit_counter;
COMMIT;

But it must also be said that, in many real use cases (like a counter for visits on a web page), having a result which is consistent with the previous increment is not so important.

For a deep explanation about possible transaction isolation levels please refer to: http://dev.mysql.com/doc/refman/5.7/en/set-transaction.html

Related Question