分支注册，主键超过1000的时候会转为or，但是使用or在mysql索引会失效

[remind]

• I have searched the issues of this repository and believe that this is not a duplicate.

Ⅰ. Issue Description

分支注册，主键超过1000的时候会转为or，但是使用or在mysql索引会失效

应该是修改 #3946 时引入的，最好不要用or。

优化建议：
1、让最大1000可配置。
2、用union或者多条sql查询。

Ⅱ. Describe what happened

If there is an exception, please attach the exception trace:

数据量大的时候就会形成慢sql，导致执行不成功。

Just paste your stack trace here!

Ⅲ. Describe what you expected to happen

能够正常执行

Ⅳ. How to reproduce it (as minimally and precisely as possible)

可参考：#3946

Minimal yet complete reproducer code (or URL to code):

Ⅴ. Anything else we need to know?

Ⅵ. Environment:

JDK version : 1.8
Others: seata1.8.0

[funky-eyes]

我认为我们可以讨论下这个优化的方式，为了避免oracle在查询大于1000条全局锁时报错而处理的，我认为暂时可以按照第一点处理，因为这个1000条理论上是可配置的，当用户配置后，再配置seata侧的限制，这样就可以比较简单的处理这个问题。

I think we can discuss the optimization approach. To avoid errors from Oracle when select more than 1000 global locks, I believe we can temporarily handle it according to the first point. Since this limit of 1000 is theoretically configurable, users can adjust their settings, and then we can configure the corresponding limits on the Seata side. This way, we can handle the issue more straightforwardly.

[remind]

我认为我们可以讨论下这个优化的方式，为了避免oracle在删除大于1000条全局锁时报错而处理的，我认为暂时可以按照第一点处理，因为这个1000条理论上是可配置的，当用户配置后，再配置seata侧的限制，这样就可以比较简单的处理这个问题。

I think we can discuss the optimization approach. To avoid errors from Oracle when delete more than 1000 global locks, I believe we can temporarily handle it according to the first point. Since this limit of 1000 is theoretically configurable, users can adjust their settings, and then we can configure the corresponding limits on the Seata side. This way, we can handle the issue more straightforwardly.

是的，加配置相对来说变更最小同时又能解决不同数据库的限制问题

[funky-eyes]

我认为我们可以讨论下这个优化的方式，为了避免oracle在删除大于1000条全局锁时报错而处理的，我认为暂时可以按照第一点处理，因为这个1000条理论上是可配置的，当用户配置后，再配置seata侧的限制，这样就可以比较简单的处理这个问题。
I think we can discuss the optimization approach. To avoid errors from Oracle when delete more than 1000 global locks, I believe we can temporarily handle it according to the first point. Since this limit of 1000 is theoretically configurable, users can adjust their settings, and then we can configure the corresponding limits on the Seata side. This way, we can handle the issue more straightforwardly.

是的，加配置相对来说变更最小同时又能解决不同数据库的限制问题

你可以提交一个pr吗？我认为第二个方案如果能实现是最好的，短时间如果不行可以选择第一个方案，针对select而言，union拼接一下应该比较简单，delete可以setautocommit(false) ，一千条记录一条sql，分批次删，而且绝大多数场景不会涉及到一个分支拥有一千个锁，性能影响也是毕竟小的

Can you submit a PR? I think the second approach would be the best if it can be implemented; if not feasible in a short time, we can go with the first approach. For SELECT, using UNION to concatenate results should be quite simple. For DELETE, we can use SET AUTOCOMMIT(FALSE) and execute one SQL statement per thousand records in batches. Moreover, in most scenarios, it won't involve a single branch having a thousand locks, so the performance impact would be relatively small.

[remind]

