Issue using @Transactional annotation above integration test class for multithreading environment

Question

When I run the integration test for code which calls JPA repository within a new thread, I'm getting data that was populated during starting PostgreSQLContainer and I can't receive data from the script above class test( @Sql(scripts ="data.sql").
But when I remove @Transactional annotation above the test I can get data both from SQL script from test and test container.
My question is it possible to get data in a multithreading environment from test script without removing @Transactional annotation? Thank you for your answer!

Application stack: Spring boot 2.1v+ test containers PostgreSQL 1.10.3v+ JUnit 4.12v

DB testcontainers config

@TestConfiguration
public class DatabaseTestConfig {

    private static JdbcDatabaseContainer PSQL;

    static {

        PSQL = (PostgreSQLContainer) new PostgreSQLContainer("mdillon/postgis:9.4").withUsername("test")
                .withPassword("test")
                .withDatabaseName("test");
        PSQL.start();

        Arrays.asList("main_data.sql")
                .forEach(DatabaseTestConfig::restoreDump);

        /*
           set db properties
        */

    }

    public void restoreDump(String fileName){
         /*
           insert sql data 
           PSQL.copyFileToContainer(fileName)...
        */
    }

}

Base Integration Test class

@RunWith(SpringRunner.class)
@SpringBootTest(classes = { DatabaseTestConfig.class, ProjectApplication.class })
@ActiveProfiles("test-int")
@AutoConfigureMockMvc
@Sql(scripts = "classpath:extra_data.sql") // insert some extra data for all integration tests
public abstract class AbstractIntTest {
    @Autowired
    protected MockMvc mockMvc;

Integration Test that calls service where everething happenes

@Transactional
public class SomeIntegrationTest extends AbstractIntTest {


    @Before
    public void setUp() throws IOException {
      //...
    }

    @Test
    public void callServiceTest() throws Exception {
      //mockMvc.perform(post(ENDPOINT_URL)
    }

Service with simplified logic

@Service
@AllArgsConstructor
public class SomeService {
    private final SomeJpaReporistory repo;
    private final ExecutorService executor;

    @Override
    @Transactional
    public SomeData call(){
        return CompletableFuture.supplyAsync(() -> {
            return repo.findAll(); 
        }, executor).exceptionally(e -> {
            throw new BadRequestException(e.getMessage());
        });
    }

Answer 1

When you make the test transactional, the SQL queries in extra_data.sql are performed in a transaction. That transaction is bound to a particular thread and is begun before execution of the test method and rolled back after the test method has completed:

1. Begin transaction
2. Execute extra_data.sql
3. Invoke test method
4. Roll back transaction

In step 3 you are calling repo.findAll() on a separate thread due to your service's use of supplyAsync. As a transaction is bound to a particular thread, this findAll() call is not part of the transaction in which extra_data.sql was executed. To be able to read the data added by extra_data.sql, it would have to be able to read uncommitted changes and perform a dirty read. Postgres does not support the read uncommitted isolation level so this isn't possible.

You'll need to revisit how you're populating your database with test data or your use of transactions in your tests. Perhaps you could apply extra_data.sql to the database in the same manner as main_data.sql so that it's always in place before any tests are executed and before and transactions are begun.

Answer 2

This is how I've solved this problem:

@Test
@Transactional
@Sql(scripts = "/db/extra_data.sql",
config = @SqlConfig(transactionMode = SqlConfig.TransactionMode.ISOLATED))
void test() {
    // extra_data.sql are executed before this test is run.
}