search

apache / incubator-seata

:fire: Seata is an easy-to-use, high-performance, open source distributed transaction solution.
https://seata.apache.org/
Apache License 2.0
25.16k stars 8.74k forks source link

XA模式，Oracle数据源，多个服务不能操作同一数据库？ #2791

Open zengjklast opened 4 years ago

zengjklast commented 4 years ago

官方的xa模式的例子是基于mysql的，将mysql数据源更换为oracle数据源之后，报错： ORA-24756：事务处理不存在，堆栈信息如下： image 不知是哪里配置有问题，能否提供一个基于Oracle数据库的XA模式的例子？ 感激不尽！

经排查，多个服务似乎不支持操作同一数据库，否则就会报上述错误，如A和B两个服务，A和B需操作不同数据库，且数据库操作只能增删改，不能仅仅是查询操作？

slievrly commented 4 years ago

@zengjklast confirmation is not the same database or under the same database and same data?

zengjklast commented 4 years ago

@slievrly 是不能操作同一数据库。A和B两个服务操作两张不同的表。两张表在同一数据库下，就会报上述错误。不同的数据库则没问题。mysql也没问题。不知道oracle是否需要额外的配置（数据库层面）？多谢指教。

qiaolin1209 commented 4 years ago

@slievrly 我这边也验证了XA的oracle情况，如果两个服务连得同一个库。被调服务在执行业务SQL时会出现SQLException，failed to start xa branch 报错如下： image

上面的事务不存在的报错是正确的，因为分支事务压根就没开启成果过。

麻烦看看，oracle XA不分库的情况是否有问题，是否seata 有缺陷？mysql这块，做了相同验证，是没有问题的。

robinZhao commented 3 years ago

same problem

全局事务id ：192.168.202.58:8091:69346279655972864
2020-11-10 08:37:21.480 ERROR 20012 --- [:20881-thread-8] o.a.dubbo.rpc.filter.ExceptionFilter     :  [DUBBO] Got unchecked and undeclared exception which called by 192.168.202.58. service: io.seata.samples.integration.common.dubbo.OrderDubboService, method: createOrder, exception: org.springframework.transaction.CannotCreateTransactionException: Could not open JDBC Connection for transaction; nested exception is java.sql.SQLException: failed to start xa branch 192.168.202.58:8091:69346279655972864 since XAErr (-3): A resource manager error has occured in the transaction branch. ORA-2079 SQLErr (0), dubbo version: 2.7.7, current host: 192.168.202.58

