You are viewing a plain text version of this content. The canonical link for it is here.
Posted to notifications@shardingsphere.apache.org by GitBox <gi...@apache.org> on 2021/10/21 03:17:21 UTC

[GitHub] [shardingsphere] roodkcab opened a new pull request #13187: support XA transaction for proxy #12771

roodkcab opened a new pull request #13187:
URL: https://github.com/apache/shardingsphere/pull/13187


   Fixes #12771 .
   
   Changes proposed in this pull request:
   add MySQL XA transaction for proxy
   


-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: notifications-unsubscribe@shardingsphere.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org



[GitHub] [shardingsphere] zhfeng commented on pull request #13187: support XA transaction for proxy #12771

Posted by GitBox <gi...@apache.org>.
zhfeng commented on pull request #13187:
URL: https://github.com/apache/shardingsphere/pull/13187#issuecomment-949429398


   I wonder that there is any problem if we just broacast the XA related statements to the backend databases ?


-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: notifications-unsubscribe@shardingsphere.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org



[GitHub] [shardingsphere] roodkcab commented on pull request #13187: support XA transaction for proxy #12771

Posted by GitBox <gi...@apache.org>.
roodkcab commented on pull request #13187:
URL: https://github.com/apache/shardingsphere/pull/13187#issuecomment-949556960


   > @roodkcab It looks like `MySQLXARecoverStatement` has not been handled. 
   Right, I'm thinking of send it to backend database directly. 


-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: notifications-unsubscribe@shardingsphere.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org



[GitHub] [shardingsphere] zhfeng commented on a change in pull request #13187: support XA transaction for proxy #12771

Posted by GitBox <gi...@apache.org>.
zhfeng commented on a change in pull request #13187:
URL: https://github.com/apache/shardingsphere/pull/13187#discussion_r734479989



##########
File path: shardingsphere-proxy/shardingsphere-proxy-backend/src/main/java/org/apache/shardingsphere/proxy/backend/text/transaction/TransactionBackendHandlerFactory.java
##########
@@ -60,6 +76,35 @@ public static TextProtocolBackendHandler newInstance(final SQLStatementContext<?
             }
             return new TransactionBackendHandler(tclStatement, TransactionOperationType.BEGIN, backendConnection);
         }
+        if (tclStatement instanceof CommitStatement) {
+            return new TransactionBackendHandler(tclStatement, TransactionOperationType.COMMIT, backendConnection);
+        }
+        if (tclStatement instanceof RollbackStatement) {
+            return new TransactionBackendHandler(tclStatement, TransactionOperationType.ROLLBACK, backendConnection);
+        }
+        return null;
+    }
+
+    private static TextProtocolBackendHandler xAHandler(final TCLStatement tclStatement, final BackendConnection backendConnection) {
+        if (tclStatement instanceof MySQLXABeginStatement) {
+            return new TransactionBackendHandler(tclStatement, TransactionOperationType.BEGIN, backendConnection);
+        }
+        if (tclStatement instanceof MySQLXAEndStatement) {
+            return new TransactionBackendHandler(tclStatement, TransactionOperationType.SKIP, backendConnection);
+        }
+        if (tclStatement instanceof MySQLXAPrepareStatement) {
+            return new TransactionBackendHandler(tclStatement, TransactionOperationType.SKIP, backendConnection);
+        }
+        if (tclStatement instanceof MySQLXACommitStatement) {
+            return new TransactionBackendHandler(tclStatement, TransactionOperationType.COMMIT, backendConnection);
+        }
+        if (tclStatement instanceof MySQLXARollbackStatement) {
+            return new TransactionBackendHandler(tclStatement, TransactionOperationType.ROLLBACK, backendConnection);
+        }
+        return null;
+    }

Review comment:
       I'd like to suggest that move all of these XA Stattement handler to ```XABackendHandlerFactory``` class.




-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: notifications-unsubscribe@shardingsphere.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org



[GitHub] [shardingsphere] zhfeng commented on pull request #13187: support XA transaction for proxy #12771

Posted by GitBox <gi...@apache.org>.
zhfeng commented on pull request #13187:
URL: https://github.com/apache/shardingsphere/pull/13187#issuecomment-994553836


   Thanks @roodkcab and please add the link of in #12771. Also if you can write in English, that would be great.


-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: notifications-unsubscribe@shardingsphere.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org



[GitHub] [shardingsphere] roodkcab commented on pull request #13187: support XA transaction for proxy #12771

Posted by GitBox <gi...@apache.org>.
roodkcab commented on pull request #13187:
URL: https://github.com/apache/shardingsphere/pull/13187#issuecomment-994581164


   > Thanks @roodkcab and please add the link of in #12771. Also if you can write in English, that would be great.
   
   no problem, I've add this proposal to issue.


-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: notifications-unsubscribe@shardingsphere.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org



[GitHub] [shardingsphere] roodkcab commented on pull request #13187: support XA transaction for proxy #12771

Posted by GitBox <gi...@apache.org>.
roodkcab commented on pull request #13187:
URL: https://github.com/apache/shardingsphere/pull/13187#issuecomment-949554624


   > I wonder that there is any problem if we just broacast the XA related statements to the backend databases ?
   
   Yes shardingsphere reports Failed to switch schema, please terminate current transaction


-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: notifications-unsubscribe@shardingsphere.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org



[GitHub] [shardingsphere] tristaZero edited a comment on pull request #13187: support XA transaction for proxy #12771

Posted by GitBox <gi...@apache.org>.
tristaZero edited a comment on pull request #13187:
URL: https://github.com/apache/shardingsphere/pull/13187#issuecomment-949683545


   Hi, Since this thread is a little long, I do not track them all, but I'd like to give some inputs.
   
   ```sql
   set autocommit = 0;
   xa begin ...
   xa commit ...
   ```
   Actually, `set autocommit = 0` hints an implicit `COMMIT`, so you will see these coding here,
   ```java
   if (((SetAutoCommitStatement) tclStatement).isAutoCommit()) { 
            return backendConnection.getTransactionStatus().isInTransaction() 
                    ? new TransactionBackendHandler(tclStatement, TransactionOperationType.COMMIT, backendConnection) : new SkipBackendHandler(tclStatement); 
        } 
   ```
   It needs to finish the last transaction and prepare the next one.


-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: notifications-unsubscribe@shardingsphere.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org



[GitHub] [shardingsphere] roodkcab commented on pull request #13187: support XA transaction for proxy #12771

Posted by GitBox <gi...@apache.org>.
roodkcab commented on pull request #13187:
URL: https://github.com/apache/shardingsphere/pull/13187#issuecomment-950449532


   > Yep, we need to control trx flow. Plus, I presume at this moment, we need to consider how to gracefully support XA transaction. I see @roodkcab did a lot of work on parsing and implementing XA SQL. Why isn't XA SQL supported so far? Actually, ShardingSpehre planned to hide the distributed transaction from users. Users do not need to be aware of the distributed transactions as if they are using one DBMS (Local trx). Imaging users take Proxy as a standalone DB, it's supposed that no distributed trx comes to their mind. That's why we haven't supported XA trx so far. We hope to hide and simplify the distributed transaction implementance from users. Only by calling non-distributed transactions simple API, users can achieve distributed trx. The key role during this process is ShardingSphere.
   
   I see, it's really nice to support XA trx transparently. I don't think there's any easy way to support such feature, considering we have no context to know users' SQLs to be executed without calling ```XA``` statements explicitly.
    
   > However, from distributed trx angle, It's fine to make ShardingSphere support XA API. But we need to consider how to make ShardingSphere responds to XA user API reasonably.
   
   any suggestion is wellcome.
   
   