我认为我们可以讨论下这个优化的方式，为了避免oracle在删除大于1000条全局锁时报错而处理的，我认为暂时可以按照第一点处理，因为这个1000条理论上是可配置的，当用户配置后，再配置seata侧的限制，这样就可以比较简单的处理这个问题。
I think we can discuss the optimization approach. To avoid errors from Oracle when delete more than 1000 global locks, I believe we can temporarily handle it according to the first point. Since this limit of 1000 is theoretically configurable, users can adjust their settings, and then we can configure the corresponding limits on the Seata side. This way, we can handle the issue more straightforwardly.

是的，加配置相对来说变更最小同时又能解决不同数据库的限制问题

你可以提交一个pr吗？我认为第二个方案如果能实现是最好的，短时间如果不行可以选择第一个方案，针对select而言，union拼接一下应该比较简单，delete可以setautocommit(false) ，一千条记录一条sql，分批次删，而且绝大多数场景不会涉及到一个分支拥有一千个锁，性能影响也是毕竟小的

Can you submit a PR? I think the second approach would be the best if it can be implemented; if not feasible in a short time, we can go with the first approach. For SELECT, using UNION to concatenate results should be quite simple. For DELETE, we can use SET AUTOCOMMIT(FALSE) and execute one SQL statement per thousand records in batches. Moreover, in most scenarios, it won't involve a single branch having a thousand locks, so the performance impact would be relatively small.

好，delete那里我还没有看的，我先看看

[funky-eyes]

我认为我们可以讨论下这个优化的方式，为了避免oracle在删除大于1000条全局锁时报错而处理的，我认为暂时可以按照第一点处理，因为这个1000条理论上是可配置的，当用户配置后，再配置seata侧的限制，这样就可以比较简单的处理这个问题。
I think we can discuss the optimization approach. To avoid errors from Oracle when delete more than 1000 global locks, I believe we can temporarily handle it according to the first point. Since this limit of 1000 is theoretically configurable, users can adjust their settings, and then we can configure the corresponding limits on the Seata side. This way, we can handle the issue more straightforwardly.

是的，加配置相对来说变更最小同时又能解决不同数据库的限制问题

你可以提交一个pr吗？我认为第二个方案如果能实现是最好的，短时间如果不行可以选择第一个方案，针对select而言，union拼接一下应该比较简单，delete可以setautocommit(false) ，一千条记录一条sql，分批次删，而且绝大多数场景不会涉及到一个分支拥有一千个锁，性能影响也是毕竟小的
Can you submit a PR? I think the second approach would be the best if it can be implemented; if not feasible in a short time, we can go with the first approach. For SELECT, using UNION to concatenate results should be quite simple. For DELETE, we can use SET AUTOCOMMIT(FALSE) and execute one SQL statement per thousand records in batches. Moreover, in most scenarios, it won't involve a single branch having a thousand locks, so the performance impact would be relatively small.

好，delete那里我还没有看的，我先看看

可以先分2个pr进行提交，先修复select那块
You can commit it in 2 pr first, and fix the select piece first.

[remind]

执行用例io.seata.rm.datasource.exec.MultiExecutorTest#testBeforeImageAndAfterImages

在io.seata.rm.datasource.mock.MockExecuteHandlerImpl#executeQuery中指定SQLSelectQueryBlock取，在加了union后为SQLUnionQuery会异常

在io.seata.sqlparser.druid.mysql.MySQLSelectForUpdateRecognizer#getSelect里面涉及得也非常多

此处是测试用例Mock写的，可以调整，不确定其它地方是否有此要求 @funky-eyes

[funky-eyes]

@remind 可以调整，只要能达到测试的目的即可，抱歉我之前理解你这个issue的问是server侧的解锁和查锁，你现在说的是客户端侧进行update的时候，查询前后镜像时的问题吧？

[remind]

@remind 可以调整，只要能达到测试的目的即可，抱歉我之前理解你这个issue的问是server侧的解锁和查锁，你现在说的是客户端侧进行update的时候，查询前后镜像时的问题吧？

嗯，这个方法使用的地方比较多，目前直接调用已经改了的就有：

还有因为sql变化引起的一些影响，如上面说到的 SQLSelectQuery 类型变化兼容处理。