org.springframework.transaction.CannotCreateTransactionException: Could not open JDBC Connection for transaction; nested exception is java.sql.SQLException: failed to start xa branch 192.168.202.58:8091:69346279655972864 since XAErr (-3): A resource manager error has occured in the transaction branch. ORA-2079 SQLErr (0)
    at org.springframework.jdbc.datasource.DataSourceTransactionManager.doBegin(DataSourceTransactionManager.java:305) ~[spring-jdbc-5.2.8.RELEASE.jar:5.2.8.RELEASE]
    at org.springframework.transaction.support.AbstractPlatformTransactionManager.startTransaction(AbstractPlatformTransactionManager.java:400) ~[spring-tx-5.2.8.RELEASE.jar:5.2.8.RELEASE]
    at org.springframework.transaction.support.AbstractPlatformTransactionManager.getTransaction(AbstractPlatformTransactionManager.java:373) ~[spring-tx-5.2.8.RELEASE.jar:5.2.8.RELEASE]
    at org.springframework.transaction.interceptor.TransactionAspectSupport.createTransactionIfNecessary(TransactionAspectSupport.java:574) ~[spring-tx-5.2.8.RELEASE.jar:5.2.8.RELEASE]
    at org.springframework.transaction.interceptor.TransactionAspectSupport.invokeWithinTransaction(TransactionAspectSupport.java:361) ~[spring-tx-5.2.8.RELEASE.jar:5.2.8.RELEASE]
    at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:118) ~[spring-tx-5.2.8.RELEASE.jar:5.2.8.RELEASE]
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:186) ~[spring-aop-5.2.8.RELEASE.jar:5.2.8.RELEASE]
    at org.springframework.aop.framework.CglibAopProxy$CglibMethodInvocation.proceed(CglibAopProxy.java:749) ~[spring-aop-5.2.8.RELEASE.jar:5.2.8.RELEASE]
    at org.springframework.aop.framework.CglibAopProxy$DynamicAdvisedInterceptor.intercept(CglibAopProxy.java:691) ~[spring-aop-5.2.8.RELEASE.jar:5.2.8.RELEASE]
    at io.seata.samples.integration.order.service.TOrderServiceImpl$$EnhancerBySpringCGLIB$$483b7dbd.createOrder(<generated>) ~[classes/:na]
    at io.seata.samples.integration.order.dubbo.OrderDubboServiceImpl.createOrder(OrderDubboServiceImpl.java:27) ~[classes/:na]
    at org.apache.dubbo.common.bytecode.Wrapper2.invokeMethod(Wrapper2.java) ~[dubbo-2.7.7.jar:2.7.7]
    at org.apache.dubbo.rpc.proxy.javassist.JavassistProxyFactory$1.doInvoke(JavassistProxyFactory.java:47) ~[dubbo-2.7.7.jar:2.7.7]
    at org.apache.dubbo.rpc.proxy.AbstractProxyInvoker.invoke(AbstractProxyInvoker.java:84) ~[dubbo-2.7.7.jar:2.7.7]
    at org.apache.dubbo.config.invoker.DelegateProviderMetaDataInvoker.invoke(DelegateProviderMetaDataInvoker.java:56) ~[dubbo-2.7.7.jar:2.7.7]
    at org.apache.dubbo.rpc.protocol.InvokerWrapper.invoke(InvokerWrapper.java:56) ~[dubbo-2.7.7.jar:2.7.7]
    at com.alibaba.dubbo.rpc.Invoker$CompatibleInvoker.invoke(Invoker.java:55) ~[dubbo-2.7.7.jar:2.7.7]
    at io.seata.integration.dubbo.alibaba.AlibabaDubboTransactionPropagationFilter.invoke(AlibabaDubboTransactionPropagationFilter.java:45) ~[seata-all-1.3.0.jar:1.3.0]
    at com.alibaba.dubbo.rpc.Filter.invoke(Filter.java:29) ~[dubbo-2.7.7.jar:2.7.7]
    at org.apache.dubbo.rpc.protocol.ProtocolFilterWrapper$1.invoke(ProtocolFilterWrapper.java:81) ~[dubbo-2.7.7.jar:2.7.7]
    at io.seata.integration.dubbo.ApacheDubboTransactionPropagationFilter.invoke(ApacheDubboTransactionPropagationFilter.java:69) ~[seata-all-1.3.0.jar:1.3.0]
    at org.apache.dubbo.rpc.protocol.ProtocolFilterWrapper$1.invoke(ProtocolFilterWrapper.java:81) ~[dubbo-2.7.7.jar:2.7.7]
    at org.apache.dubbo.rpc.filter.ExceptionFilter.invoke(ExceptionFilter.java:52) ~[dubbo-2.7.7.jar:2.7.7]
    at org.apache.dubbo.rpc.protocol.ProtocolFilterWrapper$1.invoke(ProtocolFilterWrapper.java:81) ~[dubbo-2.7.7.jar:2.7.7]
    at org.apache.dubbo.monitor.support.MonitorFilter.invoke(MonitorFilter.java:89) ~[dubbo-2.7.7.jar:2.7.7]
    at org.apache.dubbo.rpc.protocol.ProtocolFilterWrapper$1.invoke(ProtocolFilterWrapper.java:81) ~[dubbo-2.7.7.jar:2.7.7]
    at org.apache.dubbo.rpc.filter.TimeoutFilter.invoke(TimeoutFilter.java:46) ~[dubbo-2.7.7.jar:2.7.7]
    at org.apache.dubbo.rpc.protocol.ProtocolFilterWrapper$1.invoke(ProtocolFilterWrapper.java:81) ~[dubbo-2.7.7.jar:2.7.7]
    at org.apache.dubbo.rpc.protocol.dubbo.filter.TraceFilter.invoke(TraceFilter.java:77) ~[dubbo-2.7.7.jar:2.7.7]
    at org.apache.dubbo.rpc.protocol.ProtocolFilterWrapper$1.invoke(ProtocolFilterWrapper.java:81) ~[dubbo-2.7.7.jar:2.7.7]
    at org.apache.dubbo.rpc.filter.ContextFilter.invoke(ContextFilter.java:129) ~[dubbo-2.7.7.jar:2.7.7]
    at org.apache.dubbo.rpc.protocol.ProtocolFilterWrapper$1.invoke(ProtocolFilterWrapper.java:81) ~[dubbo-2.7.7.jar:2.7.7]
    at org.apache.dubbo.rpc.filter.GenericFilter.invoke(GenericFilter.java:152) ~[dubbo-2.7.7.jar:2.7.7]
    at org.apache.dubbo.rpc.protocol.ProtocolFilterWrapper$1.invoke(ProtocolFilterWrapper.java:81) ~[dubbo-2.7.7.jar:2.7.7]
    at org.apache.dubbo.rpc.filter.ClassLoaderFilter.invoke(ClassLoaderFilter.java:38) ~[dubbo-2.7.7.jar:2.7.7]
    at org.apache.dubbo.rpc.protocol.ProtocolFilterWrapper$1.invoke(ProtocolFilterWrapper.java:81) ~[dubbo-2.7.7.jar:2.7.7]
    at org.apache.dubbo.rpc.filter.EchoFilter.invoke(EchoFilter.java:41) ~[dubbo-2.7.7.jar:2.7.7]
    at org.apache.dubbo.rpc.protocol.ProtocolFilterWrapper$1.invoke(ProtocolFilterWrapper.java:81) ~[dubbo-2.7.7.jar:2.7.7]
    at org.apache.dubbo.rpc.protocol.dubbo.DubboProtocol$1.reply(DubboProtocol.java:145) ~[dubbo-2.7.7.jar:2.7.7]
    at org.apache.dubbo.remoting.exchange.support.header.HeaderExchangeHandler.handleRequest(HeaderExchangeHandler.java:100) ~[dubbo-2.7.7.jar:2.7.7]
    at org.apache.dubbo.remoting.exchange.support.header.HeaderExchangeHandler.received(HeaderExchangeHandler.java:175) ~[dubbo-2.7.7.jar:2.7.7]
    at org.apache.dubbo.remoting.transport.DecodeHandler.received(DecodeHandler.java:51) ~[dubbo-2.7.7.jar:2.7.7]
    at org.apache.dubbo.remoting.transport.dispatcher.ChannelEventRunnable.run(ChannelEventRunnable.java:57) ~[dubbo-2.7.7.jar:2.7.7]
    at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149) ~[na:1.8.0_261]
    at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624) ~[na:1.8.0_261]
    at java.lang.Thread.run(Thread.java:748) ~[na:1.8.0_261]
