Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

Sign up for GitHub

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Jump to bottom

Dev Services for Microsoft SQL server does not enable XA transactions when transaction type is XA #35336

Open
michalvavrik opened this issue Aug 13, 2023 · 5 comments
Open

Dev Services for Microsoft SQL server does not enable XA transactions when transaction type is XA #35336

michalvavrik opened this issue Aug 13, 2023 · 5 comments
Labels
area/narayana Transactions / Narayana kind/enhancement New feature or request

Comments

@michalvavrik
Copy link
Member

michalvavrik commented Aug 13, 2023 •
edited
Loading

Describe the bug

I have application with Narayana JTA extension, using XA transactions and when 2 JDBC XA resources are involved, transaction always fail in DEV mode, for XA transactions are not enabled in SQL server.

Expected behavior

It would be very nice if dev svcs could enable it for me, it can be done like this container.execInContainer( "/opt/mssql-tools/bin/sqlcmd", "-S", "localhost", "-U", getUsername(), "-P", getPassword(), "-Q", "EXEC sp_sqljdbc_xa_install");

Actual behavior

Transaction fails and exception is logged out

2023-08-13 21:47:59,230 WARN  [com.arj.ats.jta] (Periodic Recovery) ARJUNA016027: Local XARecoveryModule.xaRecovery got XA exception XAException.XAER_RMERR: javax.transaction.xa.XAException: com.microsoft.sqlserver.jdbc.SQLServerException: Failed to create the XA control connection. Error: "Could not find stored procedure 'master..xp_sqljdbc_xa_init_ex'."
	at com.microsoft.sqlserver.jdbc.SQLServerXAResource.DTC_XA_Interface(SQLServerXAResource.java:757)
	at com.microsoft.sqlserver.jdbc.SQLServerXAResource.recover(SQLServerXAResource.java:841)
	at io.agroal.narayana.RecoveryXAResource.recover(RecoveryXAResource.java:31)
	at com.arjuna.ats.internal.jta.recovery.arjunacore.XARecoveryModule.xaRecoveryFirstPass(XARecoveryModule.java:735)
	at com.arjuna.ats.internal.jta.recovery.arjunacore.XARecoveryModule.periodicWorkFirstPass(XARecoveryModule.java:249)
	at com.arjuna.ats.internal.jta.recovery.arjunacore.XARecoveryModule.periodicWorkFirstPass(XARecoveryModule.java:191)
	at com.arjuna.ats.internal.arjuna.recovery.PeriodicRecovery.doWorkInternal(PeriodicRecovery.java:770)
	at com.arjuna.ats.internal.arjuna.recovery.PeriodicRecovery.run(PeriodicRecovery.java:382)

2023-08-13 21:47:59,237 WARN  [com.arj.ats.jta] (Periodic Recovery) ARJUNA016027: Local XARecoveryModule.xaRecovery got XA exception XAException.XAER_RMERR: javax.transaction.xa.XAException: com.microsoft.sqlserver.jdbc.SQLServerException: Failed to create the XA control connection. Error: "Could not find stored procedure 'master..xp_sqljdbc_xa_init_ex'."
	at com.microsoft.sqlserver.jdbc.SQLServerXAResource.DTC_XA_Interface(SQLServerXAResource.java:757)
	at com.microsoft.sqlserver.jdbc.SQLServerXAResource.recover(SQLServerXAResource.java:841)
	at io.agroal.narayana.RecoveryXAResource.recover(RecoveryXAResource.java:31)
	at com.arjuna.ats.internal.jta.recovery.arjunacore.XARecoveryModule.xaRecoveryFirstPass(XARecoveryModule.java:735)
	at com.arjuna.ats.internal.jta.recovery.arjunacore.XARecoveryModule.periodicWorkFirstPass(XARecoveryModule.java:249)
	at com.arjuna.ats.internal.jta.recovery.arjunacore.XARecoveryModule.periodicWorkFirstPass(XARecoveryModule.java:191)
	at com.arjuna.ats.internal.arjuna.recovery.PeriodicRecovery.doWorkInternal(PeriodicRecovery.java:770)
	at com.arjuna.ats.internal.arjuna.recovery.PeriodicRecovery.run(PeriodicRecovery.java:382)

