用户事务在使用Oracle数据库的WildFly中无法正常工作

huangapple go评论82阅读模式
英文:

Usertransaction is not working in wildfly with Oracle DB

问题

以下是翻译好的内容:

我认为在 Wildfly 8.2 和 Oracle 12.2 上事务不起作用,以下是我正在尝试运行的代码:

public static void checkTransaction() throws Exception {
    Statement stmt = null; // 非事务性语句
    Statement stmtx = null;

    InitialContext initialContext = new InitialContext();

    DataSource ds = (DataSource) initialContext.lookup("jdbc/myDataSource");

    Connection conn = ds.getConnection("USERNAME", "PASSWORD");

    UserTransaction txn = (UserTransaction) new 
        InitialContext().lookup("java:jboss/UserTransaction");

    try {
        stmt = conn.createStatement(); // 非事务性语句

        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 transaction.");

            txn.begin();

            stmtx = conn.createStatement(); // 将是事务性语句

            stmtx.executeUpdate("INSERT INTO test_table (a, b) VALUES (1,2)");

            // 首先,我们尝试回滚更改
            txn.rollback(); // 回滚事务

            txn.begin(); // 开始第二个事务

            stmtx = conn.createStatement();

            stmtx.executeUpdate("INSERT INTO test_table (a, b) VALUES (3,4)");

            txn.commit(); // 提交第二个事务
        } catch (Exception ex) {
            throw new RuntimeException(ex);
        }
    } catch (Exception sysEx) {
        sysEx.printStackTrace();
    }
}

数据源的配置如下:

<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>
</datasources>

发生的情况:

根据代码的预期,test_table 应该只有一个条目,即 (3,4),因为之前插入 (1,2) 的事务已被回滚,但实际上表中有 2 行,两个 SQL 语句都被提交。有没有什么原因导致这个事务不起作用?
这是我为此代码所遵循的链接:

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

我还假设数据源的 jta 默认值为 true,不需要显式添加。

英文:

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(&quot;jdbc/myDataSource&quot;);
Connection conn = ds.getConnection(&quot;USERENAME&quot;, &quot;PASSWORD&quot;);
UserTransaction txn = (UserTransaction) new 
InitialContext().lookup(&quot;java:jboss/UserTransaction&quot;);
try {
stmt = conn.createStatement(); // non-tx statement
try {
stmt.executeUpdate(&quot;DROP TABLE test_table&quot;);
} catch (Exception e) {
e.printStackTrace();
}
try {
stmt.executeUpdate(&quot;CREATE TABLE test_table (a INTEGER,b INTEGER)&quot;);
} catch (Exception e) {
throw new RuntimeException(e);
}
try {
System.out.println(&quot;Starting top-level tranasction.&quot;);
txn.begin();
stmtx = conn.createStatement(); // will be a tx-statement
stmtx.executeUpdate(&quot;INSERT INTO test_table (a, b) VALUES (1,2)&quot;);
// First, we try to roll back changes
txn.rollback(); // rollback 
txn.begin(); // start second tranaction
stmtx = conn.createStatement();
stmtx.executeUpdate(&quot;INSERT INTO test_table (a, b) VALUES (3,4)&quot;);
txn.commit(); // committing the second transaction
} catch (Exception ex) {
throw new RuntimeException(ex);
}
} catch (Exception sysEx) {
sysEx.printStackTrace();
}
}

The datasource is configured as following:

 &lt;datasources&gt;
