开发者

Get first unlocked row in MYSQL

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)

Th开发者_如何学运维read 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


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.

0

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