2023-08-13 21:48:01,332 WARN  [com.arj.ats.jta] (executor-thread-1) ARJUNA016061: TransactionImple.enlistResource - XAResource.start returned: XAException.XAER_RMERR for < formatId=131077, gtrid_length=35, bqual_length=36, tx_uid=0:ffff0a0000b9:a38f:64d9336e:7, node_name=rhbq-qe, branch_uid=0:ffff0a0000b9:a38f:64d9336e:45, subordinatenodename=null, eis_name=0 >: javax.transaction.xa.XAException: com.microsoft.sqlserver.jdbc.SQLServerException: Failed to create the XA control connection. Error: "The connection is closed."
	at com.microsoft.sqlserver.jdbc.SQLServerXAResource.DTC_XA_Interface(SQLServerXAResource.java:757)
	at com.microsoft.sqlserver.jdbc.SQLServerXAResource.start(SQLServerXAResource.java:791)
	at io.agroal.narayana.BaseXAResource.start(BaseXAResource.java:150)
	at com.arjuna.ats.internal.jta.transaction.arjunacore.TransactionImple.enlistResource(TransactionImple.java:661)
	at com.arjuna.ats.internal.jta.transaction.arjunacore.TransactionImple.enlistResource(TransactionImple.java:422)
	at io.agroal.narayana.NarayanaTransactionIntegration.associate(NarayanaTransactionIntegration.java:93)
	at io.agroal.pool.ConnectionPool.getConnection(ConnectionPool.java:252)
	at io.agroal.pool.DataSource.getConnection(DataSource.java:86)
	at org.acme.GreetingResource.createTable(GreetingResource.java:39)
	at org.acme.GreetingResource.hello(GreetingResource.java:32)
	at org.acme.GreetingResource$quarkusrestinvoker$hello_e747664148511e1e5212d3e0f4b40d45c56ab8a1.invoke(Unknown Source)
	at org.jboss.resteasy.reactive.server.handlers.InvocationHandler.handle(InvocationHandler.java:29)
	at io.quarkus.resteasy.reactive.server.runtime.QuarkusResteasyReactiveRequestContext.invokeHandler(QuarkusResteasyReactiveRequestContext.java:141)
	at org.jboss.resteasy.reactive.common.core.AbstractResteasyReactiveContext.run(AbstractResteasyReactiveContext.java:145)
	at io.quarkus.vertx.core.runtime.VertxCoreRecorder$14.runWith(VertxCoreRecorder.java:576)
	at org.jboss.threads.EnhancedQueueExecutor$Task.run(EnhancedQueueExecutor.java:2513)
	at org.jboss.threads.EnhancedQueueExecutor$ThreadBody.run(EnhancedQueueExecutor.java:1538)
	at org.jboss.threads.DelegatingRunnable.run(DelegatingRunnable.java:29)
	at org.jboss.threads.ThreadLocalResettingRunnable.run(ThreadLocalResettingRunnable.java:29)
	at io.netty.util.concurrent.FastThreadLocalRunnable.run(FastThreadLocalRunnable.java:30)
	at java.base/java.lang.Thread.run(Thread.java:833)

