Spring Boot test with @Sql locks tables and repositores and services cannot read

Question

I am using SQL Server 2016 as a production and development database (so no h2) with a Spring Boot 2 backend and Angular 7 frontend. If I run the application normally, everything works as intended. For integration testing (JUnit5), I would like to use Selenium, so the application needs to actually run on a port and be accessible with a browser. I also need to prepare some data before each test (as I cannot use production data). In order to achieve both, I planned to use spring's @Sql annotation, which allows me execute any .sql file before the tests (In my case, I insert data I would like to manipulate). Spring rolls back everything after the test, so It should work well. However, when I am inserting data, the transaction the test started locks the database tables and the other services/repositories the application use (to query data for example) are blocked.

Example.: I insert an employee in the file I linked in the @Sql annotation, then Selenium starts the browser and I navigate to a list with the employees. At this point, the employee listing won't work (which is served using a REST endpoint and the EmployeeRepository), because it is blocked by the test's transaction.

I can also confirm this locking manually, while the tests run, I cannot execute queries using SQL Server Management Studio (waiting and completes when I end the tests).

Can I use @Sql or any other data preparation tool for my tests and let the application work normally, while also being able to roll back the changes after the tests?

Test class:

@SpringBootTest(classes = ...App.class)
@ActiveProfiles(profiles = "test")
@ExtendWith(SpringExtension.class)
@Transactional
public class SeleniumExampleIT {
    @LocalServerPort
    protected int port;

    protected WebDriver driver;
    protected NgWebDriver ngWebDriver;

    @BeforeAll
    public static void setupClass() {
        WebDriverManager.chromedriver().setup();
    }

    @BeforeEach
    public void setupTest() {
        var chromeDriver = new ChromeDriver();
        driver = chromeDriver;
        ngWebDriver = new NgWebDriver(chromeDriver);
        ngWebDriver.waitForAngularRequestsToFinish();
    }

    @AfterEach
    public void teardown() {
        if (driver != null) {
            driver.quit();
        }
    }

    @Test
    @Sql({"classpath:sql/test.sql"})
    void listEmployeesTest() {
        //...starting selenium
        // navigating, waiting ..etc
    }
}

The EmployeeRepository:

@Repository
public interface EmployeeRepository extends JpaRepository<Employee, Long> {
    @Query("SELECT DISTINCT e FROM Employee e " +
        "LEFT JOIN FETCH e.settings")
    List<Employee> findAll();
    @Query("SELECT DISTINCT e FROM Employee e " +
        "LEFT JOIN FETCH e.settings " +
        "WHERE e.id = :id")
    Optional<Employee> findById(@Param("id") Long id);
}

Edit.: Annotating the JpaRepository methods with @Transactional did not solve the problem (as mentioned here).

Edit2.: According to Spring's docs

If your test is @Transactional, it rolls back the transaction at the end of each test method by default. However, as using this arrangement with either RANDOM_PORT or DEFINED_PORT implicitly provides a real servlet environment, the HTTP client and server run in separate threads and, thus, in separate transactions. Any transaction initiated on the server does not roll back in this case.

szab.kel · Accepted Answer · 2019-10-14T08:19:34.340

The only solution I could find was to NOT use @Transactional tests. I created a separate database which only used for test runs. I clean the database BEFORE each test using a script like this:

-- CHECK IF DATABASE HAS TESTING ALLOWED
IF (OBJECT_ID('dbo.tests_allowed') IS NULL)
BEGIN
    RAISERROR('Testing is NOT allowed in the selected database! Please, check carefully, before removing all data! If you want to allow testing, create table dbo.`tests_allowed`.', 16, 1)
END;

-- DISABLE CHECKS
ALTER TABLE dbo.<table-names> NOCHECK CONSTRAINT ALL;

-- TRUNCATE TABLES
DELETE FROM dbo.<table-names>;

-- ENABLE CHECKS
ALTER TABLE dbo.<table-names> WITH CHECK CHECK CONSTRAINT ALL;

After the truncate ran, I prepare the database with the data insert script, executing it like this:

@Test
@DisplayName("Checking settings....")
@Sql(value = {"classpath:sql/truncate-tables.sql", "classpath:sql/example-dataset-1.sql"},
    config = @SqlConfig(transactionMode = SqlConfig.TransactionMode.ISOLATED))
public void check...Test() {}

Using the ISOLATED mode, the Sql files will be commited right away and I can read the data during the tests.

This way.:

The database is always cleaned before the tests and I don't need to worry about dirty data during a test
If a test fails, I can check the database in the state it failed
Having the check before the truncate script prevents the test from running on a production or development environments (I manually add the tests_allowed table to the test db).
Don't need to worry about Hibernate not committing data or transactions locking/deadlocking

Spring Boot test with @Sql locks tables and repositores and services cannot read

1 Answers1