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.