1

I'm currently debugging an application that uses Modeshape to store values and that is getting some random errors lately. Through extensive logging and the use of P6Spy to check the internal calls, I found something.

For context, Modeshape has an internal data model based on nodes, but at the end they are persisted in a database (In our case, an Oracle DB). Basically one table MP_CONFIG_CFGMGMT_REPOSITORY is used for this and contains an ID field, a modified date, and a BLOB with the contents of the node (encoded as Binary JSON).

At some point, Modeshape needs to edit a node, so updates the corresponding record in MP_CONFIG_CFGMGMT_REPOSITORY (by ID) changing the content field. This update is commited in a transaction.

After that commit, two threads (one of them being the same thread that updated and commited the table) makes a query to the same record. Using P6Spy, I could confirm that, sometimes, one of the queries returns a content field with the value prior to the commited update, whereas the other returns the correct updated value. This causes problems further in the code.

Reading the Modeshape code, I find that it creates connections using the isolation level Connection.TRANSACTION_READ_COMMITTED (and also the queries are confirmed to happen after the commited update) so it cannot be an issue of dirty reads.

The datasource is a JNDI resource defined in JBoss (the application server where my application is deployed) defined with the following configuration:

<datasource jta="false" jndi-name="java:jboss/nameOfDatasource" pool-name="nameOfDatasource" enabled="true" use-java-context="true" statistics-enabled="true">
    <connection-url>jdbc:oracle:thin:@//db-host:6789/DB_NAME</connection-url>
    <driver>oracle</driver>
    <pool>
        <min-pool-size>100</min-pool-size>
        <max-pool-size>100</max-pool-size>
    </pool>
    <security>
        <user-name>${USERNAME}</user-name>
        <password>${PASSWORD}</password>
    </security>
    <validation>
        <valid-connection-checker class-name="org.jboss.jca.adapters.jdbc.extensions.oracle.OracleValidConnectionChecker"/>
        <validate-on-match>false</validate-on-match>
        <background-validation>true</background-validation>
        <background-validation-millis>30000</background-validation-millis>
        <exception-sorter class-name="org.jboss.jca.adapters.jdbc.extensions.oracle.OracleExceptionSorter"/>
    </validation>
    <statement>
        <prepared-statement-cache-size>25</prepared-statement-cache-size>
    </statement>
</datasource>
...
<drivers>
    ...
    <driver name="oracle" module="org.jdbc.oracle">
        <driver-class>oracle.jdbc.driver.OracleDriver</driver-class>
    </driver>
</drivers>

My question is: what are the possible causes for this behavior? Possibly something with Oracle itself? Or maybe an intermediate setup by JBoss between Oracle and the application?

Oracle version: 19c Enterprise Edition Release 19.0.0.0.0

I read Oracle has LOB cache options, but it's advised to avoid it only "if you are rarely accessing the LOBs or if the LOBs are extremely large", that is not the case here

lartkma
  • 589
  • 1
  • 5
  • 15
  • It may not be a dirty read, but one of caching, either on the server or in the client. Check the timestamp for when the "failing" query is executed. If it runs before the commit but the data is fetched after you could have a race condition. – Jim Garrison Jan 06 '22 at 23:33
  • @JimGarrison I've been just checking that data. In my current scenario, the call to the JDBC's `commit` happens at timestamp 1641421234154. The next call to `executeQuery`, that is happening *in the same thread the commit happens* occurs at 1641421234161 (7 ms after), this is the one right now returning the outdated value. There is another `executeQuery` at 1641421234168 (14 ms after the commit) in another thread, that returns the updated value – lartkma Jan 06 '22 at 23:48
  • `READ_COMMITTED` is the second-_weakest_ isolation level, and is probably not enough. All it guarantees is that you will not "see" inside a transaction. It doesn't guarantee you get the _latest_ values. Have you read [On Transaction Isolation Levels](https://blogs.oracle.com/oraclemagazine/post/on-transaction-isolation-levels)? – Jim Garrison Jan 06 '22 at 23:57

0 Answers0