获取 MYSQL 中第一个未锁定的行
当有人访问我的网站时，他会看到浏览量最少的页面。所有页面（包括页面浏览计数器）都存储在 MYSQL 数据库中。 如果浏览了某个页面，页面浏览量计数器就会加一。
如果我的网络服务器有多个线程，我现在会遇到赛车状况：
线程 1：用户 A 检索页面 P，浏览量为 10
线程 2：用户 B 检索页面 P，浏览量为 10
线程1：页面P的浏览量从10增加到11并保存
线程2：页面P的浏览量从10增加到11并保存
所以最后，该页面被浏览了两次，但页面计数器只增加了 1。
解决方案是使用 SELECT ... FOR UPDATE 来锁定行，但是，用户 B 必须等到用户 A 正在访问的记录被保存，并且许多线程都试图获取页面浏览量最少的页面，是一个延迟。
所以我的问题：当多行被锁定时，是否可以获取第一行未锁定的行，这样线程就不必互相等待？ 如何在 MYSQL 中执行此操作？
我更喜欢一个解决方案，其结果是：
线程 1：用户 A 锁定页面 P，浏览量为 10
线程 2：用户 B 锁定页面 Q，浏览量为 11（因为页面 P 已锁定）
线程1：页面P的浏览量从10增加到11并保存
线程2：Q页的浏览量从11增加到12并保存
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
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（1）
我猜你正在做这样的事情：
其中常量 11 和 30 由你的代码插入（PHP？）
更好的方法是这样做：
那么你不需要跨多个查询锁定行，所以你想要的问题解决消失。
I guess you are doing something like this:
where the constants 11 and 30 are inserted by your code (PHP?)
A better way is to do this:
Then you should not need to lock the rows across multiple queries so the problem you want to solve disappears.