10 Replies Latest reply on Aug 31, 2017 11:02 AM by Tom Jenkinson

    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 Expert

            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 Expert

                  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 Expert

                      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!