`@Sql` fails if `DataSource` is wrapped in a `TransactionAwareDataSourceProxy`

[jmaniquet]

Hi Spring team

I would like to report a behaviour I encoutered using TransactionAwareDataSourceProxy in my tests, which I think may be a bug.

I used Spring Boot 4.0.3 but I believe any 4.0.X and earlier versions exhibit the same behaviour (see below).

I have a bunch of DB related sliced tests. A mix of @JdbcTest and @DataJdbcTest to be specific.

• Flyway to initialize the database schema
• Many of the tests use @Sql to prepare Data
• I do assertions on the state of the database using the AssertJ DB library

This is not directly related to AssertJ DB, but the way I use it led me to encounter the problem.
The AssertJ DB library is (obviously) an AssertJ-like library providing assertions on the database.
It needs a DataSource as an input. And since it is not a Spring library, it is not aware of the Spring transaction.

My current use of it is to wrap the DataSource with TransactionAwareDataSourceProxy for every call to the library; and then perform the DB assertions.

It works fine, but I wanted to know if I could avoid doing the wrapping on each call, by wrapping the autoconfigured DataSource only once with the proxy at the context level.

The Javadoc for BeanPostProcessor made it look like a good fit (the postProcessAfterInitialization part) :

Typically, post-processors that populate beans via marker interfaces or the like will implement postProcessBeforeInitialization, while post-processors that wrap beans with proxies will normally implement postProcessAfterInitialization.

This looks like this :

@SpringBootApplication
public class DsTxAwareApp {

    public static void main(String[] args) {
        SpringApplication.run(DsTxAwareApp.class, args);
    }

    @Bean
    static TxAwareDataSourceBeanPostProcessor txAwareDataSourceBeanPostProcessor() {
        return new TxAwareDataSourceBeanPostProcessor();
    }

    static class TxAwareDataSourceBeanPostProcessor implements BeanPostProcessor {

        @Override
        public Object postProcessAfterInitialization(Object bean, String beanName) throws BeansException {
            if (bean instanceof DataSource targetDataSource) {
                return new TransactionAwareDataSourceProxy(targetDataSource);
            }

            return bean;
        }
    }
}

After that, I was expecting that any interaction with the DataSource would be aware of the Spring transaction, without having to do the wrapping again.
It works in some cases, however any tests using @Sql fails with something like this :

java.lang.IllegalStateException: Failed to execute SQL scripts for test context [...] 
the configured DataSource [org.springframework.jdbc.datasource.TransactionAwareDataSourceProxy] (named '') is not the one associated with transaction manager [org.springframework.jdbc.support.JdbcTransactionManager] (named '')
at org.springframework.test.context.jdbc.SqlScriptsTestExecutionListener.executeSqlScripts(SqlScriptsTestExecutionListener.java:355)

This happens when executing the @Sql, so independently of any use of the AssertJ DB Library; hence unrelated.

Here is a simplified version of the test, using @Sql and raw Jdbc Usage (no AssertJ DB) :

@JdbcTest(includeFilters = @Filter(classes = FooJdbcDao.class, type = ASSIGNABLE_TYPE))
class FooJdbcDaoTest {
    @Autowired
    private FooJdbcDao underTest;

    @Autowired
    private DataSource dataSource;

    @Test
    @Sql(statements = "insert into FOO (ID, BAR) values (12, 'dummy')")
    void testWithExistingRow() {

        assertThat(count()).isEqualTo(1);

        Foo foo = Foo.builder()
                    .id(17)
                    .bar("whatever")
                    .build();

        underTest.insertFoo(foo);

        assertThat(count()).isEqualTo(2);
    }

    private int count() {
        try (
                Connection con = dataSource.getConnection();
                PreparedStatement statement = con.prepareStatement("select count(*) as count from foo");
                ResultSet result = statement.executeQuery()
        ) {
            result.next();
            return result.getInt("count");
        } catch (SQLException e) {
            throw new RuntimeException("Error during count");
        }
    }
}

My understanding is that it happens in the SqlScriptsTestExecutionListener, at a point where there is some logic to match a DataSource against its associated PlatformTransactionManager. Maybe it has to do with apps using several DataSource (just for my knowledge) ?

