如何防止select found_rows在竞争条件下中断? [英] How to prevent select found_rows breaking on race conditions?

问题描述

我必须运行一个具有限制和偏移量的查询，并且还需要总数的结果来建立分页.这是一个具有很多条件和联接的复杂查询，因此我想避免只为获得计数而重复两次查询.

I have to run a query that has a limit and offset and I also need the total number of results to build pagination. It's a complex query with a lot of conditions and joins so I would like to avoid doing the query twice just to get a count.

根据 mysql文档，我可以做这个:

mysql> SELECT SQL_CALC_FOUND_ROWS * FROM tbl_name
    -> WHERE id > 100 LIMIT 10;
mysql> SELECT FOUND_ROWS();

但是当我一次收到成千上万个请求时会发生什么，最终会发生这种情况:

But what happens when i'm getting thousands of requests at a time, eventually there will be an instance where this happens:

mysql> SELECT SQL_CALC_FOUND_ROWS * FROM tbl_name
    -> WHERE __CONDITION1__ > 100 LIMIT 10; //count from query 1
mysql> SELECT SQL_CALC_FOUND_ROWS * FROM tbl_name
    -> WHERE __CONDITION2__ LIMIT 10; //count from query 2
mysql> SELECT FOUND_ROWS(); //should be count form query 1 but it's count from query 2
mysql> SELECT FOUND_ROWS(); // count from query 2 but it's always 1

我已将查询封装在单独的事务中，但据我了解不能保证会阻止这种竞争情况.

I've encapsulated the queries in separate transactions, but as far as I understand there's no guarantee it'll prevent this race condition.

所以有两个问题，我能以某种方式强迫我的交易来防止这种竞争状况吗? 如果没有，是否还有另一种方法可以执行而又无需再次查询并获取计数呢?

So there's two questions, can i somehow force my transaction to prevent this race condition? If not, is there another way of doing it without doing the query again and retrieving a count?

推荐答案

涉及事务时存在某些问题，不同的隔离级别会或多或少地阻止它们.我已经在此处中对此进行了描述.
例如，像幻像读取之类的问题可能会影响选择的结果，就像您这样做一样.但是，SQL_CALC_FOUND_ROWS的结果将在查询结束后立即存储，并在同一会话中执行另一个查询后立即丢失.那是重要的部分. SQL_CALC_FOUND_ROWS已会话绑定.无法将另一个会话中另一个查询的结果存储在当前会话中. SQL_CALC_FOUND_ROWS的使用不受竞争条件的约束.是SELECT查询的结果，但不是FOUND_ROWS()的结果.请勿混淆.

There are certain problems when it comes to transactions and different isolation levels prevent more or less of them. I've described this in my answer here.
A problem like the phantom read for example, can affect the result of a select like you're doing it, yes. But the result of SQL_CALC_FOUND_ROWS is stored as soon as the query finishes and is lost as soon as you execute another query in the same session. That is the important part. SQL_CALC_FOUND_ROWS is session bound. There is no way, that the result of another query in another session is stored in your current session. The use of SQL_CALC_FOUND_ROWS is not subject to race conditions. The result of the SELECT query, yes, but not the result of FOUND_ROWS(). Don't confuse this.

这篇关于如何防止select found_rows在竞争条件下中断?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！