Question

When someone visits my website, he will see the page with the fewest pageviews. All pages, including a pageview counter, are stored in a MYSQL database. If a page is viewed, the pageview counter is increased by one.

I now run into a Racing condition if my webserver has more than one thread:

Thread 1: User A retrieves page P with pageview count of 10
Thread 2: User B retreives page P with pageview count of 10
Thread 1: Pageview count of page P is increased from 10 to 11 and saved
Thread 2: Pageview count of page P is increased from 10 to 11 and saved

So at the end, the page was viewed twice, but the pagecounter is only increased by one.

A solution is to lock the row by using a SELECT ... FOR UPDATE however then, user B has to wait until the record user A is accessing is saved and with many threads all trying to get the page with the fewest pageviews, there will be a delay.

So my question: is it possible when a number of rows are locked, to get the first row which is not locked, so the threads do not have to wait on each other? How to do this in MYSQL?

I would prefer a solution which results in:

Thread 1: User A locks page P with pageview count of 10
Thread 2: User B locks page Q with pageview count of 11 (because page P is locked)
Thread 1: Pageview count of page P is increased from 10 to 11 and saved
Thread 2: Pageview count of page Q is increased from 11 to 12 and saved

Answer 1

I guess you are doing something like this:

 UPDATE Pages SET PageView = 11 WHERE Id = 30

where the constants 11 and 30 are inserted by your code (PHP?)

A better way is to do this:

 UPDATE Pages SET PageView = PageView + 1 WHERE Id = 30

Then you should not need to lock the rows across multiple queries so the problem you want to solve disappears.

友情链接