Two DS used in one EJB method, DB non-XA - what should I do?

[roxburd]

Hi,

JBoss3.0.4, MySQL 3.23.52

I have a BMP EJB with a method which needs to write to two databases. I have Data Sources configured but, of course, when I access both DS on the one method JBoss (LocalTxConnectionManager$LocalConnectionEventListener) warns "prepare called on a local tx. You are not getting the semantics you expect!"

So, as I understand it, since my DB is not XA-aware, the transactions are not being (cannot be) kept safe.

So what should I do? I want to make the operation ACIDic if possible. And I want to get rid of that WARN output (it's a frequent operation and I don't want the logs jammed up with that output)!

I can't change the DB.

Should I access one of the databases via some other route (not a data source) and code the method to make operations on that database safe outside the transaction??? The way the application works, that would be acceptably safe. Is there a better / recommended alternative?

Many thanks,
Dave

[frito]

You can't be sure that commiting both local transactions is really working. You need a xa transaction for.
You will have some more work to do if you need consistency over both datasources without xa. You could use BMT and commit both datasources by yourself, or you can leave the CMT and just switch switch off the log4j for this warning. It is only a warning telling you things you still know: you are working with two local transactions which are handled one by one.

Greetings,
Frito

[roxburd]

Thanks for the post Frito,

I don't think I can use BMT because the method is in on entity bean - apparently, entity beans must use CMT.

If I switch log4j off, I have to do it in the method and leave it off because the output only gets generated by the container when it commits the transaction. Then, since execution control has left my code, I'll lose all subsequent log4j output since I don't have any opportunity to switch it back on. That is not acceptable since some of the log output is critically important.

And actually, I would like to keep the trans ACIDic if possible.

Thanks for your advice though. It looks like I have two options:
- ditch one of the DS and make SQL calls directly to one of the databases. Ah, that cannot be made ACIDic though can it - if the CMT transaction fails to commit when the method exits and returns control to the container I don't get any opportunity to rollback the non-DS one.
- put a session bean in front of the entity bean... since session beans can use BMT it could wrap the two tansactions in one ? ? ?

Regards,
Dave

[frito]

IMHO, an enity bean should not handle with datasources at all (it is done by its container).

Best practice might be using a session bean working with your entity bean(s).
But without using xa you can't be sure both transactions do the same thing (commit, rollback). Only xa can do.
If you nead consistency over both datasources without xa you have do you own "xa" transaction with some extra code when commiting, which must be BMT of course.

Greetings,
Frito