0

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());
        });
    }
arakorno
  • 3
  • 1
  • 4
  • Can you provide a [minimal, complete, and verifiable example](/help/mcve) that shows what your test is doing? It's also worth noting that making tests `@Transactional` means that the changes they make to the database will be rolledback and that transactions are scoped to a particular thread. This latter point means that, depending on the isolation level that your database is using, changes made in a transaction that has not yet been committed may not be visible on another thread. – Andy Wilkinson Oct 22 '19 at 11:13
  • The problem is in row repo.findAll(); When I put @Transactional above the integration test class I get data only from main_data.sql. Without this annotation, I get mixed data both from main_data.sql and extra_data.sql. – arakorno Oct 23 '19 at 14:27

2 Answers2

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 any transactions are begun.

Andy Wilkinson
  • 108,729
  • 24
  • 257
  • 242
1

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.
}
Sarvar Nishonboyev
  • 12,262
  • 10
  • 69
  • 70