I think I might have a big problem. Let me explain.
We have two databases, one local (MySQL) and one remote (OpenEdge Progress) accessed via teiid.
We use XA datasources for MySQL and Teiid.
So far teiid was using a local-tx-datasource to the Progress database. That was okay for reading entities but when I tried to insert an entity I got an exception saying that the transaction wasn't supported.
I found this discussion :
which suggests that you have to use XA datasource when you use hibernate even if you only have one datasource.
Okay I created a XA datasource connector for the Progress Database but when I tried to insert an entity I got this exception:
I assume this tells me that the ProgressDB is not configured to use global transactions.
Unfortunately it is very unlikely that they will configure the ProgressDB to support JTA transactions. So I will have to use local-tx-datasources.
I don't really need transaction spanning multiple datasources (the local db is only for static data) but the comment from Ramesh
got me scared:
Teiid would also require all participating connectors to be XA connectors, if you are updating multiple sources in a single transaction. If you are not using Hibernate on top, and this is the only source that is doing an "insert" in Teiid, then it would have used local transaction fine with current driver defined for your source.
We are using Hibernate, does that mean we have to use XA datasources with Teiid?
There was another quote:
If your Hibernate layer is using local transactions by setting "autocommit = false" on the JDBC driver, you can use TeiidDriver or TeiidDatasource. You can just create a regular JDBC connection on Teiid. However, in this scenario Hibernate layer *must* issue a commit.
If your Hibernate layer is using request level transactions (autocommit=true), then the burden is on Teiid to wrap the transaction around the multiple data sources involved inside Teiid. here we provide more options to the users as to how they would like to handle this. Please see explanation on "txnautoWrap" here. You can see how to set this property here. You can supply this on the connection URL.
Does that mean I can use local-tx-datasource with hibernate as long as I set TxnAutoWrap=PESSIMISTIC ?
So what are my options?
I guess I can't use a XA datasource to teiid and a local-tx-datasource to the actual db? This would be ideal as I then don't have problems using two datasources (otherwise I get concurrent transactions errors).