2023-08-13 21:48:01,335 WARN  [com.arj.ats.jta] (executor-thread-1) ARJUNA016138: Failed to enlist XA resource io.agroal.narayana.BaseXAResource@1620ee94: jakarta.transaction.SystemException: TransactionImple.enlistResource - XAResource.start ARJUNA016054: could not register transaction: < formatId=131077, gtrid_length=35, bqual_length=36, tx_uid=0:ffff0a0000b9:a38f:64d9336e:7, node_name=rhbq-qe, branch_uid=0:ffff0a0000b9:a38f:64d9336e:45, subordinatenodename=null, eis_name=0 >
	at com.arjuna.ats.internal.jta.transaction.arjunacore.TransactionImple.enlistResource(TransactionImple.java:714)
	at com.arjuna.ats.internal.jta.transaction.arjunacore.TransactionImple.enlistResource(TransactionImple.java:422)
	at io.agroal.narayana.NarayanaTransactionIntegration.associate(NarayanaTransactionIntegration.java:93)
	at io.agroal.pool.ConnectionPool.getConnection(ConnectionPool.java:252)
	at io.agroal.pool.DataSource.getConnection(DataSource.java:86)
	at org.acme.GreetingResource.createTable(GreetingResource.java:39)
	at org.acme.GreetingResource.hello(GreetingResource.java:32)
	at org.acme.GreetingResource$quarkusrestinvoker$hello_e747664148511e1e5212d3e0f4b40d45c56ab8a1.invoke(Unknown Source)
	at org.jboss.resteasy.reactive.server.handlers.InvocationHandler.handle(InvocationHandler.java:29)
	at io.quarkus.resteasy.reactive.server.runtime.QuarkusResteasyReactiveRequestContext.invokeHandler(QuarkusResteasyReactiveRequestContext.java:141)
	at org.jboss.resteasy.reactive.common.core.AbstractResteasyReactiveContext.run(AbstractResteasyReactiveContext.java:145)
	at io.quarkus.vertx.core.runtime.VertxCoreRecorder$14.runWith(VertxCoreRecorder.java:576)
	at org.jboss.threads.EnhancedQueueExecutor$Task.run(EnhancedQueueExecutor.java:2513)
	at org.jboss.threads.EnhancedQueueExecutor$ThreadBody.run(EnhancedQueueExecutor.java:1538)
	at org.jboss.threads.DelegatingRunnable.run(DelegatingRunnable.java:29)
	at org.jboss.threads.ThreadLocalResettingRunnable.run(ThreadLocalResettingRunnable.java:29)
	at io.netty.util.concurrent.FastThreadLocalRunnable.run(FastThreadLocalRunnable.java:30)
	at java.base/java.lang.Thread.run(Thread.java:833)

2023-08-13 21:48:01,412 ERROR [io.qua.ver.htt.run.QuarkusErrorHandler] (executor-thread-1) HTTP Request to /hello failed, error id: 4166535d-5154-4425-8e99-d52d8c1244de-1: java.lang.RuntimeException: java.sql.SQLException: Exception in association of connection to existing transaction
	at org.acme.GreetingResource.createTable(GreetingResource.java:44)
	at org.acme.GreetingResource.hello(GreetingResource.java:33)
	at org.acme.GreetingResource$quarkusrestinvoker$hello_e747664148511e1e5212d3e0f4b40d45c56ab8a1.invoke(Unknown Source)
	at org.jboss.resteasy.reactive.server.handlers.InvocationHandler.handle(InvocationHandler.java:29)
	at io.quarkus.resteasy.reactive.server.runtime.QuarkusResteasyReactiveRequestContext.invokeHandler(QuarkusResteasyReactiveRequestContext.java:141)
	at org.jboss.resteasy.reactive.common.core.AbstractResteasyReactiveContext.run(AbstractResteasyReactiveContext.java:145)
	at io.quarkus.vertx.core.runtime.VertxCoreRecorder$14.runWith(VertxCoreRecorder.java:576)
	at org.jboss.threads.EnhancedQueueExecutor$Task.run(EnhancedQueueExecutor.java:2513)
	at org.jboss.threads.EnhancedQueueExecutor$ThreadBody.run(EnhancedQueueExecutor.java:1538)
	at org.jboss.threads.DelegatingRunnable.run(DelegatingRunnable.java:29)
	at org.jboss.threads.ThreadLocalResettingRunnable.run(ThreadLocalResettingRunnable.java:29)
	at io.netty.util.concurrent.FastThreadLocalRunnable.run(FastThreadLocalRunnable.java:30)
	at java.base/java.lang.Thread.run(Thread.java:833)
Caused by: java.sql.SQLException: Exception in association of connection to existing transaction
	at io.agroal.narayana.NarayanaTransactionIntegration.associate(NarayanaTransactionIntegration.java:100)
	at io.agroal.pool.ConnectionPool.getConnection(ConnectionPool.java:252)
	at io.agroal.pool.DataSource.getConnection(DataSource.java:86)
	at org.acme.GreetingResource.createTable(GreetingResource.java:39)
	... 12 more