Caused by: java.sql.SQLException: failed to start xa branch 192.168.202.58:8091:69346279655972864 since XAErr (-3): A resource manager error has occured in the transaction branch. ORA-2079 SQLErr (0)
    at io.seata.rm.datasource.xa.ConnectionProxyXA.setAutoCommit(ConnectionProxyXA.java:146) ~[seata-all-1.3.0.jar:1.3.0]
    at org.springframework.jdbc.datasource.DataSourceTransactionManager.doBegin(DataSourceTransactionManager.java:283) ~[spring-jdbc-5.2.8.RELEASE.jar:5.2.8.RELEASE]
    ... 45 common frames omitted
Caused by: oracle.jdbc.xa.OracleXAException: XAErr (-3): A resource manager error has occured in the transaction branch. ORA-2079 SQLErr (0)
    at oracle.jdbc.xa.OracleXAResource.checkError(OracleXAResource.java:1114) ~[ojdbc8-19.7.0.0.jar:19.7.0.0.0]
    at oracle.jdbc.xa.client.OracleXAResource.start(OracleXAResource.java:267) ~[ojdbc8-19.7.0.0.jar:19.7.0.0.0]
    at io.seata.rm.datasource.xa.ConnectionProxyXA.setAutoCommit(ConnectionProxyXA.java:143) ~[seata-all-1.3.0.jar:1.3.0]
    ... 46 common frames omitted
