How to synchronize JDBC transactions with Bean Managed User Transactions...

[henri_tudor]

Hi all...

In the scope of a project of ours, we have developped a custom rule engine application based on a JBoss Application Server in version 5.1. All rule processing is done in a stateful Session Bean with Bean Managed Transactions. While processing rules, the session bean relies on regular JDBC connections to query data from external data sources. Before applying rules, the session bean begins a new transaction, all involved JDBC connections are beginning a new transaction as well. After the rules have been applied, the BMT persists the results of rule evaluation in the rule engines database and all involved transactions are either committed or rolled back. All this worked fine. Now, the end user created a new JDBC data source, pointing to the rule engines database itself. The idea was to define a rule that would check whether a previous rule evaluation generated some specific result. Unfortunately, this doesn't work as expected. Looking at the rule engines log file, I noticed that the results of a previous evaluation are not immediately visible for the next transactions but appear to be updated with a certain latency. The JDBC transactions are in READ_COMMITTED isolation, which explains why the updated results are visible in the same transaction. What bugs me is the fact that, even though the Bean Managed Transaction after commit returns STATUS_NO_TRANSACTION, which indicates that the commit was completed successfully, it looks as if behind the scenes the actual database commit process is either being cached, postponed or somehow delayed. From the point of view of the transaction manager this makes sense, given that fact, that the Transaction Manager assumes that all transactions are managed by himself and decides when to commit which piece of data. My question is, is there a way, to attach external JDBC transactions to the transaction manager so that he is aware of them and may include them in his decision process ? Is there a way to synchronize with the actual db commit process ? Has anyone tried something similar ?

Many thanks in advance for reading all this and your feedback....

[tomjenkinson]

Are you able to make all the connections XA?

It seems strange that your database "commit" has not committed the database, I would expect the updates to be visible at that point, maybe ask your DB vendor?

Either way, mixing none-XA and XA like this is dangerous imo.

[henri_tudor]

Hi Tom...

Thanks for your feedback. Well yes, the fact that the commit isn't visible right away seemed strange to me as well. The database we're talking about is Oracle 10. It is important to know that, instead of creating a new JDBC datasource accessing the rule engine database, then end-user "pulls-in" the rule engine tables of interest via a DB-Link into the external database. I had omitted this detail in order to avoid making a complex description even more complex ;-) So the DB-Link was my first suspect, but I rejected the thought since "latency" issues with DB-Links seemed a bit too far-fetched to me. I decided to follow a different route which I'm going to test next week. I created some kind of internal datasource, which allows the end user to query the rule engine database via the entity manager through native queries. This way, all access to the rule engine data are handled by the transaction manager itself and I reckon this should solve the issue I've been facing. This solution also has the positive side-effect that we're getting rid of the DB-Links which were some kind of crutch.

Thanks again for your feedback....