Caused by: com.arjuna.ats.jta.exceptions.RollbackException: ARJUNA016081: The transaction implementation threw a RollbackException
	at com.arjuna.ats.internal.jta.transaction.arjunacore.TransactionSynchronizationRegistryImple.registerInterposedSynchronization(TransactionSynchronizationRegistryImple.java:144)
	at jakarta.transaction.NarayanaJtaProducers_ProducerMethod_transactionSynchronizationRegistry_6c1ae1f6c0015764ef2a5d1837652baa9054bdb5_ClientProxy.registerInterposedSynchronization(Unknown Source)
	at io.agroal.narayana.NarayanaTransactionIntegration.associate(NarayanaTransactionIntegration.java:82)
	... 15 more
Caused by: jakarta.transaction.RollbackException: ARJUNA016083: Cannot register synchronization because the transaction is in aborted state
	at com.arjuna.ats.internal.jta.transaction.arjunacore.TransactionImple.registerSynchronizationImple(TransactionImple.java:395)
	at com.arjuna.ats.internal.jta.transaction.arjunacore.TransactionSynchronizationRegistryImple.registerInterposedSynchronization(TransactionSynchronizationRegistryImple.java:140)
	... 17 more

How to Reproduce?

Reproducer:

  • git clone [email protected]:michalvavrik/quarkus-narayana-jta-mssql-reproducer.git
  • cd quarkus-narayana-jta-mssql-reproducer
  • mvn clean test

Output of uname -a or ver

Linux fedora 6.4.7-200.fc38.x86_64 #1 SMP PREEMPT_DYNAMIC Thu Jul 27 20:01:18 UTC 2023 x86_64 GNU/Linux

Output of java -version

OpenJDK Runtime Environment Temurin-17.0.7+7 (build 17.0.7+7)

GraalVM version (if different from Java)

22.3

Quarkus version or git rev

3.2.4.Final and 999-SNAPSHOT

Build tool (ie. output of mvnw --version or gradlew --version)

Apache Maven 3.9.3

Additional information

No response
@michalvavrik michalvavrik added kind/bug Something isn't working area/narayana Transactions / Narayana labels Aug 13, 2023
@michalvavrik
Copy link
Member Author

/cc @mmusgrov @zhfeng

@zhfeng
Copy link
Contributor

zhfeng commented Aug 14, 2023

Thanks @michalvavrik for reporting!

And I think it needs to do something on the MSSQL server side to enable the xa transaction.

Exec the following on the server

1> EXEC sp_sqljdbc_xa_install
2> GO
1>

More information https://learn.microsoft.com/en-us/sql/connect/jdbc/understanding-xa-transactions

@michalvavrik
Copy link
Member Author

I'll try it later today, thanks.

@michalvavrik
Copy link
Member Author

note: I didn't get to it this week, will try it next week.

@michalvavrik michalvavrik mentioned this issue Sep 15, 2023
Merged
10 tasks
@michalvavrik
Copy link
Member Author

hey @zhfeng , you were right, I have it working. I'd like to keep this issue open for I think our Dev Services should detect that XA transactions are used and prepare it. That's at least my opinion.

Anyway, I experienced something that I didn't with other databases, you can't query the table that were part of this crashed transaction until the transaction is recovered. Is that expected?

@michalvavrik michalvavrik changed the title Narayana XA transactions doesn't work with MsSQL as it failes to create the XA control connection Dev Services for Microsoft SQL server does not enable XA transactions when transaction type is XA Sep 15, 2023
@michalvavrik michalvavrik added kind/enhancement New feature or request and removed kind/bug Something isn't working labels Sep 15, 2023
@mmusgrov mmusgrov mentioned this issue Nov 13, 2023
Closed
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Assignees
No one assigned
Labels
area/narayana Transactions / Narayana kind/enhancement New feature or request
Projects
None yet
Milestone
No milestone
Development

No branches or pull requests
2 participants
@zhfeng @michalvavrik