米巴蒂斯不回滚序列

Question

我有一个问题，创建测试使用Spring Boot我的巴蒂斯Spring Boot我已经为我的地图创建了一对夫妇。对于插入，我使用< code>@SelectKey注释来获取序列的下一个值。当Spring回滚时，事务不会回滚序列，而是在每次应用程序启动时递增序列。

铁。

@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()));

    }

日志:

第一次开始：

[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 - 

下次开始:

[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 - 

Answer 1

无法回滚数据库序列。这是一个功能，而不是一个bug。：）

PostgreSQL：

重要提示：为避免阻塞从同一序列获取数字的并发事务，nextval 操作永远不会回滚;也就是说，一旦一个值被获取，它就被认为是被使用的，即使后来执行下一个值的事务中止了。这意味着中止的事务可能会在分配值的顺序中留下未使用的“漏洞”。

甲骨文：

生成序列号后，序列号仅对生成该序列号的会话可用。独立于事务提交或回滚，其他用户引用order_seq。NEXTVAL获取唯一值。如果两个用户同时访问同一序列，那么每个用户收到的序列号可能会有间隙，因为序列号也是由另一个用户生成的。

SQL Server:

序列号是在当前事务的范围之外生成的。无论使用序列号的事务是提交还是回滚，它们都会被消耗。