accessing wrong Oracle schema
Architecture:
-1 linux server, with 2 jboss instances, named JB1 and JB2 (two linux processes created by two different linux users).
-1 Oracle RAC database, implemented on 2 linux servers (i.e. 2 nodes)
-2 different schemas on Oracle, USR1 and USR2, with different passwords, same tables but different data inside.
JB1 is configured to access data on schema USR1, JB2 to access schema USR2.
Problem: in the log file of JB1, sometimes I get an error like "USR2.FK1" violated , where FK1 is a foreign key. In other words, it seems that sometimes JB1 try to write data on USR2.
Besides, it happens that in the same db transaction the first query is executed on schema USR1 while the second query on USR2.
The foreign key violation is due to different data in parent tables.
Please keep into consideration that JB1 does not know anything about user/passowrd of schema USR2 (and viceversa).
Technical data:
I'm using jboss 4.0.3sp1 on linux red-hat 4, connecting to database Oracle 10.0.2 using thin with ojdb14.jar library. Oracle is installed as RAC on two linux servers. The string database connection is something like: connection.url=jdbc:oracle:thin:@(DESCRIPTION=
(LOAD_BALANCE=on)(ADDRESS=(PROTOCOL=TCP)(HOST=miprodb01-vip)
(PORT=1521))(ADDRESS=(PROTOCOL=TCP)(HOST=miprodb02-vip)
(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=USR1_PROA)))
"USR1_PROA" is an oracle (rac) service configured to established a connection on the first oracle server miprodb01 (preferred) or (if unavaible) on the second oracle server miprodb02.
A connection pool is configured with system properties file loaded on Jboss startup:
<local-tx-datasource>
<jndi-name>MyDataSource</jndi-name>
<use-java-context>false</use-java-context>
<connection-url>...see above...</connection-url>
<driver-class>oracle.jdbc.driver.OracleDriver</driver-class>
<user-name>USR1</user-name>
USR1PWD
<valid-connection-checker-class-name>org.jboss.resource.adapter.jdbc.vendor.OracleValidConnectionChecker</valid-connection-checker-class-name>
<!-- Checks the Oracle error codes and messages for fatal errors -->
<exception-sorter-class-name>org.jboss.resource.adapter.jdbc.vendor.OracleExceptionSorter</exception-sorter-class-name>
<transaction-isolation>TRANSACTION_READ_COMMITTED</transaction-isolation>
<!--pooling parameters-->
<min-pool-size>5</min-pool-size>
<max-pool-size>10</max-pool-size>
<blocking-timeout-millis>5000</blocking-timeout-millis>
<idle-timeout-minutes>15</idle-timeout-minutes>
</local-tx-datasource>
The query involved are different, an example is:
INSERT INTO MYTABLE (F1, F2) values (1234, 4567).
I use prepared statement.
Does anyone face the same problem?
Thanks