Caused by: java.sql.SQLSyntaxErrorException: ORA-02079: 无法联接要提交的分布式事务处理

    at oracle.jdbc.driver.T4CTTIoer11.processError(T4CTTIoer11.java:509) ~[ojdbc8-19.7.0.0.jar:19.7.0.0.0]
    at oracle.jdbc.driver.T4CTTIoer11.processError(T4CTTIoer11.java:456) ~[ojdbc8-19.7.0.0.jar:19.7.0.0.0]
    at oracle.jdbc.driver.T4CTTIoer11.processError(T4CTTIoer11.java:451) ~[ojdbc8-19.7.0.0.jar:19.7.0.0.0]
    at oracle.jdbc.driver.T4CTTIfun.processError(T4CTTIfun.java:1123) ~[ojdbc8-19.7.0.0.jar:19.7.0.0.0]
    at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:553) ~[ojdbc8-19.7.0.0.jar:19.7.0.0.0]
    at oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:269) ~[ojdbc8-19.7.0.0.jar:19.7.0.0.0]
    at oracle.jdbc.driver.T4CTTIOtxse.doOTXSE(T4CTTIOtxse.java:168) ~[ojdbc8-19.7.0.0.jar:19.7.0.0.0]
    at oracle.jdbc.driver.T4CXAResource.doStart(T4CXAResource.java:209) ~[ojdbc8-19.7.0.0.jar:19.7.0.0.0]
    at oracle.jdbc.xa.client.OracleXAResource.start(OracleXAResource.java:262) ~[ojdbc8-19.7.0.0.jar:19.7.0.0.0]
    ... 47 common frames omitted

2020-11-10 08:37:21.484  INFO 20012 --- [h_RMROLE_1_3_32] i.s.c.r.p.c.RmBranchRollbackProcessor    : rm handle branch rollback process:xid=192.168.202.58:8091:69346279655972864,branchId=69346279710498817,branchType=XA,resourceId=jdbc:oracle:thin:@localhost:1521:XE,applicationData=null
2020-11-10 08:37:21.484  INFO 20012 --- [h_RMROLE_1_3_32] io.seata.rm.AbstractRMHandler            : Branch Rollbacking: 192.168.202.58:8091:69346279655972864 69346279710498817 jdbc:oracle:thin:@localhost:1521:XE
2020-11-10 08:37:21.485  INFO 20012 --- [h_RMROLE_1_3_32] i.s.rm.datasource.xa.ResourceManagerXA   : 192.168.202.58:8091:69346279655972864-69346279710498817 rollback failed since XAErr (-4): The XID is not valid. ORA-24756 SQLErr (0)