Now when trying the BeanPostProcessor approach, I saw that in the DataSourceTransactionManager there is a need to check for such a proxy.
I then assumed that there could be other places in the framework where that was necessary; but maybe my use case was never intended to be supported.

I put up a reproducer on my gitlab : https://gitlab.com/jmaniquet-prototypes/data-source-tx-aware

• main branch : @JdbcTest / @Sql - no BeanPostProcessor, verifies data by raw Jdbc, wrapping the DataSource on each call interacting directly with
• tx-aware-proxy-with-bean-post-processor branch : remove the wrapping on each call, and use the BeanPostProcessor - @Sql then tests starts failing
• older-spring-boot - Same with older versions of Spring Boot - I wanted to see if at some point it used to work (it did not)

There is an additional branch with more complex set up which may be to much for a reproducer : AssertJ DB is introduced and there is additional tests for @DataJdbcTest and @MybatisTest (integration with mybatis).
I wanted to know if other sliced tests had the same behaviour (they do).

This may be a spring only problem; not boot; I opened the ticket here, my setup being boot only.

If it is not intended to be supported, maybe this should be documented somewhere ?

Let me know if you need me to prune the repo or alter it (or if maybe I am doing something wrong).

Thanks for this amazing framework

Related Issues

• Improve transaction management for @Sql [SPR-11911] #16530
• Unwrap DataSource hidden behind InfrastructureProxy in SqlScriptsTestExecutionListener #26422

[wilkinsona]

Thanks for the sample.

Here's a minimal Spring Framework only test that reproduces the same failure:

package org.jmaniquet.prototypes.dstxaware.jdbcclient;

import javax.sql.DataSource;

import org.junit.jupiter.api.Test;
import org.springframework.context.annotation.Bean;
import org.springframework.context.annotation.Configuration;
import org.springframework.jdbc.datasource.TransactionAwareDataSourceProxy;
import org.springframework.jdbc.datasource.embedded.EmbeddedDatabaseBuilder;
import org.springframework.jdbc.datasource.embedded.EmbeddedDatabaseType;
import org.springframework.jdbc.support.JdbcTransactionManager;
import org.springframework.test.context.jdbc.Sql;
import org.springframework.test.context.junit.jupiter.SpringJUnitConfig;
import org.springframework.transaction.TransactionManager;

@SpringJUnitConfig
@Sql(statements = "SELECT 1")
public class ExampleTests {
	
	@Test
	void test() {
		
	}
	
	@Configuration(proxyBeanMethods = false)
	static class ExampleConfiguration {
		
		@Bean
		DataSource dataSource() {
			DataSource dataSource = new EmbeddedDatabaseBuilder().setType(EmbeddedDatabaseType.H2).build();
			return new TransactionAwareDataSourceProxy(dataSource);
		}
		
		@Bean
		TransactionManager transactionManager(DataSource dataSource) {
			return new JdbcTransactionManager(dataSource);
		}
		
	}

}

We'll transfer this to the Framework team so that they can investigate further. I'm not sure if TransactionAwareDataSourceProxy and @Sql are a supported combination.

[jmaniquet]

Thank you for your feedback.

I'm not sure if TransactionAwareDataSourceProxy and @Sql are a supported combination.

I knew this was a possibility.
I would like to give more context about my intention, for consideration on whether to support this or not.

My initial intention was to open a feature request on the Spring Boot side.
My wish was for easier integration of AssertJ DB by making the test DataSource transaction aware at the Spring Boot level.
Probably activated based on a condition (maybe a property, something like : spring.datasource.tx-aware.enabled)

I tried this to see if there is any chance it would work before opening the feature request.

If there is any interest for such a feature, it would probably require fixing this first.
I would understand if it's a no though.

I will be away for a few days and won't be able to discuss it during that time.

[sbrannen]

Thanks for reporting this, @jmaniquet.

And thanks for the simplified reproducer, @wilkinsona.

I've labeled this a bug, since it was not our intention to prevent such uses cases.

In addition, DataSourceTransactionManager.setDataSource(DataSource) already unwraps a TransactionAwareDataSourceProxy, so it makes sense to do the same in SqlScriptsTestExecutionListener.

Related Issues

• Improve transaction management for @Sql [SPR-11911] #16530
• Unwrap DataSource hidden behind InfrastructureProxy in SqlScriptsTestExecutionListener #26422