-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: notifications-unsubscribe@shardingsphere.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org



[GitHub] [shardingsphere] zhfeng commented on pull request #13187: support XA transaction for proxy #12771

Posted by GitBox <gi...@apache.org>.
zhfeng commented on pull request #13187:
URL: https://github.com/apache/shardingsphere/pull/13187#issuecomment-957093192


   I wonder if it is possible to add a XA proxy senario in shardingsphere-integration-test-suite to verify our ideas. 


-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: notifications-unsubscribe@shardingsphere.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org



[GitHub] [shardingsphere] zhfeng commented on pull request #13187: support XA transaction for proxy #12771

Posted by GitBox <gi...@apache.org>.
zhfeng commented on pull request #13187:
URL: https://github.com/apache/shardingsphere/pull/13187#issuecomment-951664165


   @tristaZero In your cases, all of db[0-30 are the same type of databases. I think when ```begin```, the shardingsphere could start a XA transaction by TransactionManger and all of the backend connection must be XADataSource, and then
   ```
   update t_order // executed on db0, db1
   ```
   XADataSource connect to db0, db1 can be enlisted (which means joining the XA transaction) automatically, this should be supported by HikariCP.
   
   This same happens on ```update t_item```. And ```commit``` at last, it will commit all of the changes to 4 backend databases. I explain this in Shardingsphere XA mode.
   
   And yeah, different types of database can support XA protocol.


-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: notifications-unsubscribe@shardingsphere.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org



[GitHub] [shardingsphere] tristaZero edited a comment on pull request #13187: support XA transaction for proxy #12771

Posted by GitBox <gi...@apache.org>.
tristaZero edited a comment on pull request #13187:
URL: https://github.com/apache/shardingsphere/pull/13187#issuecomment-950257412


   > Then I propose the following changes with TransactionBackendHandlerFactory
   
   Honestly speaking, I am a little blurred of 
   ```java
   No.61      return new TransactionBackendHandler(tclStatement, TransactionOperationType.BEGIN, backendConnection); 
   ```
   I suppose this statement is to make ShardingSphere prepared for the next coming trx. Suppose the next coming command is a DML, e.g. `SELECT xxxx`, rather than the expected `begin trx`, what will happen? We still need implicitly **BEGIN** trx, right? (i.e., `return new TransactionBackendHandler(tclStatement, TransactionOperationType.BEGIN, backendConnection);`)
   
   Appreciate your great providing. Therefore,
   
   If `InTrx`, we need to commit whatever users request (`set auto_commit = false/true`). In contrast, if `NotInTrx`, we need to handle different situations.
   
   > https://github.com/apache/shardingsphere/blob/bfb784c9a0e47fd147c8a654c2f0b2a1e86c2c27/shardingsphere-proxy/shardingsphere-proxy-backend/src/main/java/org/apache/shardingsphere/proxy/backend/text/transaction/TransactionBackendHandlerFactory.java#L56-L61
   


-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: notifications-unsubscribe@shardingsphere.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org



[GitHub] [shardingsphere] TeslaCN commented on a change in pull request #13187: support XA transaction for proxy #12771

Posted by GitBox <gi...@apache.org>.
TeslaCN commented on a change in pull request #13187:
URL: https://github.com/apache/shardingsphere/pull/13187#discussion_r739961544



##########
File path: shardingsphere-sql-parser/shardingsphere-sql-parser-dialect/shardingsphere-sql-parser-mysql/src/main/java/org/apache/shardingsphere/sql/parser/mysql/visitor/statement/impl/MySQLTCLStatementSQLVisitor.java
##########
@@ -98,7 +108,32 @@ public ASTNode visitSavepoint(final SavepointContext ctx) {
     }
     
     @Override
-    public ASTNode visitXa(final XaContext ctx) {
-        return new MySQLXAStatement();
+    public ASTNode visitXaBegin(final XaBeginContext ctx) {
+        return new MySQLXABeginStatement();

Review comment:
       Is the `xid` been ignored?




-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: notifications-unsubscribe@shardingsphere.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org



[GitHub] [shardingsphere] roodkcab commented on a change in pull request #13187: support XA transaction for proxy #12771

Posted by GitBox <gi...@apache.org>.
roodkcab commented on a change in pull request #13187:
URL: https://github.com/apache/shardingsphere/pull/13187#discussion_r739978209



##########
File path: shardingsphere-proxy/shardingsphere-proxy-backend/src/main/java/org/apache/shardingsphere/proxy/backend/text/transaction/TransactionBackendHandler.java
##########
@@ -45,6 +47,9 @@
     public TransactionBackendHandler(final TCLStatement tclStatement, final TransactionOperationType operationType, final BackendConnection backendConnection) {
         this.tclStatement = tclStatement;
         this.operationType = operationType;
+        if (tclStatement instanceof MySQLXABeginStatement) {

Review comment:
       well, I think non distribution transaction should be LOCAL, and Atomikos or other provider is only for XA transaction.




-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: notifications-unsubscribe@shardingsphere.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org



[GitHub] [shardingsphere] tristaZero commented on pull request #13187: support XA transaction for proxy #12771

Posted by GitBox <gi...@apache.org>.
tristaZero commented on pull request #13187:
URL: https://github.com/apache/shardingsphere/pull/13187#issuecomment-963792626


   @jingshanglu Hey could you have a look here?


-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: notifications-unsubscribe@shardingsphere.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org



[GitHub] [shardingsphere] roodkcab commented on pull request #13187: support XA transaction for proxy #12771

Posted by GitBox <gi...@apache.org>.
roodkcab commented on pull request #13187:
URL: https://github.com/apache/shardingsphere/pull/13187#issuecomment-994477948


   close this pr due to https://wonderfull.feishu.cn/docs/doccn4a1x4Rmx4pEADYbtkAeLGc


-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: notifications-unsubscribe@shardingsphere.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org



[GitHub] [shardingsphere] roodkcab closed pull request #13187: support XA transaction for proxy #12771

Posted by GitBox <gi...@apache.org>.
roodkcab closed pull request #13187:
URL: https://github.com/apache/shardingsphere/pull/13187


   


-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: notifications-unsubscribe@shardingsphere.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org



[GitHub] [shardingsphere] zhfeng commented on pull request #13187: support XA transaction for proxy #12771

Posted by GitBox <gi...@apache.org>.
zhfeng commented on pull request #13187:
URL: https://github.com/apache/shardingsphere/pull/13187#issuecomment-949601110


   yeah, I think I find something related to this issue. As you said, you run the following commands:
   ```
   set autocommit = 0;
   xa begin ...
   xa commit ...
   ```
   
   I just checked https://github.com/apache/shardingsphere/blob/bfb784c9a0e47fd147c8a654c2f0b2a1e86c2c27/shardingsphere-proxy/shardingsphere-proxy-backend/src/main/java/org/apache/shardingsphere/proxy/backend/text/transaction/TransactionBackendHandlerFactory.java#L56-L61
   
   It returns a handler to execute BEGIN when ```autoCommit == false```. @strongduanmu can you explain the logic here ? I think it might be a resean that the following XA statemet throws with ```Failed to switch schema, please terminate current transaction``` because the BackendConnection is in transaction now.


-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: notifications-unsubscribe@shardingsphere.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org



[GitHub] [shardingsphere] zhfeng commented on pull request #13187: support XA transaction for proxy #12771

Posted by GitBox <gi...@apache.org>.
zhfeng commented on pull request #13187:
URL: https://github.com/apache/shardingsphere/pull/13187#issuecomment-949571431


   > Yes shardingsphere reports Failed to switch schema, please terminate current transaction, I did a step by step trace and found shardingsphere is trying to execute XA statement on every schema defined in conf.
   
   OK, I think this could a bug on shardingsphere. The XA statment should execute on all of the backend databases as the following Sql after xa_begin might be re-written and execute on different backend databases. Can you point out the codes which report the failures ? I can take a close look.
   
   Thanks !


-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: notifications-unsubscribe@shardingsphere.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org



[GitHub] [shardingsphere] zhfeng edited a comment on pull request #13187: support XA transaction for proxy #12771

Posted by GitBox <gi...@apache.org>.
zhfeng edited a comment on pull request #13187:
URL: https://github.com/apache/shardingsphere/pull/13187#issuecomment-951664165


   @tristaZero In your cases, all of db[0-3] are the same type of databases. I think when ```begin```, the shardingsphere could start a XA transaction by TransactionManger and all of the backend connection must be XADataSource, and then
   ```
   update t_order // executed on db0, db1
   ```
   XADataSource connect to db0, db1 can be enlisted (which means joining the XA transaction) automatically, this should be supported by HikariCP.
   
   This same happens on ```update t_item```. And ```commit``` at last, it will commit all of the changes to 4 backend databases. I explain this in Shardingsphere XA mode.
   
   And yeah, different types of database can support XA protocol.


-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: notifications-unsubscribe@shardingsphere.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org



[GitHub] [shardingsphere] zhfeng commented on pull request #13187: support XA transaction for proxy #12771

Posted by GitBox <gi...@apache.org>.
zhfeng commented on pull request #13187:
URL: https://github.com/apache/shardingsphere/pull/13187#issuecomment-949427542


   @roodkcab It looks like ```MySQLXARecoverStatement``` has not been handled. This could cause non-consistent data  when the transaction manager is doing the crash recovery. Also you skip the ```XAEnd``` and  ```XAPrepare```, and I think this could not be compatiable with XA protocol. 


-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: notifications-unsubscribe@shardingsphere.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org



[GitHub] [shardingsphere] strongduanmu commented on a change in pull request #13187: support XA transaction for proxy #12771

Posted by GitBox <gi...@apache.org>.
strongduanmu commented on a change in pull request #13187:
URL: https://github.com/apache/shardingsphere/pull/13187#discussion_r734338923



##########
File path: shardingsphere-proxy/shardingsphere-proxy-backend/src/main/java/org/apache/shardingsphere/proxy/backend/text/transaction/TransactionBackendHandlerFactory.java
##########
@@ -60,6 +76,35 @@ public static TextProtocolBackendHandler newInstance(final SQLStatementContext<?
             }
             return new TransactionBackendHandler(tclStatement, TransactionOperationType.BEGIN, backendConnection);
         }
+        if (tclStatement instanceof CommitStatement) {
+            return new TransactionBackendHandler(tclStatement, TransactionOperationType.COMMIT, backendConnection);
+        }
+        if (tclStatement instanceof RollbackStatement) {
+            return new TransactionBackendHandler(tclStatement, TransactionOperationType.ROLLBACK, backendConnection);
+        }
+        return null;
+    }
+
+    private static TextProtocolBackendHandler xAHandler(final TCLStatement tclStatement, final BackendConnection backendConnection) {

Review comment:
       @roodkcab `createXATransactionHandler` may be better.

##########
File path: shardingsphere-proxy/shardingsphere-proxy-backend/src/main/java/org/apache/shardingsphere/proxy/backend/text/transaction/TransactionBackendHandlerFactory.java
##########
@@ -50,6 +59,13 @@
      */
     public static TextProtocolBackendHandler newInstance(final SQLStatementContext<? extends TCLStatement> sqlStatementContext, final String sql, final BackendConnection backendConnection) {
         TCLStatement tclStatement = sqlStatementContext.getSqlStatement();
+        return requireNonNullElse(localHandler(tclStatement, backendConnection),

Review comment:
       @roodkcab Can you replace `requireNonNullElse` with `if condition return...`?

##########
File path: shardingsphere-proxy/shardingsphere-proxy-backend/src/main/java/org/apache/shardingsphere/proxy/backend/text/transaction/TransactionBackendHandlerFactory.java
##########
@@ -60,6 +76,35 @@ public static TextProtocolBackendHandler newInstance(final SQLStatementContext<?
             }
             return new TransactionBackendHandler(tclStatement, TransactionOperationType.BEGIN, backendConnection);
         }
+        if (tclStatement instanceof CommitStatement) {
+            return new TransactionBackendHandler(tclStatement, TransactionOperationType.COMMIT, backendConnection);
+        }
+        if (tclStatement instanceof RollbackStatement) {
+            return new TransactionBackendHandler(tclStatement, TransactionOperationType.ROLLBACK, backendConnection);
+        }
+        return null;

Review comment:
       Can we use `optional` result instead of null?

##########
File path: shardingsphere-sql-parser/shardingsphere-sql-parser-statement/src/main/java/org/apache/shardingsphere/sql/parser/sql/dialect/statement/mysql/tcl/MySQLXACommitStatement.java
##########
@@ -0,0 +1,26 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one or more
+ * contributor license agreements.  See the NOTICE file distributed with
+ * this work for additional information regarding copyright ownership.
+ * The ASF licenses this file to You under the Apache License, Version 2.0
+ * (the "License"); you may not use this file except in compliance with
+ * the License.  You may obtain a copy of the License at
+ *
+ *     http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing, software
+ * distributed under the License is distributed on an "AS IS" BASIS,
+ * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+ * See the License for the specific language governing permissions and
+ * limitations under the License.
+ */
+
+package org.apache.shardingsphere.sql.parser.sql.dialect.statement.mysql.tcl;
+
+import lombok.ToString;
+import org.apache.shardingsphere.sql.parser.sql.common.statement.tcl.XAStatement;
+import org.apache.shardingsphere.sql.parser.sql.dialect.statement.mysql.MySQLStatement;
+
+@ToString

Review comment:
       @roodkcab Please add java doc for these class.

##########
File path: shardingsphere-proxy/shardingsphere-proxy-backend/src/main/java/org/apache/shardingsphere/proxy/backend/text/transaction/TransactionBackendHandlerFactory.java
##########
@@ -50,6 +59,13 @@
      */
     public static TextProtocolBackendHandler newInstance(final SQLStatementContext<? extends TCLStatement> sqlStatementContext, final String sql, final BackendConnection backendConnection) {
         TCLStatement tclStatement = sqlStatementContext.getSqlStatement();
+        return requireNonNullElse(localHandler(tclStatement, backendConnection),
+                requireNonNullElse(xAHandler(tclStatement, backendConnection),
+                        requireNonNullElse(savePointHandler(tclStatement, backendConnection),
+                                new BroadcastDatabaseBackendHandler(sqlStatementContext, sql, backendConnection))));
+    }
+
+    private static TextProtocolBackendHandler localHandler(final TCLStatement tclStatement, final BackendConnection backendConnection) {

Review comment:
       @roodkcab `createLocalTransactionHandler` may be better.




-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: notifications-unsubscribe@shardingsphere.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org



[GitHub] [shardingsphere] tristaZero commented on pull request #13187: support XA transaction for proxy #12771

Posted by GitBox <gi...@apache.org>.
tristaZero commented on pull request #13187:
URL: https://github.com/apache/shardingsphere/pull/13187#issuecomment-950257412


   > Then I propose the following changes with TransactionBackendHandlerFactory
   
   Honestly speaking, I am a little blurred of 
   ```java
   No.61      return new TransactionBackendHandler(tclStatement, TransactionOperationType.BEGIN, backendConnection); 
   ```
   I suppose this statement is to make ShardingSphere prepared for the next coming trx. Suppose the next coming command is a DML, e.g. `SELECT xxxx`, rather than the expected `begin trx`, what will happen? We still need implicitly **BEGIN** trx, right?
   
   Appreciate your great providing. Therefore,
   
   If `InTrx`, we need to commit whatever users request (`set auto_commit = false/true`). In contrast, if `NotInTrx`, we need to handle different situations.
   
   > https://github.com/apache/shardingsphere/blob/bfb784c9a0e47fd147c8a654c2f0b2a1e86c2c27/shardingsphere-proxy/shardingsphere-proxy-backend/src/main/java/org/apache/shardingsphere/proxy/backend/text/transaction/TransactionBackendHandlerFactory.java#L56-L61
   


-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: notifications-unsubscribe@shardingsphere.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org



[GitHub] [shardingsphere] zhfeng commented on pull request #13187: support XA transaction for proxy #12771

Posted by GitBox <gi...@apache.org>.
zhfeng commented on pull request #13187:
URL: https://github.com/apache/shardingsphere/pull/13187#issuecomment-949661731


   OK, sure. @terrymanu can you take a look at this issue ?
   
   I think the correct logic of ```autoCommit``` could be
   
   ```
   if (isInTransaction()) {
        if (autoCommit == true) {
        // we can not set autoCommit to true during the transaction, so it should throw exception
            throw Exception ("can not set autoCommit with true in transaction");
        } else {
        // it is safe to skip this statment, because it is in transaction
            return new SkipBackendHandler();
       }
   }
   // If it is not in transaction, it should fall down to return broadcast handler to execute setAutoCommit on all backend databases.
   ```


-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: notifications-unsubscribe@shardingsphere.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org



[GitHub] [shardingsphere] zhfeng commented on pull request #13187: support XA transaction for proxy #12771

Posted by GitBox <gi...@apache.org>.
zhfeng commented on pull request #13187:
URL: https://github.com/apache/shardingsphere/pull/13187#issuecomment-951727251


   as far as I know, redis does not support XA, but it support [transaction](https://redis.io/topics/transactions) to allow the execution of a group of commands in a single step.


-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: notifications-unsubscribe@shardingsphere.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org



[GitHub] [shardingsphere] zhfeng commented on pull request #13187: support XA transaction for proxy #12771

Posted by GitBox <gi...@apache.org>.
zhfeng commented on pull request #13187:
URL: https://github.com/apache/shardingsphere/pull/13187#issuecomment-953938658


   Oh, I get a little bit further investigation. And I found that ```SetAutoCommit``` is a beginning  of a local transaction that shardingsphere is used to mark the current connection is ```inTransaction``` since ```java.sql.Connection``` does not have a ```begin()``` method.
   
   if transaction type is ```LOCAL```, localTransactionManger just to call ```setAutoCommit(false)``` in all backend databases in ```begin()``` method. And if transaction type is not ```LOCAL``` and shardingTransactionManager is set. The ```shardingTransactionManager.begin()``` will be invoked. I think understand the logic of ```SetAutoCommit``` here, is it right ? @terrymanu 
   
   So I think we have to process the ```xa_start``` with
   ```
   if (connection.isInTransaction()) {
       commit(); // commit a transaction in current connection
   }
   
   connection.setInXA(true); // set current connection in XA mode
   broadcast(); // pass the xa_start to all of the backend database
   ```
   
   in ```xa_commit``` and ```xa_rollback```
   ```
   connection.setInXA(false);
   broadcast(); 
   ```
   And in all of other local transaction operations the following check should be applied
   ```
   if (connection.isInXA()) {
       throw RuntimeException("There is a pending XA transaction !");
   }
   ```


-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: notifications-unsubscribe@shardingsphere.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org



[GitHub] [shardingsphere] zhfeng commented on pull request #13187: support XA transaction for proxy #12771

Posted by GitBox <gi...@apache.org>.
zhfeng commented on pull request #13187:
URL: https://github.com/apache/shardingsphere/pull/13187#issuecomment-952065243


   Well, I find shardingsphere handle the XADataSource enlist by themself. see
   https://github.com/apache/shardingsphere/blob/12c2f623d5ade73df1a0cb1c6911801c92a8e683/shardingsphere-kernel/shardingsphere-transaction/shardingsphere-transaction-type/shardingsphere-transaction-xa/shardingsphere-transaction-xa-core/src/main/java/org/apache/shardingsphere/transaction/xa/jta/datasource/XATransactionDataSource.java#L77-L100
   
   And there could be a bug on close() method
   https://github.com/apache/shardingsphere/blob/12c2f623d5ade73df1a0cb1c6911801c92a8e683/shardingsphere-kernel/shardingsphere-transaction/shardingsphere-transaction-type/shardingsphere-transaction-xa/shardingsphere-transaction-xa-core/src/main/java/org/apache/shardingsphere/transaction/xa/jta/datasource/XATransactionDataSource.java#L103-L109
   
   it should check the current transactio isActive. and if the transaction is active. close method must be defer to invoke after the transation is completion (commit or rollback).
   
   ```
   void close() {
       if (isActive(transaction)) {
           transation.registerSynchronization(new Synchronization () {
               @Override
                public void afterCompletion(final int status) { this.close(dataSource); }
           }
       } else {
           close(dataSource);
       }
   }
   ```


-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: notifications-unsubscribe@shardingsphere.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org



[GitHub] [shardingsphere] tristaZero edited a comment on pull request #13187: support XA transaction for proxy #12771

Posted by GitBox <gi...@apache.org>.
tristaZero edited a comment on pull request #13187:
URL: https://github.com/apache/shardingsphere/pull/13187#issuecomment-951648731


   Hi @roodkcab 
   
   > I see, it's really nice to support XA trx transparently. I don't think there's any easy way to support such feature, considering we have no context to know users' SQLs to be executed without calling XA statements explicitly.
   
   It's possible for ShardingSphere to know, as it needs to deal with users' SQL (parsing, routing and rewriting) and send them to DB. Therefore, if we find this logic SQL in one transaction will be executed in different DBs, then XA (or B.A.S.E) begins to work. Otherwise, this trx will become a local trx. From this perspective, it looks like we can provide users the local trx API but treat it as a XA internally. I am curious which case drives you to call XA API? Do you think the current transaction function of ShardingSphere can meet your needs? 
   
   Hi @zhfeng 
   I'd like to expand the distributed SQLs ShardingSphere faces,
   First, the case you mentioned (One logic SQL has multiple actual SQLs) is certainly a kind of distributed transaction. Moreover, the following one is another case.
   ```sql
   begin;
   // This SQL is going to be executed on db0, db1
   update t_order
   // This SQL will be executed on db2, db3
   update t_item
   commit;
   ```
   
   > And in term of XA support, I think the user would like to coordinate the database operation and others by the external Transaction Manager. For example, insert into db and send a message to MQ
   
   I am interested in this case. Could you explain more? I mean, like a Java program, a XA can deal with different types of databases in the same trx? Does that mean redis also implement the XA API?
   
   


-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: notifications-unsubscribe@shardingsphere.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org



[GitHub] [shardingsphere] roodkcab commented on pull request #13187: support XA transaction for proxy #12771

Posted by GitBox <gi...@apache.org>.
roodkcab commented on pull request #13187:
URL: https://github.com/apache/shardingsphere/pull/13187#issuecomment-949553037


   > @roodkcab It looks like `MySQLXARecoverStatement` has not been handled. This could cause non-consistent data when the transaction manager is doing the crash recovery. Also you skip the `XAEnd` and `XAPrepare`, and I think this could not be compatiable with XA protocol.
   
   End and Prepare is handled by ShardingSphereTransactionManager(Atomikos), which should be skipped here. For broadcasting sqls to backend database, I think it's better to implement jdbc and proxy in the same way.


-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: notifications-unsubscribe@shardingsphere.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org



[GitHub] [shardingsphere] roodkcab edited a comment on pull request #13187: support XA transaction for proxy #12771

Posted by GitBox <gi...@apache.org>.
roodkcab edited a comment on pull request #13187:
URL: https://github.com/apache/shardingsphere/pull/13187#issuecomment-949553037


   > @roodkcab It looks like `MySQLXARecoverStatement` has not been handled. This could cause non-consistent data when the transaction manager is doing the crash recovery. Also you skip the `XAEnd` and `XAPrepare`, and I think this could not be compatiable with XA protocol.
   
   ```XAEnd``` and ```XAPrepare``` is handled by ShardingSphereTransactionManager(Atomikos), which should be skipped here. For broadcasting sqls to backend database, I think it's better to implement jdbc and proxy in the same way.


-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: notifications-unsubscribe@shardingsphere.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org



[GitHub] [shardingsphere] zhfeng commented on pull request #13187: support XA transaction for proxy #12771

Posted by GitBox <gi...@apache.org>.
zhfeng commented on pull request #13187:
URL: https://github.com/apache/shardingsphere/pull/13187#issuecomment-950460294


   @tristaZero I think the idea of hiding distributed transaction from users means that in sharding database cases, for example a INSERT SQL could be rewritten to more acutal SQLs to exeucte on the different backend databases, and Shardingsphere will handle the different type of transaction (LOCAL, XA or BASE) to make sure the consistent of data which means INSERT THEM ALL OR NOTHING. Correct me if I misundertand the transaction model of shardingsphere. @terrymanu 
   
   And in term of XA support, I think the user would like to coordinate the database operation and others by the external Transaction Manager. For example, insert into db and send a message to MQ
   
   ```
   tx.begin();
   // the shardingsphere will insert into two backend databse accroding to the user_id sharding rule
   ds.executeSQL("INSERT INTO t_user VALUES ('1', 'user1'), ('2', 'user2')");
   jms.send("insert users");
   
   tx.commit(); // tx.rollback();
   ```
   
   If the Shardingsphere does not support XA, when user decide to rollback the transaction, the message will not be sent to MQ but the data have been inserted into database that could break the bussiness logic.
   


-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: notifications-unsubscribe@shardingsphere.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org



[GitHub] [shardingsphere] zhfeng commented on pull request #13187: support XA transaction for proxy #12771

Posted by GitBox <gi...@apache.org>.
zhfeng commented on pull request #13187:
URL: https://github.com/apache/shardingsphere/pull/13187#issuecomment-949709282


   I assume that shardingsphere disable ```autoCommit``` on all the connections to the backend databases. Since we have to control the transaction by ourself according to the different transactionType setting in the configuration, is it right ?


-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: notifications-unsubscribe@shardingsphere.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org



[GitHub] [shardingsphere] TeslaCN commented on a change in pull request #13187: support XA transaction for proxy #12771

Posted by GitBox <gi...@apache.org>.
TeslaCN commented on a change in pull request #13187:
URL: https://github.com/apache/shardingsphere/pull/13187#discussion_r739961544



##########
File path: shardingsphere-sql-parser/shardingsphere-sql-parser-dialect/shardingsphere-sql-parser-mysql/src/main/java/org/apache/shardingsphere/sql/parser/mysql/visitor/statement/impl/MySQLTCLStatementSQLVisitor.java
##########
@@ -98,7 +108,32 @@ public ASTNode visitSavepoint(final SavepointContext ctx) {
     }
     
     @Override
-    public ASTNode visitXa(final XaContext ctx) {
-        return new MySQLXAStatement();
+    public ASTNode visitXaBegin(final XaBeginContext ctx) {
+        return new MySQLXABeginStatement();

Review comment:
       Is the `xid` been ignored? My understanding is it may cause the data inconsistent if the transaction in ShardingSphere is not the one specified `xid` by the client.




-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: notifications-unsubscribe@shardingsphere.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org



[GitHub] [shardingsphere] roodkcab edited a comment on pull request #13187: support XA transaction for proxy #12771

Posted by GitBox <gi...@apache.org>.
roodkcab edited a comment on pull request #13187:
URL: https://github.com/apache/shardingsphere/pull/13187#issuecomment-949556960


   > @roodkcab It looks like `MySQLXARecoverStatement` has not been handled. 
   
   Right, I'm thinking of sending it to backend database directly. 


-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: notifications-unsubscribe@shardingsphere.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org



[GitHub] [shardingsphere] tristaZero commented on pull request #13187: support XA transaction for proxy #12771

Posted by GitBox <gi...@apache.org>.
tristaZero commented on pull request #13187:
URL: https://github.com/apache/shardingsphere/pull/13187#issuecomment-950255541


   > I assume that shardingsphere disable autoCommit on all the connections to the backend databases. Since we have to control the transaction by ourself according to the different transactionType setting in the configuration, is it right ?
   
   Yep, we need to control trx flow. Plus, I presume at this moment, we need to consider how to gracefully support XA transaction. I see @roodkcab did a lot of work on parsing and implementing XA SQL. Why isn't XA SQL supported so far? Actually, ShardingSpehre planned to hide the distributed transaction from users. Users do not need to be aware of the distributed transactions as if they are using one DBMS (Local trx). Imaging users take Proxy as a standalone DB, it's supposed that no distributed trx comes to their mind. That's why we haven't supported XA trx so far. We hope to hide and simplify the distributed transaction implementance from users. Only by calling non-distributed transactions simple API, users can achieve distributed trx. The key role during this process is ShardingSphere.
   
   However, from distributed trx angle, It's fine to make ShardingSphere support XA API. But we need to consider how to make ShardingSphere responds to XA user API reasonably.
   
   


-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: notifications-unsubscribe@shardingsphere.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org



[GitHub] [shardingsphere] zhfeng commented on a change in pull request #13187: support XA transaction for proxy #12771

Posted by GitBox <gi...@apache.org>.
zhfeng commented on a change in pull request #13187:
URL: https://github.com/apache/shardingsphere/pull/13187#discussion_r740070976



##########
File path: shardingsphere-sql-parser/shardingsphere-sql-parser-dialect/shardingsphere-sql-parser-mysql/src/main/java/org/apache/shardingsphere/sql/parser/mysql/visitor/statement/impl/MySQLTCLStatementSQLVisitor.java
##########
@@ -98,7 +108,32 @@ public ASTNode visitSavepoint(final SavepointContext ctx) {
     }
     
     @Override
-    public ASTNode visitXa(final XaContext ctx) {
-        return new MySQLXAStatement();
+    public ASTNode visitXaBegin(final XaBeginContext ctx) {
+        return new MySQLXABeginStatement();

Review comment:
       yeah, because the JTA and XA are different protocol.
   
   - JTA is the API used between the Application (AP) and Transaction Manager (TM) .
   - XA is used between Transaction Manager (TM) and Resource Manager (RM) (such as database, message broker, ...) to coordinate during the 2PC commit.
   
   So we can not use JTA here to handle the XA statments




-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: notifications-unsubscribe@shardingsphere.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org



[GitHub] [shardingsphere] roodkcab edited a comment on pull request #13187: support XA transaction for proxy #12771

Posted by GitBox <gi...@apache.org>.
roodkcab edited a comment on pull request #13187:
URL: https://github.com/apache/shardingsphere/pull/13187#issuecomment-994477948


   close this pr due to [proposal](https://wonderfull.feishu.cn/docs/doccn4a1x4Rmx4pEADYbtkAeLGc)


-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: notifications-unsubscribe@shardingsphere.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org



[GitHub] [shardingsphere] TeslaCN commented on a change in pull request #13187: support XA transaction for proxy #12771

Posted by GitBox <gi...@apache.org>.
TeslaCN commented on a change in pull request #13187:
URL: https://github.com/apache/shardingsphere/pull/13187#discussion_r739938463



##########
File path: shardingsphere-proxy/shardingsphere-proxy-backend/src/main/java/org/apache/shardingsphere/proxy/backend/text/transaction/TransactionBackendHandler.java
##########
@@ -45,6 +47,9 @@
     public TransactionBackendHandler(final TCLStatement tclStatement, final TransactionOperationType operationType, final BackendConnection backendConnection) {
         this.tclStatement = tclStatement;
         this.operationType = operationType;
+        if (tclStatement instanceof MySQLXABeginStatement) {

Review comment:
       Could we consider setting the transaction type in configuration rules? Otherwise, the actual transaction type may be inconsistent with the rules.
   ```
   rules:
     - !TRANSACTION
       defaultType: XA
       providerType: Atomikos
   ```




-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: notifications-unsubscribe@shardingsphere.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org



[GitHub] [shardingsphere] zhfeng commented on pull request #13187: support XA transaction for proxy #12771

Posted by GitBox <gi...@apache.org>.
zhfeng commented on pull request #13187:
URL: https://github.com/apache/shardingsphere/pull/13187#issuecomment-953874010


   @tristaZero @roodkcab I think I have to say -1 for this PR. It is not a right way to use the internal transaction handler to process the XA operations. And it could be a risk on data consistent especially when the crash recovery happens. As I mentioned before, the ```SetAutoCommit``` handler which starts a ```BEGIN``` looks like very weird. 
   
   @roodkcab can you just remove the following line and I think all of XA related statments should be passed broadcast to the backend databases to see if it works.
   https://github.com/apache/shardingsphere/blob/bfb784c9a0e47fd147c8a654c2f0b2a1e86c2c27/shardingsphere-proxy/shardingsphere-proxy-backend/src/main/java/org/apache/shardingsphere/proxy/backend/text/transaction/TransactionBackendHandlerFactory.java#L61
   
   Thanks in advance !
   


-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: notifications-unsubscribe@shardingsphere.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org



[GitHub] [shardingsphere] zhfeng commented on pull request #13187: support XA transaction for proxy #12771

Posted by GitBox <gi...@apache.org>.
zhfeng commented on pull request #13187:
URL: https://github.com/apache/shardingsphere/pull/13187#issuecomment-954665423


   Well, there are still some minor issues in mysql ```TCLStatement.g4```. I will spend somethime during this weekend and try to resolve them and also propsal the other PR to address this issue as well.
   
   Please take a review when I get a further update.
   
   Thanks all for participating this discussion about XA support. It really gave me a chance to take a close look with transactions in shardingsphere. Hope we can sort it out with graceful way !


-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: notifications-unsubscribe@shardingsphere.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org



[GitHub] [shardingsphere] tristaZero commented on pull request #13187: support XA transaction for proxy #12771

Posted by GitBox <gi...@apache.org>.
tristaZero commented on pull request #13187:
URL: https://github.com/apache/shardingsphere/pull/13187#issuecomment-955172452


   Still help me remember my snippet coded 2, 3 years ago. Look forward to your improvement. And then I'd like to listen to your idea on this PR. @zhfeng  @strongduanmu @roodkcab 


-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: notifications-unsubscribe@shardingsphere.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org



[GitHub] [shardingsphere] zhfeng commented on pull request #13187: support XA transaction for proxy #12771

Posted by GitBox <gi...@apache.org>.
zhfeng commented on pull request #13187:
URL: https://github.com/apache/shardingsphere/pull/13187#issuecomment-957093192


   I wonder if it is possible to add a XA proxy senario in shardingsphere-integration-test-suite to verify our ideas. 


-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: notifications-unsubscribe@shardingsphere.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org



[GitHub] [shardingsphere] roodkcab commented on a change in pull request #13187: support XA transaction for proxy #12771

Posted by GitBox <gi...@apache.org>.
roodkcab commented on a change in pull request #13187:
URL: https://github.com/apache/shardingsphere/pull/13187#discussion_r739975116



##########
File path: shardingsphere-sql-parser/shardingsphere-sql-parser-dialect/shardingsphere-sql-parser-mysql/src/main/java/org/apache/shardingsphere/sql/parser/mysql/visitor/statement/impl/MySQLTCLStatementSQLVisitor.java
##########
@@ -98,7 +108,32 @@ public ASTNode visitSavepoint(final SavepointContext ctx) {
     }
     
     @Override
-    public ASTNode visitXa(final XaContext ctx) {
-        return new MySQLXAStatement();
+    public ASTNode visitXaBegin(final XaBeginContext ctx) {
+        return new MySQLXABeginStatement();

Review comment:
       yes, xid is generated by TransactionManager, and will be managed in itself, i.e. per xid per connection




-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: notifications-unsubscribe@shardingsphere.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org



[GitHub] [shardingsphere] zhfeng commented on pull request #13187: support XA transaction for proxy #12771

Posted by GitBox <gi...@apache.org>.
zhfeng commented on pull request #13187:
URL: https://github.com/apache/shardingsphere/pull/13187#issuecomment-957093192


   I wonder if it is possible to add a XA proxy senario in shardingsphere-integration-test-suite to verify our ideas. 


-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: notifications-unsubscribe@shardingsphere.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org



[GitHub] [shardingsphere] roodkcab edited a comment on pull request #13187: support XA transaction for proxy #12771

Posted by GitBox <gi...@apache.org>.
roodkcab edited a comment on pull request #13187:
URL: https://github.com/apache/shardingsphere/pull/13187#issuecomment-949556960


   > @roodkcab It looks like `MySQLXARecoverStatement` has not been handled. 
   
   Right, I'm thinking of send it to backend database directly. 


-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: notifications-unsubscribe@shardingsphere.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org



[GitHub] [shardingsphere] strongduanmu commented on pull request #13187: support XA transaction for proxy #12771

Posted by GitBox <gi...@apache.org>.
strongduanmu commented on pull request #13187:
URL: https://github.com/apache/shardingsphere/pull/13187#issuecomment-949605604


   > yeah, I think I find something related to this issue. As you said, you run the following commands:
   > 
   > ```
   > set autocommit = 0;
   > xa begin ...
   > xa commit ...
   > ```
   > 
   > I just checked
   > 
   > https://github.com/apache/shardingsphere/blob/bfb784c9a0e47fd147c8a654c2f0b2a1e86c2c27/shardingsphere-proxy/shardingsphere-proxy-backend/src/main/java/org/apache/shardingsphere/proxy/backend/text/transaction/TransactionBackendHandlerFactory.java#L56-L61
   > 
   > It returns a handler to execute BEGIN when `autoCommit == false`. @strongduanmu can you explain the logic here ? I think it might be a resean that the following XA statemet throws with `Failed to switch schema, please terminate current transaction` because the BackendConnection is in transaction now.
   
   Hi @zhfeng, this logic has existed for a long time, and I think @terrymanu  can give a detailed explanation.


-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: notifications-unsubscribe@shardingsphere.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org



[GitHub] [shardingsphere] tristaZero commented on pull request #13187: support XA transaction for proxy #12771

Posted by GitBox <gi...@apache.org>.
tristaZero commented on pull request #13187:
URL: https://github.com/apache/shardingsphere/pull/13187#issuecomment-949683545


   Hi, Since this thread is a little long, I do not track them all, but I'd like to give some inputs.
   
   ```sql
   set autocommit = 0;
   xa begin ...
   xa commit ...
   ```
   Actually, `set autocommit = 0` hints an implicit `COMMIT`, so you will see these coding here,
   ```java
   if (((SetAutoCommitStatement) tclStatement).isAutoCommit()) { 
            return backendConnection.getTransactionStatus().isInTransaction() 
                    ? new TransactionBackendHandler(tclStatement, TransactionOperationType.COMMIT, backendConnection) : new SkipBackendHandler(tclStatement); 
        } 
   ```
   


-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: notifications-unsubscribe@shardingsphere.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org



[GitHub] [shardingsphere] zhfeng edited a comment on pull request #13187: support XA transaction for proxy #12771

Posted by GitBox <gi...@apache.org>.
zhfeng edited a comment on pull request #13187:
URL: https://github.com/apache/shardingsphere/pull/13187#issuecomment-949700678


   Refer to the javadoc about ```Connection::setAutoCommit(boolean)``` https://docs.oracle.com/javase/7/docs/api/java/sql/Connection.html#setAutoCommit(boolean)
   
   ```
   NOTE: If this method is called during a transaction and the auto-commit mode is changed, the transaction is committed. If setAutoCommit is called and the auto-commit mode is not changed, the call is a no-op.
   
   Throws:
   SQLException - if a database access error occurs, setAutoCommit(true) is called while participating in a distributed transaction, or this method is called on a closed connection.
   ```
   
   Then I propose the following changes with ```TransactionBackendHandlerFactory```
   
   ```
   if (tclStatement instanceof SetAutoCommitStatement) {
               if (backendConnection.getTransactionStatus(). isInXA()) {
                    throw new SQLException("can not setAutoCommit in a distributed transaction");
               }
   
               if (((SetAutoCommitStatement) tclStatement).isAutoCommit()) {
                   return backendConnection.getTransactionStatus().isInTransaction()
                           ? new TransactionBackendHandler(tclStatement, TransactionOperationType.COMMIT, backendConnection) : new SkipBackendHandler(tclStatement);
               }
               
              return new SkipBackendHandler(tclStatement);
           }
   
   if (tclStatement instanceof XAStatement) {
             // if it is xa_start backendConnection.getTransactionStatus().setInXA(true);
             // if it is xa_commit or xa_rollback backendConnection.getTransactionStatus().setInXA(false);
   }
   ```
   
   any thought ?
   


-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: notifications-unsubscribe@shardingsphere.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org



[GitHub] [shardingsphere] tristaZero commented on pull request #13187: support XA transaction for proxy #12771

Posted by GitBox <gi...@apache.org>.
tristaZero commented on pull request #13187:
URL: https://github.com/apache/shardingsphere/pull/13187#issuecomment-953817576


   Hi Coming back to this PR, guys, do you think it's ready to merge? Or needs more changes? @zhfeng @strongduanmu 
   For me, I assume this PR focuses on users' XA requests and calls all the original transaction handlers to handle these XA requests. It looks fine to me. 


-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: notifications-unsubscribe@shardingsphere.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org



[GitHub] [shardingsphere] roodkcab commented on pull request #13187: support XA transaction for proxy #12771

Posted by GitBox <gi...@apache.org>.
roodkcab commented on pull request #13187:
URL: https://github.com/apache/shardingsphere/pull/13187#issuecomment-951657904


   > Hi @roodkcab
   > 
   > > I see, it's really nice to support XA trx transparently. I don't think there's any easy way to support such feature, considering we have no context to know users' SQLs to be executed without calling XA statements explicitly.
   > 
   > It's possible for ShardingSphere to know, as it needs to deal with users' SQL (parsing, routing and rewriting) and send them to DB. Therefore, if we find this logic SQL in one transaction will be executed in different DBs, then XA (or B.A.S.E) begins to work. Otherwise, this trx will become a local trx. 
   
   how does shardingsphere-proxy distinguish sqls on two different connections belong to one XA transaction ?
   
   >From this perspective, it looks like we can provide users the local trx API but treat it as a XA internally. I am curious which case drives you to call XA API? Do you think the current transaction function of ShardingSphere can meet your needs?
   
   It's actually some projects already use XA statements.
   


-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: notifications-unsubscribe@shardingsphere.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org



[GitHub] [shardingsphere] tristaZero commented on pull request #13187: support XA transaction for proxy #12771

Posted by GitBox <gi...@apache.org>.
tristaZero commented on pull request #13187:
URL: https://github.com/apache/shardingsphere/pull/13187#issuecomment-953804873


   @roodkcab Hi, @zhfeng gave a good explanation.
   @zhfeng Speaking of `close()` actually, I didn't find where it is called?


-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: notifications-unsubscribe@shardingsphere.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org



[GitHub] [shardingsphere] roodkcab commented on a change in pull request #13187: support XA transaction for proxy #12771

Posted by GitBox <gi...@apache.org>.
roodkcab commented on a change in pull request #13187:
URL: https://github.com/apache/shardingsphere/pull/13187#discussion_r739978883



##########
File path: shardingsphere-sql-parser/shardingsphere-sql-parser-dialect/shardingsphere-sql-parser-mysql/src/main/java/org/apache/shardingsphere/sql/parser/mysql/visitor/statement/impl/MySQLTCLStatementSQLVisitor.java
##########
@@ -98,7 +108,32 @@ public ASTNode visitSavepoint(final SavepointContext ctx) {
     }
     
     @Override
-    public ASTNode visitXa(final XaContext ctx) {
-        return new MySQLXAStatement();
+    public ASTNode visitXaBegin(final XaBeginContext ctx) {
+        return new MySQLXABeginStatement();

Review comment:
       Another way is pass xid to atomikos, but I couldn't find any possible interface to use. By the way JTA only provides begin, commit and rollback.




-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: notifications-unsubscribe@shardingsphere.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org



[GitHub] [shardingsphere] roodkcab edited a comment on pull request #13187: support XA transaction for proxy #12771

Posted by GitBox <gi...@apache.org>.
roodkcab edited a comment on pull request #13187:
URL: https://github.com/apache/shardingsphere/pull/13187#issuecomment-949554624


   > I wonder that there is any problem if we just broacast the XA related statements to the backend databases ?
   
   Yes shardingsphere reports Failed to switch schema, please terminate current transaction, I did a step by step trace and found shardingsphere is trying to execute XA statement on every schema defined in conf.


-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: notifications-unsubscribe@shardingsphere.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org



[GitHub] [shardingsphere] zhfeng edited a comment on pull request #13187: support XA transaction for proxy #12771

Posted by GitBox <gi...@apache.org>.
zhfeng edited a comment on pull request #13187:
URL: https://github.com/apache/shardingsphere/pull/13187#issuecomment-949689809


   @tristaZero OK, I understand. It makes sense here. But why do we need to begin a new local transaction when ```autoCommit == false```
   ```
   return new TransactionBackendHandler(tclStatement, TransactionOperationType.BEGIN, backendConnection); 
   ```


-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: notifications-unsubscribe@shardingsphere.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org



[GitHub] [shardingsphere] tristaZero commented on pull request #13187: support XA transaction for proxy #12771

Posted by GitBox <gi...@apache.org>.
tristaZero commented on pull request #13187:
URL: https://github.com/apache/shardingsphere/pull/13187#issuecomment-951648731


   Hi @roodkcab 
   
   > I see, it's really nice to support XA trx transparently. I don't think there's any easy way to support such feature, considering we have no context to know users' SQLs to be executed without calling XA statements explicitly.
   
   It's possible for ShardingSphere to know, as it needs to deal with users' SQL (parsing, routing and rewriting) and send them to DB. Therefore, if we find this logic SQL in one transaction will be executed in different DBs, then XA (or B.A.S.E) begins to work. Otherwise, this trx will become a local trx. From this perspective, it looks like we can provide users the local trx API but treat it as a XA internally. I am curious which case drives you to call XA API? Do you think the current transaction function of ShardingSphere can meet your needs? 
   
   Hi @zhfeng 
   I'd like to expand the distributed SQLs ShardingSphere faces,
   First, the case you mentioned (One logic SQL has multiple actual SQLs) is certainly a kind of distributed transaction. Moreover, the following one is another case.
   ```sql
   begin;
   // This SQL is going to be executed on db0, db1
   update t_order
   // This SQL will be executed on db2, db3
   update t_item
   commit;
   ```
   
   > And in term of XA support, I think the user would like to coordinate the database operation and others by the external Transaction Manager. For example, insert into db and send a message to MQ
   
   I am interested in this case. Could you explain more? I mean,
   First, like a Java program, a XA can deal with different types of databases in the same trx? Does that mean redis also implement the XA API?
   
   


-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: notifications-unsubscribe@shardingsphere.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org



[GitHub] [shardingsphere] zhfeng commented on pull request #13187: support XA transaction for proxy #12771

Posted by GitBox <gi...@apache.org>.
zhfeng commented on pull request #13187:
URL: https://github.com/apache/shardingsphere/pull/13187#issuecomment-949700678


   Refer to the javadoc about ```Connection::setAutoCommit(boolean)``` https://docs.oracle.com/javase/7/docs/api/java/sql/Connection.html#setAutoCommit(boolean)
   
   ```
   NOTE: If this method is called during a transaction and the auto-commit mode is changed, the transaction is committed. If setAutoCommit is called and the auto-commit mode is not changed, the call is a no-op.
   
   Throws:
   SQLException - if a database access error occurs, setAutoCommit(true) is called while participating in a distributed transaction, or this method is called on a closed connection.
   ```
   
   Then I propose the following changes with ```TransactionBackendHandlerFactory```
   
   ```
   if (tclStatement instanceof SetAutoCommitStatement) {
               if (backendConnection.getTransactionStatus(). isInXA()) {
                    throw new SQLException("can not setAutoCommit in a distributed transaction");
               }
   
               if (((SetAutoCommitStatement) tclStatement).isAutoCommit()) {
                   return backendConnection.getTransactionStatus().isInTransaction()
                           ? new TransactionBackendHandler(tclStatement, TransactionOperationType.COMMIT, backendConnection) : new SkipBackendHandler(tclStatement);
               }
               
               // fall down to return broadcast 
           }
   
   if (tclStatement instanceof XAStatement) {
             // if it is xa_start backendConnection.getTransactionStatus().setInXA(true);
             // if it is xa_commit or xa_rollback backendConnection.getTransactionStatus().setInXA(false);
   }
   ```
   
   any thought ?
   


-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: notifications-unsubscribe@shardingsphere.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org



[GitHub] [shardingsphere] zhfeng commented on pull request #13187: support XA transaction for proxy #12771

Posted by GitBox <gi...@apache.org>.
zhfeng commented on pull request #13187:
URL: https://github.com/apache/shardingsphere/pull/13187#issuecomment-949689809


   @tristaZero OK, I understand. It makes sense here. But why do we need to begin a new local transaction
   ```
   return new TransactionBackendHandler(tclStatement, TransactionOperationType.BEGIN, backendConnection); 
   ```


-- 
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.

To unsubscribe, e-mail: notifications-unsubscribe@shardingsphere.apache.org

For queries about this service, please contact Infrastructure at:
users@infra.apache.org