Transactions with complex structure isn't persisting to database

Support
#1

Hello,
I’m facing with a weird issue and would like to get your advice on this.
I have a scenario which I need to update/insert into multiple tables, I created a middleware service with transaction similar to below code.

try (Transaction tx = persistence.createTransaction()) {
//step 1: create a new effective group / end date existing group
		final String getExistingEffectiveGroup = "select ef from service$ServiceEffective ef where ef.expiryDate is null";
		Query query = persistence.getEntityManager().createQuery(getExistingEffectiveGroup, ServiceEffective.class);
		List<ServiceEffective> exitingEffectiveDates = query.getResultList();
		
		//Expire current group
		final String expireEffectiveGroup = "update service_effective_table set expiry_date = current_timestamp where expiry_date = null";
		Query expireGroupQuery = persistence.getEntityManager().createNativeQuery(expireEffectiveGroup);
		expireGroupQuery.executeUpdate();
		
		final String facilitySql = "select e from service$Facility e join fetch e.type where e.type.name = 'Facility'";
		Query facilityQuery = persistence.getEntityManager().createQuery(facilitySql, Facility.class);
		List<Facility> facilities = facilityQuery.getResultList(); 
		for(Facility f : facilities) {
			ServiceEffective effective = metadata.create(ServiceEffective.class);
			//effective.setId(UUID.randomUUID());
			effective.setFacility(f);
			effective.setEffectiveDate(new Date());
			
			persistence.getEntityManager().persist(effective);
		}
		
		//step 2: update editing params to newly created group
		final String updateServiceParamsEditingSql = "update service_table_editing  " + 
				"set effective_group_id = ?1 " + 
				"where effective_group_id = ?2 ";
		
		final String getCurrentEffectiveDateByFacility = "select ef from service$ServiceEffective ef "
				+ "where ef.expiryDate is null and ef.facility.id = ?1";
		
		for(ServiceEffective ef : exitingEffectiveDates) {
			Query newEffectiveDateQuery = persistence.getEntityManager().createQuery(getCurrentEffectiveDateByFacility);
			newEffectiveDateQuery.setParameter(1, ef.getFacility().getId());
			List<ServiceEffective> newEffectiveDates = newEffectiveDateQuery.getResultList();
			ServiceEffective effectiveDate = newEffectiveDates.get(0); //should only return 1
			
			Query updateServiceParamsEditingQuery = persistence.getEntityManager().createNativeQuery(updateServiceParamsEditingSql);
			updateServiceParamsEditingQuery.setParameter(1, ef.getId());
			updateServiceParamsEditingQuery.setParameter(2, effectiveDate.getId());
			
			int updatedParams = updateServiceParamsEditingQuery.executeUpdate();
			System.out.println(ef.getFacility().getAbbrev() + " num of updated param:  " + updatedParams);
		}
		
		//step 3: move all editing params to main table.
		final String createSystemParamsSql = "insert into service_table(id,version,key_,type_,value_,effective_group_id,created_by,create_ts) \n" + 
				" select uuid_generate_v4(),1 as version, key_,type_,value_,effective_group_id,ef.created_by,ef.create_ts \n" + 
				" from service_table_editing rspe \n" + 
				" join service_effective_table ef on rspe.effective_group_id = ef.id \n" + 
				" where ef.expiry_date is null";
		Query createSystemParamsQuery = persistence.getEntityManager().createNativeQuery(createSystemParamsSql);
		int numOfParams = createSystemParamsQuery.executeUpdate();
		System.out.println("newly created params: " + numOfParams);
		//step 4: send all params in editing table with effective date as current date to kafka topic
		final String effectiveParamsSql = "select sp from service$ServiceParameter sp "
				+ " join fetch sp.effectiveGroup"
				+ " where sp.effectiveGroup.expiryDate is null";
		Query effectiveParamsQuery = persistence.getEntityManager().createQuery(effectiveParamsSql, ServiceParameter.class);
		List<ServiceParameter> effectiveParams = effectiveParamsQuery.getResultList();
		
		//step 5: commit and conclude transaction.
		tx.commit();			
		
	}

Everything’s working without error, I’m able to confirmed with print out (see code) that the insert and update return with positive count.
But after the execution and commit, only the entries used persist statement found on database, none of other found, what did I do wrong and how should I correct this? is there any restriction on using entity type persist and native query update?

Thanks,

#2

Hi,
Your code snippet looks fine.

Note that every time you make a native query, CUBA / EclipseLink make an automatic flush of not yet committed entity changes to the database (sends inserts for persisted entities and updates for merged entities to the database, but keeps current transaction open).

If you observe non-committed changes, I suspect that your code is actually more complex than you’ve presented here. Maybe in some cases tx.commit() is not always called (exception in the middle of the try statement).

To debug the problem, you can enable eclipselink and eclipselink.sql loggers. See here: Useful Loggers - CUBA Platform. Developer’s Manual
You will see transaction number and connection number in the log. It will help you to ensure that all changes are actually performed in the same transaction.

If the problem persists and you will be able to reliably reproduce it, please try to create small sample project with minimal data model and logic that reproduces the bug. In this case we will be able to investigate the problem from our side.

#3

I would also kindly recommend to use these methods instead of try-with-resources, when you need to use long transaction:

        persistence.runInTransaction(em -> {
            em.createQuery(). // ...
        });
        
        long result = persistence.callInTransaction(em -> {
            return em.createQuery(). // ...
        });

These methods are easier to use and they automatically commit transaction, so you don’t have to remember about it.

#4

Thanks for the suggestion Alex!