1 2 Previous Next 15 Replies Latest reply on Jan 2, 2019 6:05 PM by Manuel Fehlhammer

    Multiple DataSources in one transaction

    Juan Quiros Newbie

      Hi all,

       

      I have been working a couple of weeks on a requirement that requires modifying some methods that are using transactions, the code between the transaction can read/update/delete/etc elements in the database(PostgreSQL) through the application. At this moment, the application is using a persistence unit (pointing to a wildfly datasource) and an entity manager to access the database.

       

       

      @PersistenceContext( unitName="name", type = PersistenceContextType.TRANSACTION )

      protected EntityManager em;

       

      public String updateReadRead()

      {

         try

         {

         TransactionUtil.begin( this.utx );

       

         Query nativeQuery = this.em.createNativeQuery("update schema.company_info set service_manager_phone_ext = '1004';");

         int i = nativeQuery.executeUpdate();

       

         this.searchCriteriaApplication.trimVo();

         this.searchCriteriaCustomer.trimVo();

         this.searchResults = this.applicationSearchService.searchApplications( this.searchCriteriaApplication, this.searchCriteriaCustomer, this.em, false, true );

       

         this.showAccountsForCustomer = false;

       

         this.applicationSearchService.searchApplications( this.searchCriteriaApplication, this.searchCriteriaCustomer, this.em, false, true );

         TransactionUtil.commit(this.utx);

         }

         catch ( Exception e )

         {

       

         }

         return null;

      }

       

       

      But now, we want to use two entity managers ,one in charge of the write operations (pointing to a read-write Data Base) and the other one for read operations (pointing to a replica Data Base (ReadOnly DataBase)).

       

       

      @PersistenceContext( unitName="readonly", type = PersistenceContextType.TRANSACTION )

      protected EntityManager readOnlyEM;

       

      @PersistenceContext( unitName="readwrite", type = PersistenceContextType.TRANSACTION )

      protected EntityManager readWriteEM;

       

       

      public String updateReadRead()

      {

         try

         {

         TransactionUtil.begin( this.utx );

       

         Query nativeQuery = this.readWriteEM.createNativeQuery("update schema.company_info set service_manager_phone_ext = '1004';");

         int i = nativeQuery.executeUpdate();

       

         this.searchCriteriaApplication.trimVo();

         this.searchCriteriaCustomer.trimVo();

         this.searchResults = this.applicationSearchService.searchApplications( this.searchCriteriaApplication, this.searchCriteriaCustomer, this.readOnlyEM, false, true );

       

         this.showAccountsForCustomer = false;

       

         this.applicationSearchService.searchApplications( this.searchCriteriaApplication, this.searchCriteriaCustomer, this.readOnlyEM, false, true );

       

         TransactionUtil.commit(this.utx);

         }

         catch ( Exception e )

         {

       

         }

         return null;

      }

       

       

      I have tried two approaches:

       

        1.  Create a new entityManager pointing to the readonly database. It fails when we use the readonly entityManager and then, change to the write entityManager or vice versa (All of this in the same transaction).

       

      If we use two transactions, one for read operations and the other one for write operations, it works or if we define the <property name="com.arjuna.ats.arjuna.allowMultipleLastResources" value="true"/>, it works too. But an ugly warning message is displayed (ARJUNA012141: Multiple last resources have been added to the current transaction. This is transactionally unsafe and should not be relied upon. Current resource is LastResourceRecord) and i don't know if there will be an issue in the future.

       

       

       

      2. Use distributed transactions. I configured two new XA Data Sources and two new persistence units associated to those datasources. I changed the entityManagers to point those new persistence units but the transaction fails when it tries to commit. I already set the max_prepared_transactions with the same value as maxSession property in both databases as the documentation suggests.

       

       

      I think that this is not a XA transaction because we are not going to update two different databases.

       

      Is any of these approaches correct? in order to continue testing or is there a better solution to achieve the requirement?

       

      I will appreciate any suggestion.

      Thanks.

        • 1. Re: Multiple DataSources in one transaction
          Tom Jenkinson Master

          I am guessing that you are using WildFly?

           

          Based on your error log about ARJUNA012141 I suspect that you have misconfigured your datasources as standard datasources. You need to configure them as xa-datasource: DataSource configuration - WildFly 8 - Project Documentation Editor

           

          Best wishes,

          Tom

          • 2. Re: Multiple DataSources in one transaction
            Ondra Chaloupka Master

            hi juapeqf, as Tom pointed out I think all is about configuration of your datasources (expecting you are running on the WFLY).

             

            In case `1)` you configured two `jta` capable datasources. If a bean is configured to operate under the transaction context then all the resources are tried to be enlisted under the global transaction. Enlistment of multi non-XA datasources is not permitted by default as it's not safe. If you don't care you can change the property `<property name="com.arjuna.ats.arjuna.allowMultipleLastResources" value="true"/>` as you did. But the warning will be shown as there is a danger of data integrity violation.

            An option for you could be using non jta datasource (`<datasource jta="false"  . . . >`) for the read-only resource. As you know will never write to it, it's probably not needed to be managed by container (meaning you don't care about rollback as there is nothing to rollback).

             

            In case `2)` I would need to know what is the exception what you get (taken from your statement ` I changed the entityManagers to point those new persistence units but the transaction fails when it tries to commit.`). It could be your postgresql installation does not permit processing of XA transactions, kind of getting `org.postgresql.util.PSQLException: ERROR: prepared transactions are disabled`? If you change the config `max_prepared_transactions` you need to restart the db, what I know.

            • 3. Re: Multiple DataSources in one transaction
              Juan Quiros Newbie

              Hi Ondra,

               

              Thank you very much for your suggestions.

               

              You're right, I'm using wildly. I just made the change suggested in case 1 (jta=false) and it is working so far. I have to continue testing different scenarios. But, as you said, this read-only resource will be used just for read operations so, i don't care about rollbacks.

               

              In case 2, that's true, I restarted the database when I changed the parameter. The exception was the next:

               

              "javax.transaction.HeuristicMixedException" due to "Suppressed: org.postgresql.xa.PGXAException: Error preparing transaction" and Caused by: org.postgresql.util.PSQLException: ERROR: cannot execute PREPARE TRANSACTION during recovery

               

              At the beginning i think that it was a distributed transaction because more than one DB was involved. But, as I mentioned and let me know if i'm wrong... the context of the requirement is not related to a XA transaction, because we don't want to update more than one database.

               

              Please check my data sources and tell me if you have any other suggestion:

               

              <datasources>

                              <datasource jta="true" jndi-name="java:/DSReadWrite" pool-name="ReadWrite" enabled="true" use-java-context="true" use-ccm="false">

                                  <connection-url>jdbc:postgresql://readwritedatabaseserver:5432/databaseName</connection-url>

                                  <driver-class>org.postgresql.Driver</driver-class>

                                  <driver>postgresql</driver>

                                  <pool>

                                      <min-pool-size>10</min-pool-size>

                                      <max-pool-size>100</max-pool-size>

                                      <prefill>false</prefill>

                                      <use-strict-min>false</use-strict-min>

                                      <flush-strategy>FailingConnectionOnly</flush-strategy>

                                  </pool>

                                  <security>

                                      <user-name>user</user-name>

                                      <password>pass</password>

                                  </security>

                                  <validation>

                                      <validate-on-match>false</validate-on-match>

                                      <background-validation>false</background-validation>

                                      <background-validation-millis>10</background-validation-millis>

                                  </validation>

                                  <statement>

                                      <prepared-statement-cache-size>0</prepared-statement-cache-size>

                                      <share-prepared-statements>false</share-prepared-statements>

                                  </statement>

                              </datasource>

               

                              <datasource jta="false" jndi-name="java:/DSReadOnly" pool-name="ReadOnlyPool2" enabled="true" use-java-context="true" use-ccm="false">

                                  <connection-url>jdbc:postgresql://readonlydatabaseserver:5432/databaseName</connection-url>

                                  <driver-class>org.postgresql.Driver</driver-class>

                                  <driver>postgresql</driver>

                                  <pool>

                                      <min-pool-size>10</min-pool-size>

                                      <max-pool-size>100</max-pool-size>

                                      <prefill>false</prefill>

                                      <use-strict-min>false</use-strict-min>

                                      <flush-strategy>FailingConnectionOnly</flush-strategy>

                                  </pool>

                                  <security>

                                      <user-name>user</user-name>

                                      <password>pass</password>

                                  </security>

                                  <validation>

                                      <validate-on-match>false</validate-on-match>

                                      <background-validation>false</background-validation>

                                      <background-validation-millis>10</background-validation-millis>

                                  </validation>

                                  <statement>

                                      <prepared-statement-cache-size>0</prepared-statement-cache-size>

                                      <share-prepared-statements>false</share-prepared-statements>

                                  </statement>

                              </datasource>

               

               

              Regards and thanks again.

               

              Juan

              • 4. Re: Multiple DataSources in one transaction
                Tom Jenkinson Master

                These should be <xa-datasource> and configured in the standalone.xml as per the link I provided earlier. It's still worth making them XA so that you can have the xa locking.

                • 5. Re: Multiple DataSources in one transaction
                  Ondra Chaloupka Master

                  Hi juapeqf, as Tom suggested you posted here the configuration of datasources `<datasource ...` and not the xa datasources `<xa_datasource ...>`. It would be good to see what are exact settings for your xa-datasources. I incidentaly hit your question on stackoverflow (https://stackoverflow.com/questions/45867536/postgresql-xa-transaction-exception), thus it could be some specific postgresql settings issue.

                   

                  But true, JCA datasource options (https://wildscribe.github.io/Wildfly/10.0.0.Final/subsystem/datasources/xa-data-source/index.html) could be helpful here.

                  My blind try would be e.g. try to set 'same-rm-override' to 'false', or maybe 'no-tx-separate-pool' to set to 'true'

                  <xa-pool>
                    <is-same-rm-override>false</is-same-rm-override>
                    <no-tx-separate-pools>true</no-tx-separate-pools>
                  </xa-pool>
                  

                   

                  (a side note for the read only xa datasource I would consider to set the 'no-recovery' to 'true' but it's just optimization for the db being hit by recovery manager when not needed, but that does not help you in your trouble)

                  • 6. Re: Multiple DataSources in one transaction
                    Juan Quiros Newbie

                    Hi,

                    tomjenkinson and  ochaloup, Thanks for your advises again.

                     

                    Yes, here (java - Postgresql XA Transaction Exception - Stack Overflow ) you can see the complete stacktrace.

                     

                    That was the first thing I tried (XA DataSoucers). I thought that a XA Datasource was the solution, but , i think that it is used when you have to update more than one database and in this case we want to read from one and write in the other one. Is it right?

                     

                    These are my XA Data Sources:

                     

                    <xa-datasource jndi-name="java:/XAReadOnlyDS" pool-name="ReadOnlyXA" enabled="true" use-java-context="true">

                                        <xa-datasource-property name="ServerName">

                                            server

                                        </xa-datasource-property>

                                        <xa-datasource-property name="PortNumber">

                                            5438

                                        </xa-datasource-property>

                                        <xa-datasource-property name="DatabaseName">

                                            database

                                        </xa-datasource-property>

                                        <driver>postgresql</driver>

                                        <xa-pool>

                                            <min-pool-size>1</min-pool-size>

                                            <max-pool-size>250</max-pool-size>

                                            <prefill>true</prefill>

                                            <use-strict-min>true</use-strict-min>

                                            <flush-strategy>FailingConnectionOnly</flush-strategy>

                                            <pad-xid>false</pad-xid>

                                            <wrap-xa-resource>true</wrap-xa-resource>

                                        </xa-pool>

                                        <security>

                                            <user-name>snap</user-name>

                                            <password>TnTz6oxPE9</password>

                                        </security>

                                        <validation>

                                            <valid-connection-checker class-name="org.jboss.jca.adapters.jdbc.extensions.postgres.PostgreSQLValidConnectionChecker"/>

                                            <exception-sorter class-name="org.jboss.jca.adapters.jdbc.extensions.postgres.PostgreSQLExceptionSorter"/>

                                        </validation>

                                        <statement>

                                            <track-statements>TRUE</track-statements>

                                            <prepared-statement-cache-size>100</prepared-statement-cache-size>

                                            <share-prepared-statements>true</share-prepared-statements>

                                        </statement>

                                    </xa-datasource>

                     

                                    <xa-datasource jndi-name="java:/XAReadWriteDS" pool-name="ReadWriteXA" enabled="true" use-java-context="true">

                                        <xa-datasource-property name="ServerName">

                                           server

                                        </xa-datasource-property>

                                        <xa-datasource-property name="PortNumber">

                                            5438

                                        </xa-datasource-property>

                                        <xa-datasource-property name="DatabaseName">

                                            database

                                        </xa-datasource-property>

                                        <driver>postgresql</driver>

                                        <xa-pool>

                                            <min-pool-size>1</min-pool-size>

                                            <max-pool-size>250</max-pool-size>

                                            <prefill>true</prefill>

                                            <use-strict-min>true</use-strict-min>

                                            <flush-strategy>FailingConnectionOnly</flush-strategy>

                                            <pad-xid>false</pad-xid>

                                            <wrap-xa-resource>true</wrap-xa-resource>

                                        </xa-pool>

                                        <security>

                                            <user-name>snap</user-name>

                                            <password>TnTz6oxPE9</password>

                                        </security>

                                        <validation>

                                            <valid-connection-checker class-name="org.jboss.jca.adapters.jdbc.extensions.postgres.PostgreSQLValidConnectionChecker"/>

                                            <exception-sorter class-name="org.jboss.jca.adapters.jdbc.extensions.postgres.PostgreSQLExceptionSorter"/>

                                        </validation>

                                        <statement>

                                            <track-statements>TRUE</track-statements>

                                            <prepared-statement-cache-size>100</prepared-statement-cache-size>

                                            <share-prepared-statements>true</share-prepared-statements>

                                        </statement>

                                    </xa-datasource>

                    • 7. Re: Multiple DataSources in one transaction
                      Ondra Chaloupka Master

                      Hi juapeqf, I think the important point is what you *want* from the settings.

                       

                      The XA handling can bring you a benefit of transaction isolation. I think this is an only merit (if I'm not wrong) that can bring you transactions in this case. PostgreSQL uses some MVCC processing, then you get a "data view" of the time the transaction started for whole duration of the transaction. Reading a record twice during the transaction duration will return the same value. In case you don't enlist the datasource to the global transaction it could happen that two sequential reads, interleaved with another transaction working with the same record, could return different value each time. But as you use JPA it should not be concern for you even when you use the '<datasource jta="false"...>', maybe depending on some special hibernate settings.

                       

                      Anyway, I think if you really only reads from the datasource and you don't concern in isolation it's fine to use non-jta datasource.

                       

                      If you want the datasource being enlisted to the transaction then you need to set the read-write datasource as xa-datasource. The read-only datasource needs to be

                       

                      • either tuned with jca settings to work with the postgresql master-slave functionality. As I'm not familiar with specifics I'm not sure here but as I put to my previous post you could try to set 'is-same-rm-override' to 'true'. This could help if jdbc driver would be trying to join the operations from both datasources to one database transaction. I'm really not sure if it can happen (my understanding is having the same postgresql for both datasources but only using different machine to connect to) - but you can try. Or check other datasource setings.
                      • or set the read-only as <datasource jta="true"...> (but read-write is defined as xa-datasource). Then read-only will be part of the global transaction and only during commit the LRCO (http://narayana.io/docs/project/index.html#d0e1859) will be used (tt's automatic handling in Narayana and you don't need to care).
                      • 8. Re: Multiple DataSources in one transaction
                        Tom Jenkinson Master

                        I do agree with Ondra - again assuming you are happy with the relaxation on read isolation.

                        • 9. Re: Multiple DataSources in one transaction
                          Juan Quiros Newbie

                          HI tomjenkinson/ ochaloup,

                           

                          Thank you very much for your advises. I really appreciate the time you took to help me. I will try both approaches in order to define which will be the better.

                           

                          Thanks.

                          • 10. Re: Multiple DataSources in one transaction
                            Tom Jenkinson Master

                            No problem - good luck with your research!

                            • 11. Re: Multiple DataSources in one transaction
                              Manuel Fehlhammer Newbie

                              Stumbled across this post. Very interessting read!

                              I do have a quite similar problem/setup:

                              - using two distinct JDBC data-sources

                              - one being read-write

                              - one being read-only

                               

                              In such a scenario  I don't want to have necessarily the overhead of XA. (Might be even the case, that some of the data-sources does not even provide XA capable driver).

                               

                              Now: Regarding one of the proposals here in the thread: I could configure e.g. my read-only data-source with '<datasource jta="false"...>'.

                              I also did this for my scenario described above and it worked.

                              But what is interessting here:

                              - although I annotate the data-source being jta="false" in the wildfly config, in the J2EE persistence.xml for my read-only persistence-unit I set the attribute transaction-type="JTA".

                              - I do this (transaction-type="JTA"), because - as per JPA/J2EE spec I want to use normal DI for my EntityManager etc ...

                              - so from, a first view, it looks contradicting: Wildfly config says "datasource jta="false"" and persistence.xml of my deployment says transaction-type="JTA"

                              - my interpretation is, that both settings have different meaning. With the wildfly config of datasource I can hinder enlistement of the datasource to a "global" tx, while the configuration on persistence-unit simply influences, what interfaces the container provides to my application code...

                               

                              Can someone shed some light of those differences? As I said: My Setup seems to work, but it feels a bit iffy

                              • 12. Re: Multiple DataSources in one transaction
                                Tom Jenkinson Master

                                As you say, they are slightly different settings. One is controlling the type of transaction that is being registered into (persistence-unit) and one is controlling how it is (datasource). When jta=false on the datasource that means that it will be wrapped as it is not capable of fulfilling the jta contract.

                                • 13. Re: Multiple DataSources in one transaction
                                  Manuel Fehlhammer Newbie

                                  tomjenkinson

                                   

                                  Thanks for your quick reply!
                                  I somehow "got it". So I feel, that my initially chosen setup:
                                  - read-write datasource with <datasource jta="true".../> and persistent-unit with <jta-data-source> in persistence.xml
                                  - read-only datasource with <datasource jta="false".../> and persistent-unit with <jta-data-source> in persistence.xml

                                  although it "works" in wildfly - it is not the "intended" usage
                                  I mean: When using <jta-data-source> inside persistence.xml instead of <non-jta-data-source>, then I do express, that I expect a certain behavior/functionality of the container infrastructure.
                                  Namely, that the container is able to fullfill the transaction annotations, which I place at my session-bean methods with @TransactionAttribute annotations... which is in reality not possible at all, when the underlying data-source is configured with <datasource jta="false".../>

                                  So, the "cleaner" approach would be for my read-only datasource:

                                  • set <datasource jta="false".../> AND persistent-unit with <non-jta-data-source> in persistence.xml
                                  • in source code then use for this read-only data-source (this is what JPA/J2EE require for <non-jta-data-source> afaik):
                                    • @PersistenceUnit instead of @PersistenceContext
                                    • EntityManagerFactory.createEntityManager()
                                  • explicit calls to em.getTransaction().begin() and em.getTransaction().commit()

                                  By the way:
                                  I now went the other route: My read-write datasource is now a xa-datasource, the read-only datasource is a jta-datasource and I rely on the Last resource commit optimization (LRCO) discussed in this thread.

                                  • 14. Re: Multiple DataSources in one transaction
                                    Ondra Chaloupka Master

                                    hi crimson ,

                                     

                                    I agree that's not the "intended" usage as you say. Only I think it's not specified elsewhere. My summary would be that the intended use is

                                    • <datasource jta="true".../> in WFLY standalone.xml, <jta-data-source> in persistence.xml with transaction-type="JTA" ("JTA" is default value for managed (application containers) deployments
                                    • <datasource jta="false".../> in WFLY standalone.xml, <non-jta-data-source> in persistence.xml with transaction-type="RESOURCE_LOCAL"

                                     

                                    From my experience, setting the datasource to true and using non-jta-data-source tag in persistence.xml does not cause any effect and the non-jta-data-source is taken as jta-data-source. What made changes it was use of the attribute transaction-type. (I was doing some checks on an older versions of the JBoss, not on the newest WildFly)

                                    1 of 1 people found this helpful
                                    1 2 Previous Next