Multiple DataSources in one transaction
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.