oracle.jdbc.xa.OracleXAException: XAErr (-4): The XID is not valid. ORA-24756 SQLErr (0)
    at oracle.jdbc.xa.OracleXAResource.checkError(OracleXAResource.java:1114) ~[ojdbc8-19.7.0.0.jar:19.7.0.0.0]
    at oracle.jdbc.xa.client.OracleXAResource.rollback(OracleXAResource.java:1052) ~[ojdbc8-19.7.0.0.jar:19.7.0.0.0]
    at io.seata.rm.datasource.xa.ConnectionProxyXA.xaRollback(ConnectionProxyXA.java:108) ~[seata-all-1.3.0.jar:1.3.0]
    at io.seata.rm.datasource.xa.ResourceManagerXA.finishBranch(ResourceManagerXA.java:72) [seata-all-1.3.0.jar:1.3.0]
    at io.seata.rm.datasource.xa.ResourceManagerXA.branchRollback(ResourceManagerXA.java:58) [seata-all-1.3.0.jar:1.3.0]
    at io.seata.rm.AbstractRMHandler.doBranchRollback(AbstractRMHandler.java:125) [seata-all-1.3.0.jar:1.3.0]
    at io.seata.rm.AbstractRMHandler$2.execute(AbstractRMHandler.java:67) [seata-all-1.3.0.jar:1.3.0]
    at io.seata.rm.AbstractRMHandler$2.execute(AbstractRMHandler.java:63) [seata-all-1.3.0.jar:1.3.0]
    at io.seata.core.exception.AbstractExceptionHandler.exceptionHandleTemplate(AbstractExceptionHandler.java:116) [seata-all-1.3.0.jar:1.3.0]
    at io.seata.rm.AbstractRMHandler.handle(AbstractRMHandler.java:63) [seata-all-1.3.0.jar:1.3.0]
    at io.seata.rm.DefaultRMHandler.handle(DefaultRMHandler.java:63) [seata-all-1.3.0.jar:1.3.0]
    at io.seata.core.protocol.transaction.BranchRollbackRequest.handle(BranchRollbackRequest.java:35) [seata-all-1.3.0.jar:1.3.0]
    at io.seata.rm.AbstractRMHandler.onRequest(AbstractRMHandler.java:150) [seata-all-1.3.0.jar:1.3.0]
    at io.seata.core.rpc.processor.client.RmBranchRollbackProcessor.handleBranchRollback(RmBranchRollbackProcessor.java:63) [seata-all-1.3.0.jar:1.3.0]
    at io.seata.core.rpc.processor.client.RmBranchRollbackProcessor.process(RmBranchRollbackProcessor.java:58) [seata-all-1.3.0.jar:1.3.0]
    at io.seata.core.rpc.netty.AbstractNettyRemoting.lambda$processMessage$2(AbstractNettyRemoting.java:265) [seata-all-1.3.0.jar:1.3.0]
    at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149) ~[na:1.8.0_261]
    at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624) ~[na:1.8.0_261]
    at io.netty.util.concurrent.FastThreadLocalRunnable.run(FastThreadLocalRunnable.java:30) ~[netty-all-4.1.51.Final.jar:4.1.51.Final]
    at java.lang.Thread.run(Thread.java:748) ~[na:1.8.0_261]
Caused by: java.sql.SQLException: ORA-24756: 事务处理不存在

    at oracle.jdbc.driver.T4CTTIoer11.processError(T4CTTIoer11.java:509) ~[ojdbc8-19.7.0.0.jar:19.7.0.0.0]
    at oracle.jdbc.driver.T4CTTIoer11.processError(T4CTTIoer11.java:456) ~[ojdbc8-19.7.0.0.jar:19.7.0.0.0]
    at oracle.jdbc.driver.T4CTTIoer11.processError(T4CTTIoer11.java:451) ~[ojdbc8-19.7.0.0.jar:19.7.0.0.0]
    at oracle.jdbc.driver.T4CTTIfun.processError(T4CTTIfun.java:1123) ~[ojdbc8-19.7.0.0.jar:19.7.0.0.0]
    at oracle.jdbc.driver.T4CTTIfun.receive(T4CTTIfun.java:553) ~[ojdbc8-19.7.0.0.jar:19.7.0.0.0]
    at oracle.jdbc.driver.T4CTTIfun.doRPC(T4CTTIfun.java:269) ~[ojdbc8-19.7.0.0.jar:19.7.0.0.0]
    at oracle.jdbc.driver.T4CTTIOtxen.doOTXEN(T4CTTIOtxen.java:171) ~[ojdbc8-19.7.0.0.jar:19.7.0.0.0]
    at oracle.jdbc.driver.T4CXAResource.doTransaction(T4CXAResource.java:820) ~[ojdbc8-19.7.0.0.jar:19.7.0.0.0]
    at oracle.jdbc.driver.T4CXAResource.kputxrec(T4CXAResource.java:888) ~[ojdbc8-19.7.0.0.jar:19.7.0.0.0]
    at oracle.jdbc.driver.T4CXAResource.doRollback(T4CXAResource.java:711) ~[ojdbc8-19.7.0.0.jar:19.7.0.0.0]
    at oracle.jdbc.xa.client.OracleXAResource.rollback(OracleXAResource.java:1047) ~[ojdbc8-19.7.0.0.jar:19.7.0.0.0]
    ... 18 common frames omitted
