CODEWITHSUNDEEP
oracle-databasejakarta-eexatomee-7
jmb
jmb

Reputation: 139

I get the error "ORA-25351: transaction in use" when I access the Oracle database from a Java application running on a Tomee server

I am writing a web service method which writes into a first schema 'IMMO' and then writes into another schema 'CSF'. The writing into the first schema is running well, but the first try to write into the second schema leads to the following error :

ORA-25351: transaction in use

What I need is a unique distributed transaction starting at the begining of my web service method and ending at the return of this method. My web service is implemented by a stateless EJB which calls other stateless EJBs, all these EJB using default transaction parameters :

Despite all that, it seems that when I try to get a connection to the second datasource, it considers that I am in another session.

The server is tomee 7.0.3

I think I have missed something in tomee configuration, but I can't find anything ...

Here is the datasource definition, coming from tomee.xml :

<Resource id="ImmoDataSource" type="javax.sql.DataSource">
    DataSourceCreator   dbcp
    name            ImmoDataSource
    username        IMMO
    password        ****
    JdbcUrl         jdbc:oracle:thin:@srv-ora5:1521:IBOP
    JdbcDriver      oracle.jdbc.xa.client.OracleXADataSource
    validationQuery     select * from dual
    connectionProperties    useUnicode=false;characterEncoding=WE8MSWIN1252;
    maxTotal        30
    maxIdle         1
    minIdle         0
    maxWaitMillis   60000
    DefaultAutoCommit   false
    testOnBorrow        false
</Resource>

<Resource id="CSFDataSource" type="javax.sql.DataSource">
    DataSourceCreator   dbcp
    name            CSFDataSource
    username        CSF
    password        ***
    JdbcUrl         jdbc:oracle:thin:@srv-ora5:1521:IBOP
    JdbcDriver      oracle.jdbc.xa.client.OracleXADataSource
    validationQuery select * from dual
    connectionProperties    useUnicode=false;characterEncoding=WE8MSWIN1252;
    maxTotal        30
    maxIdle         1
    minIdle         0
    maxWaitMillis   60000
    DefaultAutoCommit   false
    testOnBorrow        false
</Resource>

I expect that the writing in the second schema CSFDataSource is executed in the same transaction as the writing into the first one.

I have writed a small web service, only one little class, that uses the same tomee.xml, and that reproduces the problem :

package fr.csf.testxa;

import java.sql.Connection;
import java.sql.SQLException;
import java.sql.Statement;
import javax.annotation.Resource;
import javax.ejb.Stateless;
import javax.jws.WebMethod;
import javax.jws.WebService;
import javax.sql.DataSource;

@Stateless
@WebService
public class TestXa
{
    @Resource
    private DataSource CSFDataSource;

    @Resource
    private DataSource ImmoDataSource;

    @WebMethod
    public String test() throws SQLException
    {
        String ret = "?";

        // This first insert via ImmoDataSource goes well
        Connection cnx = this.ImmoDataSource.getConnection();
        Statement stm = cnx.createStatement();
        stm.executeUpdate( "insert into testxa( id) values ( sys_guid())");

        // The second access gets the ORA-25351 error
        cnx = this.CSFDataSource.getConnection();
        stm = cnx.createStatement();
        stm.executeUpdate( "insert into testxa( id) values ( sys_guid())");

        // We never arrive here because of the SQLException
        ret = "OK";

        return ret;
    }
}

After some search and many tries, I have found out that I get the ORA-25351 error only when the two datasources reference the same database. But I don't think it is illegal to reference different schema of the same database. That code had been written a long time ago for a Weblogic 8.1 server, and that worked perfectly well.

Upvotes: 0

Views: 673

Answers (1)

jmb
jmb

Reputation: 139

Ater much work, I have found an explanation : The version of my Oracle JDBC driver (ojdbc7.jar) may not be compatible with the version of the Oracle database (Oracle 10gR2).

Here is a link to Oracle website : https://docs.oracle.com/en/database/oracle/oracle-database/19/jjdbc/JDBC-getting-started.html#GUID-926E5324-D89A-4A00-B1AE-975C1089F0EA

where one can read this :

Oracle Database 12c Release 2 (12.2.0.1) JDBC drivers are certified with supported Oracle Database releases (11.x.0.x). However, they are not certified to work with older, unsupported database releases, such as 10.2.x, 10.1.x, 9.2.x, and 9.0.1.x.

So I replaced my ojdbc7.jar driver with an old ojdbc1.4.jar one. But now I get an other error :

Unable to enlist connection in transaction: enlistResource returns 'false'.

But I think that is an other problem, and I will create an other question for it.

Upvotes: 0

Related Questions