&lt;datasource enabled=&quot;true&quot; jndi-name=&quot;java:/jdbc/myDataSource&quot; pool-name=&quot;DataSourcePool&quot; use-ccm=&quot;true&quot;&gt;
&lt;connection-url&gt;jdbc:oracle:thin:@(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=host)(PORT=1521))(CONNECT_DATA=(SERVER=DEDICATED)(SERVICE_NAME=serviceId)))&lt;/connection-url&gt;
&lt;driver&gt;oracle&lt;/driver&gt;
&lt;pool&gt;
&lt;min-pool-size&gt;4&lt;/min-pool-size&gt;
&lt;max-pool-size&gt;18&lt;/max-pool-size&gt;
&lt;/pool&gt;
&lt;security&gt;
&lt;security-domain&gt;EncryptedDBPassword&lt;/security-domain&gt;
&lt;/security&gt;
&lt;validation&gt;
&lt;valid-connection-checker class-name=&quot;org.jboss.jca.adapters.jdbc.extensions.oracle.OracleValidConnectionChecker&quot;/&gt;
&lt;check-valid-connection-sql&gt;select 1 from dual&lt;/check-valid-connection-sql&gt;
&lt;validate-on-match&gt;true&lt;/validate-on-match&gt;
&lt;background-validation&gt;true&lt;/background-validation&gt;
&lt;background-validation-millis&gt;30000&lt;/background-validation-millis&gt;
&lt;stale-connection-checker class-name=&quot;org.jboss.jca.adapters.jdbc.extensions.oracle.OracleStaleConnectionChecker&quot;/&gt;
&lt;exception-sorter class-name=&quot;org.jboss.jca.adapters.jdbc.extensions.oracle.OracleExceptionSorter&quot;/&gt;
&lt;/validation&gt;
&lt;timeout&gt;
&lt;set-tx-query-timeout&gt;true&lt;/set-tx-query-timeout&gt;
&lt;blocking-timeout-millis&gt;10000&lt;/blocking-timeout-millis&gt;
&lt;idle-timeout-minutes&gt;1&lt;/idle-timeout-minutes&gt;
&lt;query-timeout&gt;120&lt;/query-timeout&gt;
&lt;use-try-lock&gt;0&lt;/use-try-lock&gt;
&lt;allocation-retry&gt;0&lt;/allocation-retry&gt;
&lt;allocation-retry-wait-millis&gt;0&lt;/allocation-retry-wait-millis&gt;
&lt;/timeout&gt;
&lt;statement&gt;
&lt;share-prepared-statements&gt;false&lt;/share-prepared-statements&gt;
&lt;/statement&gt;
&lt;/datasource&gt;
&lt;drivers&gt;
&lt;driver module=&quot;com.h2database.h2&quot; name=&quot;h2&quot;&gt;
&lt;xa-datasource-class&gt;org.h2.jdbcx.JdbcDataSource&lt;/xa-datasource-class&gt;
&lt;/driver&gt;
&lt;driver module=&quot;com.oracle.ojdbc&quot; name=&quot;oracle&quot;&gt;
&lt;driver-class&gt;oracle.jdbc.OracleDriver&lt;/driver-class&gt;
&lt;/driver&gt;
&lt;/drivers&gt;

</datasources>

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.

答案1

得分: 1

我已经能够找出解决这个问题的方法,即针对异步线程的情况。
解决方案是在UserTransaction.begin()完成后从DataSource获取连接
这与Wildfly文档中的情况不符,在示例中显示可以在Transaction.begin()之前获取连接,但在异步线程的情况下(在Wildfly管理的Bean场景中可以正常工作),这种方法不起作用。

Wildfly事务示例链接:https://access.redhat.com/documentation/en-us/jboss_enterprise_application_platform/6/html/development_guide/jta_transaction_example

英文:

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

答案2

得分: 0

我有一个类似的问题。然而,当我通过Servlet进行访问时,与上述情况类似。问题出现在我尝试在非EE线程中使用事务时。它不会抛出任何异常,但是即使我已经使用了UserTransaction.rollback(),数据库事务仍然被提交。

根据Wildfly 8文档,使用java:jboss/UserTransaction来获取用户事务,该事务应该是存在的,代码确实获得了UserTransaction对象,但实际上无法控制如上所示的数据库更改。
我的理解是,我的非EE线程在某种程度上并不属于Wildfly管理的事务,但在文档中找不到任何相关信息。
这里的任何输入都将是很好的。

英文:

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.

huangapple
  • 本文由 发表于 2020年8月27日 19:47:33
  • 转载请务必保留本文链接:https://go.coder-hub.com/63615307.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定