funky-eyes commented 3 years ago

麻烦把完整异常堆栈贴上来(不要截图)

robinZhao commented 3 years ago

https://docs.oracle.com/cd/A84870_01/doc/java.816/a81354/xadistr3.htm#1061004

Oracle XA Optimizations Oracle JDBC has functionality to improve performance if two or more branches of a distributed transaction use the same database instance--meaning that the XA resource instances associated with these branches are associated with the same resource manager.

In such a circumstance, the prepare() method of only one of these XA resource instances will return XA_OK (or failure); the rest will return XA_RDONLY, even if updates are made. This allows the transaction manager to implicitly join all the transaction branches and commit (or roll back, if failure) the joined transaction through the XA resource instance that returned XA_OK (or failure).

The transaction manager can use the OracleXAResource class isSameRM() method to determine if two XA resource instances are using the same resource manager. This way it can interpret the meaning of XA_RDONLY return values.

robinZhao commented 3 years ago

https://www.oraexcel.com/database-oracle-11gR2-ORA-02079

Database: 11g Release 2 Error code: ORA-02079 Description: cannot join a committing distributed transaction Cause: Once a transaction branch is prepared, no more new transaction branches are allowed to start, nor is the prepared transaction branch allowed to be joined. Action: Check the application code as this is an XA protocol violation.

robinZhao commented 3 years ago

seata's logic: resource1.start resource1.end resource1.prepare

resource2.start (ORA-02079: cannot join a committing distributed transaction) resource2.end resource2.prepare

resource1.commit resource2.commit

oracle's logic: resource1.start resource1.end resource2.start resource2.end

resource1.prepare(return read_only) resource2.prepare(return ok)

resource1.commit(should skip) resource2.commit

mccxj commented 3 years ago

看描述是不支持的。 https://docs.oracle.com/en/database/oracle/oracle-database/18/adfns/xa.html#GUID-C4F27C42-C388-4604-8986-7917930F7EF8

Oracle XA applications can access other Oracle Database instances through database links with these restrictions:

They must use the shared server configuration.

The transaction processing monitors (TPMs) use shared servers to open the connection to an Oracle Database A. Then the operating system network connection required for the database link is opened by the dispatcher instead of a dedicated server process. This allows different services or threads to operate on the transaction.

If this restriction is not satisfied, then when you use database links within an XA transaction, it creates an operating system network connection between the dedicated server process and the other Oracle Database B. Because this network connection cannot be moved from one dedicated server process to another, you cannot detach from this dedicated server process of database A. Then when you access the database B through a database link, you receive an ORA-24777 error.

The other database being accessed must be another Oracle Database.

funky-eyes commented 3 years ago

请帮忙验证此pr是否修复了该问题

3698

@robinZhao @bocsoft2546

funky-eyes commented 3 years ago

上述pr无法解决此问题,请先忽视,oracle对同一个resources存在隐式链接2个事务的问题,其中一个预先prepare后,导致后续无法join,oracle所谓的高性能优化,虽然保证了目前seata的mysqlxa无法做到的锁重入,但是造成了需要三阶段提交的问题,先end,再决议后prepare,再进行commit/rollback,找个能帮忙测试验证的用户有兴趣的,欢迎联系我