Reputation: 1
I believe transaction is not working on Wildfly 8.2 and oracle 12.2 following is the code that I am trying to run:
public static void checkTransaction() throws Exception {
Statement stmt = null; // Non-transactional statement
Statement stmtx = null;
InitialContext initialContext = new InitialContext();
DataSource ds = (DataSource) initialContext.lookup("jdbc/myDataSource");
Connection conn = ds.getConnection("USERENAME", "PASSWORD");
UserTransaction txn = (UserTransaction) new
InitialContext().lookup("java:jboss/UserTransaction");
try {
stmt = conn.createStatement(); // non-tx statement
try {
stmt.executeUpdate("DROP TABLE test_table");
} catch (Exception e) {
e.printStackTrace();
}
try {
stmt.executeUpdate("CREATE TABLE test_table (a INTEGER,b INTEGER)");
} catch (Exception e) {
throw new RuntimeException(e);
}
try {
System.out.println("Starting top-level tranasction.");
txn.begin();
stmtx = conn.createStatement(); // will be a tx-statement
stmtx.executeUpdate("INSERT INTO test_table (a, b) VALUES (1,2)");
// First, we try to roll back changes
txn.rollback(); // rollback
txn.begin(); // start second tranaction
stmtx = conn.createStatement();
stmtx.executeUpdate("INSERT INTO test_table (a, b) VALUES (3,4)");
txn.commit(); // committing the second transaction
} catch (Exception ex) {
throw new RuntimeException(ex);
}
} catch (Exception sysEx) {
sysEx.printStackTrace();
}
}
The datasource is configured as following:
<datasources>
<datasource enabled="true" jndi-name="java:/jdbc/myDataSource" pool-name="DataSourcePool" use-ccm="true">
<connection-url>jdbc:oracle:thin:@(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=host)(PORT=1521))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=serviceId)))</connection-url>
<driver>oracle</driver>
<pool>
<min-pool-size>4</min-pool-size>
<max-pool-size>18</max-pool-size>
</pool>
<security>
<security-domain>EncryptedDBPassword</security-domain>
</security>
<validation>
<valid-connection-checker class-name="org.jboss.jca.adapters.jdbc.extensions.oracle.OracleValidConnectionChecker"/>
<check-valid-connection-sql>select 1 from dual</check-valid-connection-sql>
<validate-on-match>true</validate-on-match>
<background-validation>true</background-validation>
<background-validation-millis>30000</background-validation-millis>
<stale-connection-checker class-name="org.jboss.jca.adapters.jdbc.extensions.oracle.OracleStaleConnectionChecker"/>
<exception-sorter class-name="org.jboss.jca.adapters.jdbc.extensions.oracle.OracleExceptionSorter"/>
</validation>
<timeout>
<set-tx-query-timeout>true</set-tx-query-timeout>
<blocking-timeout-millis>10000</blocking-timeout-millis>
<idle-timeout-minutes>1</idle-timeout-minutes>
<query-timeout>120</query-timeout>
<use-try-lock>0</use-try-lock>
<allocation-retry>0</allocation-retry>
<allocation-retry-wait-millis>0</allocation-retry-wait-millis>
</timeout>
<statement>
<share-prepared-statements>false</share-prepared-statements>
</statement>
</datasource>
<drivers>
<driver module="com.h2database.h2" name="h2">
<xa-datasource-class>org.h2.jdbcx.JdbcDataSource</xa-datasource-class>
</driver>
<driver module="com.oracle.ojdbc" name="oracle">
<driver-class>oracle.jdbc.OracleDriver</driver-class>
</driver>
</drivers>
What is happenning:
It is expected from the code that the test_table will be having only one entry i.e (3,4) because the previous transaction in which (1,2) is inserted is rolled back but in reality the table contains 2 rows and both the sqls are committed. Any reason why this transaction is not working ? This is the link that I followed for this code:
I am also assuming that the default value of jta is true in the data-source and that need not be added explicitly.
Upvotes: 0
Views: 758
Reputation: 21
I was able to figure out the solution for this problem i.e. for Asynchronous Threads. Solution is to fetch the connection from DataSource after UserTransaction.begin() is done. This is not inline with the Wildfly documentation where the example is shows that connection can be fetched before Transaction.begin() but it doesn't work for Asynchronous Threads scenario (works in Wildfly managed beans scenario flawlessly though).
Link to Wildfly transaction example : https://access.redhat.com/documentation/en-us/jboss_enterprise_application_platform/6/html/development_guide/jta_transaction_example
Upvotes: 1
Reputation: 21
I have a similar issue . However, the above when I hit the via Servlet . The issue comes when I am trying to use transaction in non EE threads . It doesn't throw any exception but DB transaction is still committed even though I have used Usertransaction.rollback(). Per Wildfly 8 documentation using java:jboss/UserTransaction for getting user transaction which should be present and code does get the Usertransaction Object but doesn't actually control the DB changes as shown above. My sense is that my non EE threads are somehow not part of Wildfly managed transaction but can't any documentation around it. Any inputs here would be great.
Upvotes: 0