Spring Data Rollback only rolls back partially

Issue

While integration testing, I am attempting to test the execution of a stored procedure. To do so, I need to perform the following steps:

• Insert some setup data
• Execute the stored procedure
• Find the data in the downstream repo that is written to by the stored proc

I am able to complete all of this successfully, however, after completion of the test only the rows written by the stored procedure are rolled back. Those rows inserted via the JdbcAggregateTemplate are not rolled back. Obviously I can delete them manually at the end of the test declaration, but I feel like I must be missing something here with my configuration (perhaps in the @Transactional or @Rollback annotations.

@SpringBootTest
@Transactional
@Rollback
@TestInstance(TestInstance.Lifecycle.PER_CLASS)
class JobServiceIntegrationTest @Autowired constructor(
    private val repo: JobExecutorService,
    private val template: JdbcAggregateTemplate,
    private val generatedDataRepo: GeneratedDataRepo,
) {

    @Nested
    inner class ExecuteMyStoredProc {

        @Test
        fun `job is executed`() {
            // arrange
            val supportingData = supportingData()

            // act
            // this data does not get rolled back but I would like it to
            val expected = template.insert(supportingData)

            // this data does get rolled back
            repo.doExecuteMyStoredProc()

            val actual = generatedDataRepo.findAll().first()

            assertEquals(expected.supportingDataId, actual.supportingDataId)
        }
    }

    fun supportingData() : SupportingData {
        ...
    }
}

If this was all done as part of a physical database transaction, I would anticipate the inner transactions are all rolled back when the outer transaction rolls back. Obviously this is not that, but that's the behavior I'm hoping to emulate.

I've made plenty of integration tests and all of them roll back as I expect, but typically I'm just applying some business logic and writing to a database, nothing as involved as this. The only unique situations about this test from my other tests is that I'm executing a stored proc (and the stored proc contains transactions).

I'm writing this data to a SQL Server DB, and I'm using Spring JDBC with Kotlin.

Solution

Making my comment into an answer since it seemed to have solved the problem:

I suspect the transaction in the SP commits the earlier changes. Could you post the code for a simple SP that causes the problems you describe, so I can play around with it?

Answered By - Jens Schauder