Exception thrown tied to race condition issue in java program using @Transaction and @Async annotations for transaction management

I am using springboot with JdbcTemplate for persistence. I have a series of separate database steps that must be orchestrated properly as an atomic transaction. However, there is one highly time-consuming step that I calling asynchrously because, well because its really time-consuming.

The problem I am having is a race-condition I am not managing properly, and that its causing the transaction to fail.

Below is the code. Its highly simplified in order to focus on the known issue, which is I always get the following exception thrown at the updateChild() method (again I had to simplify some exception detail):

SimpleAsyncUncaughtExceptionHandler","method":"handleUncaughtException","file":"SimpleAsyncUncaughtExceptionHandler.java","line":39,"stack":"org.springframework.dao.DataIntegrityViolationException: StatementCallback; SQL [UPDATE childTable SET child_fk = 'Foo' WHERE child_fk = 'Bar' ]; ERROR: insert or update on table \"childTable\" violates foreign key constraint \"child_fk_fkey\"\n  Detail: Key (child_fk)=Bar  is not present in table \"parentTable\".

Its essentially saying the child record can't be updated because the new parent record has not been inserted yet. This is the race condition I don't know how to address (i.e, how to make sure the new parent record is inserted before attempting to update the child records.

Its important to point out that this transactional code works fine if I just comment out the two async-related annotations, its just really slow. I would be grateful for any advice about what I can do to fix this situation. Thanks.

@Repository
public class MainRepositoryClass {

@Autowired
protected JdbcTemplate jdbcTemplate;

    // this method is called from a controller class. It starts and ends overall transaction
    @Transactional
    public void updateParentWithCascadetoChildren(String parentTableName, ParentObj pObjOld,  ParentObj pObjNew){

        jdbcTemplate.update( <query here creates new parent record with new primary key>);
        determineChildTables(parentTableName, ParentObj pObjOld,  ParentObj pObjNew);
        jdbcTemplate.update( <query here deletes the original parent record>);
    }

    // this method determines the child record dependencies and calls another repo method
    // (in another repo class) that asynchronously updates the child record
    @Transactional
    public void determineChildTables(String parentTableName, ParentObj pObjOld,  ParentObj pObjNew){
        childMapping = jdbcTemplate.query(<query calls a view that returns affected child tables>);

        for(Child child : childMapping) {
            public void updateChild(child, pObjbNew);
        }
    }
}


@Repository
@EnableAsync
public class SecondRepositoryClass {

@Autowired
protected JdbcTemplate jdbcTemplate;

    @Transactional
    @Async
    public void updateChild(Child child, ParentObj pObjNew){
        // here is the step where the exception is thrown
        jdbcTemplate.update( <query here updates the child record on child table with new foreign key dependency value>);
    }
}

Solution

I finally figured out that my problem was that there were several dozen asynchronously-running threads that, at the postGres level, were contending for access to the parent table as part of the updating of the new parent key value (the foreign key value-change they had to perform locally).

So, I needed to set up a mutex (in this instance, I used a single java Semaphore) that the individual threads have to share in order to properly take turns attempting their update. This resolved my problem.

Also, at the end, the last thread is required to delete the original parent table before the semaphore is released for the last time.

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.scheduling.annotation.Async;
import org.springframework.scheduling.annotation.EnableAsync;
import org.springframework.stereotype.Repository;

import javax.transaction.Transactional;
import java.util.concurrent.Semaphore;

@Repository
@EnableAsync
public class SecondRepositoryClass {
    static Semaphore semaphore = new Semaphore(1);
    static int threadCount = 0;

    @Autowired
    protected JdbcTemplate jdbcTemplate;

    @Transactional
    @Async
    public void updateChild(Child child, ParentObj pObjNew){

        try {
            semaphore.acquire();

            try {            
            jdbcTemplate.update( <query here updates the child record on child table with new foreign key dependency value>);
            } finally {
                decrementThreadCount();

                if (threadCount == 0) { // last thread deletes original lookup table record
                jdbcTemplate.update("query to delete original parent table");
                semaphore.release();
            }
        } catch (InterruptedException e) {
            e.printStackTrace();
        }
    }

    private synchronized void decrementThreadCount() {
        threadCount--;
    }

}