Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question

1 Answer 1

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

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.