Mybatis doesn't Roll Back Sequences

Question

I have a problem creating test using Spring Boot + My Batis Spring Boot I have created a couple of U. Test for my mappers. For the insertions, I use the @SelectKey annotation to get the next value of the sequence. When Spring rolls back the transaction doesn't roll back the sequence and it increment it in each application start.

FE.

@RunWith(SpringRunner.class)
@ActiveProfiles(profiles = "local")
@SpringBootTest
@Slf4j
@Transactional
public class MultimediaMapperTest {

    @Autowired
    private MultimediaMapper multimediaMapper;

    @Autowired
    private IEmpresasService empresasService;



    @Test
    public void insertArchivo() {
        Multimedia archivo = Multimedia.builder()
                .tiposEntidad(TiposEntidad.EMP)
                .idEntidad(1)
                .awsKey("KEY_AMAZON_WEB_SERVICES")
                .nomFichero("fichero.txt")
                .mime("application/pdf")
                .observaciones("prueba observaciones")
                .build();

        multimediaMapper.insertArchivos(archivo);
        log.debug("Fin de la inserción de {}", archivo);
        assertThat(archivo.getIdArchivo(), is(notNullValue()));

    }

Log:

First start:


[INFO ] 2018-04-23 21:44:16,369 [] o.s.c.s.DefaultLifecycleProcessor - Starting beans in phase 2147483647
[INFO ] 2018-04-23 21:44:16,369 [] s.d.s.w.p.DocumentationPluginsBootstrapper - Context refreshed
[INFO ] 2018-04-23 21:44:16,396 [] s.d.s.w.p.DocumentationPluginsBootstrapper - Found 1 custom documentation plugin(s)
[INFO ] 2018-04-23 21:44:16,470 [] s.d.s.w.s.ApiListingReferenceScanner - Scanning for api listing references
[INFO ] 2018-04-23 21:44:16,880 [] e.g.m.a.d.MultimediaMapperTest - Started MultimediaMapperTest in 7.035 seconds (JVM running for 8.255)
[INFO ] 2018-04-23 21:44:18,600 [] o.s.t.c.t.TransactionContext - Began transaction (1) for test context [DefaultTestContext@40d10264 testClass = MultimediaMapperTest, testInstance = es.gogroup.module.archivos.dao.MultimediaMapperTest@6edd4fe2, testMethod = insertArchivo@MultimediaMapperTest, testException = [null], mergedContextConfiguration = [WebMergedContextConfiguration@2773504f testClass = MultimediaMapperTest, locations = '{}', classes = '{class es.gogroup.PanchoApplication}', contextInitializerClasses = '[]', activeProfiles = '{local}', propertySourceLocations = '{}', propertySourceProperties = '{org.springframework.boot.test.context.SpringBootTestContextBootstrapper=true}', contextCustomizers = set[org.springframework.boot.test.context.SpringBootTestContextCustomizer@2a798d51, org.springframework.boot.test.context.filter.ExcludeFilterContextCustomizer@37afeb11, org.springframework.boot.test.json.DuplicateJsonObjectContextCustomizerFactory$DuplicateJsonObjectContextCustomizer@245b4bdc, org.springframework.boot.test.mock.mockito.MockitoContextCustomizer@0, org.springframework.boot.test.autoconfigure.properties.PropertyMappingContextCustomizer@0, org.springframework.boot.test.autoconfigure.web.servlet.WebDriverContextCustomizerFactory$Customizer@1b7cc17c], resourceBasePath = 'src/main/webapp', contextLoader = 'org.springframework.boot.test.context.SpringBootContextLoader', parent = [null]]]; transaction manager [org.springframework.jdbc.datasource.DataSourceTransactionManager@cfb94fd]; rollback [true]
[DEBUG] 2018-04-23 21:44:18,662 [] e.g.m.m.d.M.insertArchivos!selectKey - ==>  Preparing: SELECT pg_catalog.nextval('"SEQ_MULTIMEDIA"'); 
[DEBUG] 2018-04-23 21:44:18,689 [] e.g.m.m.d.M.insertArchivos!selectKey - ==> Parameters: 
[DEBUG] 2018-04-23 21:44:18,759 [] e.g.m.m.d.M.insertArchivos!selectKey -   Preparing: INSERT INTO MULTIMEDIA(ID_ARCHIVO,TIPO_ENTIDAD, ID_ENTIDAD,AWS_KEY, NOM_FICHERO, OBSERVACIONES, MIME) VALUES (?,?,?,?, ?, ?, ?) 
[DEBUG] 2018-04-23 21:44:18,767 [] e.g.m.m.d.M.insertArchivos - ==> Parameters: 15(Integer), EMP(String), 1(Integer), KEY_AMAZON_WEB_SERVICES(String), fichero.txt(String), prueba observaciones(String), application/pdf(String)
[DEBUG] 2018-04-23 21:44:18,803 [] e.g.m.m.d.M.insertArchivos -

Next start:


[INFO ] 2018-04-23 21:58:15,778 [] o.s.t.c.t.TransactionContext - Began transaction (1) for test context [DefaultTestContext@40d10264 testClass = MultimediaMapperTest, testInstance = es.gogroup.module.archivos.dao.MultimediaMapperTest@6edd4fe2, testMethod = insertArchivo@MultimediaMapperTest, testException = [null], mergedContextConfiguration = [WebMergedContextConfiguration@2773504f testClass = MultimediaMapperTest, locations = '{}', classes = '{class es.gogroup.PanchoApplication}', contextInitializerClasses = '[]', activeProfiles = '{local}', propertySourceLocations = '{}', propertySourceProperties = '{org.springframework.boot.test.context.SpringBootTestContextBootstrapper=true}', contextCustomizers = set[org.springframework.boot.test.context.SpringBootTestContextCustomizer@2a798d51, org.springframework.boot.test.context.filter.ExcludeFilterContextCustomizer@37afeb11, org.springframework.boot.test.json.DuplicateJsonObjectContextCustomizerFactory$DuplicateJsonObjectContextCustomizer@245b4bdc, org.springframework.boot.test.mock.mockito.MockitoContextCustomizer@0, org.springframework.boot.test.autoconfigure.properties.PropertyMappingContextCustomizer@0, org.springframework.boot.test.autoconfigure.web.servlet.WebDriverContextCustomizerFactory$Customizer@1b7cc17c], resourceBasePath = 'src/main/webapp', contextLoader = 'org.springframework.boot.test.context.SpringBootContextLoader', parent = [null]]]; transaction manager [org.springframework.jdbc.datasource.DataSourceTransactionManager@cfb94fd]; rollback [true]
[DEBUG] 2018-04-23 21:58:15,856 [] e.g.m.m.d.M.insertArchivos!selectKey - ==>  Preparing: SELECT pg_catalog.nextval('"SEQ_MULTIMEDIA"'); 
[DEBUG] 2018-04-23 21:58:15,888 [] e.g.m.m.d.M.insertArchivos!selectKey - ==> Parameters: 
[DEBUG] 2018-04-23 21:58:15,954 [] e.g.m.m.d.M.insertArchivos!selectKey -   Preparing: INSERT INTO MULTIMEDIA(ID_ARCHIVO,TIPO_ENTIDAD, ID_ENTIDAD,AWS_KEY, NOM_FICHERO, OBSERVACIONES, MIME) VALUES (?,?,?,?, ?, ?, ?) 
[DEBUG] 2018-04-23 21:58:15,964 [] e.g.m.m.d.M.insertArchivos - ==> Parameters: 16(Integer), EMP(String), 1(Integer), KEY_AMAZON_WEB_SERVICES(String), fichero.txt(String), prueba observaciones(String), application/pdf(String)
[DEBUG] 2018-04-23 21:58:16,001 [] e.g.m.m.d.M.insertArchivos -

Have you tried to simulate this scenario using your favorite SQL client? — Robert Hume

Krzysztof Kaszkowiak Krzysztof Kaszkowiak · Accepted Answer · 2018-04-23T20:05:23

Database sequences cannot be rolled back. That's a feature, not a bug. :)

PostgreSQL:

Important: To avoid blocking concurrent transactions that obtain numbers from the same sequence, a nextval operation is never rolled back; that is, once a value has been fetched it is considered used, even if the transaction that did the nextval later aborts. This means that aborted transactions might leave unused "holes" in the sequence of assigned values.

Oracle:

Once a sequence number is generated, the sequence number is available only to the session that generated the number. Independent of transactions committing or rolling back, other users referencing order_seq.NEXTVAL obtain unique values. If two users are accessing the same sequence concurrently, then the sequence numbers each user receives might have gaps because sequence numbers are also being generated by the other user.

SQL Server:

Sequence numbers are generated outside the scope of the current transaction. They are consumed whether the transaction using the sequence number is committed or rolled back.

Mybatis doesn't Roll Back Sequences

1 Answers