CODEWITHSUNDEEP
javaoracle-databasejbosswildflyjta
lalit
lalit

Reputation: 1

Usertransaction is not working in wildfly with Oracle DB

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:

https://access.redhat.com/documentation/en-us/red_hat_jboss_enterprise_application_platform/7.1/html/development_guide/java_transaction_api_jta#jta_transaction_example

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

Answers (2)

user7041287
user7041287

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

user7041287
user7041287

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

Related Questions