Spring Data Rollback only rolls back partially

Question

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.

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? — Jens Schauder, Jun 24 '22 at 05:55
@JensSchauder that was it! Thank you so much, I was inadvertently committing prior to starting the transaction in my stored procedure. After removing that I am receiving the expected behavior. — Justin Grahn, Jun 24 '22 at 18:57

score 1 · Accepted Answer · answered Jun 27 '22 at 06:35

1

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 Jun 27 '22 at 06:35

Jens Schauder

77,657
34
181
348

To add clarification for future observers, this was occurring due to a `commit transaction` statement being executed in the stored proc prior to creating a transaction. `IF @@TRANCOUNT > 0 COMMIT TRANSACTION; BEGIN TRANSACTION` – Justin Grahn Jun 27 '22 at 15:45

Spring Data Rollback only rolls back partially

1 Answers1