when complex cross join contained in a xa-transaction, the statement commit can't return

[funnyAnt]

• dble version：
  2.19.01.0
• preconditions ：
  no
• configs：

schema.xml

<schema name="dbtest">
		<table dataNode="dn1,dn2" name="sbtest1" rule="mod2" />
   </schema>

	<dataNode name="dn1" dataHost="localhost1" database="sysbench_0" />
	<dataNode name="dn2" dataHost="localhost1" database="sysbench_1" />

rule.xml

	<tableRule name="mod2">
		<rule>
			<columns>id</columns>
			<algorithm>hashmod2</algorithm>
		</rule>
	</tableRule>
	<function name="hashmod2" class="Hash">
		<property name="partitionCount">2</property>
		<property name="partitionLength">1</property>
	</function>

server.xml

• steps：
  step1. create jdbc class ,contain the following codes:
  Class.forName("com.mysql.jdbc.Driver"); conn = DriverManager.getConnection("jdbc:mysql://ip:port/dbtest?user=root&password=123"); Statement stat = conn.createStatement(); stat.execute("begin"); stat.execute("set xa=1"); stat.executeQuery("SELECT * FROM( SELECT 1 FROM sbtest1 T where id in(1,2) ORDER BY id) S"); stat.execute("commit");

  step2. run this jdbc class

• expect result：

  1. The jdbc program ends normally

• real result：

  1. stat.execute("commit") can't return

• supplements：
  1.

/label ~BUG

[funnyAnt]

• The current modification is that all connections involved in the transaction are not released.

• Such a modification today I have a new idea, compared to the implementation of mycat, the select statement in the transaction, will not open XA, rowEofReponse will determine if the connection does not modify the operation will release the connection, so as little as possible to occupy the connection.

• Question:
  So, relative to complex queries, is it possible to determine the release of the connection that can be released in the OutputHandler?

  Answer: In this scenario: begin;select;select;commit; may not behave correctly under Repeatable Read

[funnyAnt]

After the 962a9fd commit, a new error occurs when the following statement is executed。 [ERROR 1003 (HY000): XAER_DUPID: The XID already exists]

mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> set xa=1;
Query OK, 0 rows affected (0.00 sec)

mysql> explain select 1 from sbtest1 a,sbtest1 b where a.id in(1,2) and b.id in(1,2) limit 1;
+-----------------+---------------+------------------------------------------------------------------------------------+
| DATA_NODE       | TYPE          | SQL/REF                                                                            |
+-----------------+---------------+------------------------------------------------------------------------------------+
| dn1_0           | BASE SQL      | select `a`.`id`,`a`.`k`,`a`.`c`,`a`.`pad` from  `sbtest1` `a` where a.id IN (1, 2) |
| dn2_0           | BASE SQL      | select `a`.`id`,`a`.`k`,`a`.`c`,`a`.`pad` from  `sbtest1` `a` where a.id IN (1, 2) |
| merge_1         | MERGE         | dn1_0; dn2_0                                                                       |
| shuffle_field_1 | SHUFFLE_FIELD | merge_1                                                                            |
| dn1_1           | BASE SQL      | select `b`.`id`,`b`.`k`,`b`.`c`,`b`.`pad` from  `sbtest1` `b` where b.id IN (1, 2) |
| dn2_1           | BASE SQL      | select `b`.`id`,`b`.`k`,`b`.`c`,`b`.`pad` from  `sbtest1` `b` where b.id IN (1, 2) |
| merge_2         | MERGE         | dn1_1; dn2_1                                                                       |
| shuffle_field_3 | SHUFFLE_FIELD | merge_2                                                                            |
| join_1          | JOIN          | shuffle_field_1; shuffle_field_3                                                   |
| limit_1         | LIMIT         | join_1                                                                             |
| shuffle_field_2 | SHUFFLE_FIELD | limit_1                                                                            |
+-----------------+---------------+------------------------------------------------------------------------------------+
11 rows in set (0.04 sec)

mysql> select 1 from sbtest1 a,sbtest1 b where a.id in(1,2) and b.id in(1,2) limit 1;
ERROR 1003 (HY000): XAER_DUPID: The XID already exists

Solving this problem requires redesigning the XAID generation mechanism.