Sustituir el @TableGenerator de INSIDE

[ibarrancos]

Se deben sustituir todas las ocurrencias del estilo:

  @Id
  // @GeneratedValue(strategy = IDENTITY)
  @TableGenerator(name = "GeneradorPk_ExpedienteInsideIndiceFirmas", table = "GeneradorClaves",
      pkColumnName = "GenName", valueColumnName = "GenValue",
      pkColumnValue = "GEN_ExpedienteInsideIndiceFirmas", allocationSize = 1)
  @GeneratedValue(strategy = GenerationType.TABLE,
      generator = "GeneradorPk_ExpedienteInsideIndiceFirmas")
  @Column(name = "id", unique = true, nullable = false)
  public Integer getId() {
    return this.id;
  }

Por este otro tipo de construcciones:

  @Id
  @GenericGenerator( name = "GeneradorPk_ExpedienteInsideIndiceFirmas", 
      strategy = "es.carm.InsideGeneratorID", 
      parameters = @Parameter(name = "sequence", value = "GEN_ExpedienteInsideIndiceFirmas") )
  @GeneratedValue(generator = "GeneradorPk_ExpedienteInsideIndiceFirmas")
  @Column(name = "id", unique = true, nullable = false)
  public Integer getId() {
    return this.id;
  }

Esto hay que hacerlo porque se han detectado deadlocks al usar org.hibernate.id.enhanced.TableGenerator:

"ajp-nio-8040-exec-106" #543 daemon prio=5 os_prio=0 tid=0x00007f537c02a800 nid=0x445d waiting for monitor entry [0x00007f531fbb8000]
   java.lang.Thread.State: BLOCKED (on object monitor)
    at org.hibernate.id.enhanced.TableGenerator.generate(TableGenerator.java:464)
    - waiting to lock <0x000000068367d5e0> (a org.hibernate.id.enhanced.TableGenerator)
    at org.hibernate.event.def.AbstractSaveEventListener.saveWithGeneratedId(AbstractSaveEventListener.java:122)
    at org.hibernate.event.def.DefaultSaveOrUpdateEventListener.saveWithGeneratedOrRequestedId(DefaultSaveOrUpdateEventListener.java:210)
    at org.hibernate.event.def.DefaultSaveOrUpdateEventListener.entityIsTransient(DefaultSaveOrUpdateEventListener.java:195)
    at org.hibernate.event.def.DefaultSaveOrUpdateEventListener.performSaveOrUpdate(DefaultSaveOrUpdateEventListener.java:117)
    at org.hibernate.event.def.DefaultSaveOrUpdateEventListener.onSaveOrUpdate(DefaultSaveOrUpdateEventListener.java:93)
    at org.hibernate.impl.SessionImpl.fireSaveOrUpdate(SessionImpl.java:677)
    at org.hibernate.impl.SessionImpl.saveOrUpdate(SessionImpl.java:669)
    at org.hibernate.impl.SessionImpl.saveOrUpdate(SessionImpl.java:665)
    at es.mpt.dsic.inside.service.store.impl.hibernate.InsideServiceStoreHibernatePersister.saveInsideMetadatosAdicionales(InsideServiceStoreHibernatePersister.java:229)
    at es.mpt.dsic.inside.service.store.impl.hibernate.InsideServiceStoreHibernatePersister.saveDocumento(InsideServiceStoreHibernatePersister.java:143)
    at es.mpt.dsic.inside.service.store.impl.hibernate.InsideServiceStoreHibernatePersister.persistExpedienteOrDocumento(InsideServiceStoreHibernatePersister.java:105)
    at es.mpt.dsic.inside.service.store.impl.hibernate.InsideServiceStoreHibernateImpl.storeObject(InsideServiceStoreHibernateImpl.java:176)
    at es.mpt.dsic.inside.service.impl.InSideServiceImpl.altaDocumento(InSideServiceImpl.java:643)
    at es.mpt.dsic.inside.ws.operation.impl.InsideOperationWebServiceImpl.altaDocumento(InsideOperationWebServiceImpl.java:1316)
    at es.mpt.dsic.inside.ws.operation.impl.InsideOperationWebServiceImpl.altaDocumentoOtros(InsideOperationWebServiceImpl.java:935)
    at es.mpt.dsic.inside.ws.operation.impl.InsideOperationWebServiceImpl$$FastClassByCGLIB$$3a9b45ad.invoke(<generated>)
    at net.sf.cglib.proxy.MethodProxy.invoke(MethodProxy.java:204)
    at org.springframework.aop.framework.Cglib2AopProxy$CglibMethodInvocation.invokeJoinpoint(Cglib2AopProxy.java:689)
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:150)
    at org.springframework.security.access.intercept.aopalliance.MethodSecurityInterceptor.invoke(MethodSecurityInterceptor.java:61)
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:172)
    at org.springframework.aop.framework.Cglib2AopProxy$DynamicAdvisedInterceptor.intercept(Cglib2AopProxy.java:622)
    at es.mpt.dsic.inside.ws.operation.impl.InsideOperationWebServiceImpl$$EnhancerByCGLIB$$5b8a268.altaDocumentoOtros(<generated>)
    at es.mpt.dsic.inside.ws.service.impl.InsideUserTokenWebServiceImpl.altaDocumentoOtros(InsideUserTokenWebServiceImpl.java:380)
    at es.mpt.dsic.inside.ws.service.impl.InsideUserTokenWebServiceImpl$$FastClassByCGLIB$$a0e77ae6.invoke(<generated>)
    at net.sf.cglib.proxy.MethodProxy.invoke(MethodProxy.java:204)
    at org.springframework.aop.framework.Cglib2AopProxy$CglibMethodInvocation.invokeJoinpoint(Cglib2AopProxy.java:689)
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:150)
    at org.springframework.security.access.intercept.aopalliance.MethodSecurityInterceptor.invoke(MethodSecurityInterceptor.java:61)
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:172)
    at org.springframework.aop.framework.Cglib2AopProxy$DynamicAdvisedInterceptor.intercept(Cglib2AopProxy.java:622)
    at es.mpt.dsic.inside.ws.service.impl.InsideUserTokenWebServiceImpl$$EnhancerByCGLIB$$676117cb.altaDocumentoOtros(<generated>)
    at sun.reflect.GeneratedMethodAccessor257.invoke(Unknown Source)
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
    at java.lang.reflect.Method.invoke(Method.java:498)
    at org.apache.cxf.service.invoker.AbstractInvoker.performInvocation(AbstractInvoker.java:188)
    at org.apache.cxf.service.invoker.AbstractInvoker.invoke(AbstractInvoker.java:104)
    at org.apache.cxf.jaxws.AbstractJAXWSMethodInvoker.invoke(AbstractJAXWSMethodInvoker.java:237)
    at org.apache.cxf.jaxws.JAXWSMethodInvoker.invoke(JAXWSMethodInvoker.java:69)
    at org.apache.cxf.service.invoker.AbstractInvoker.invoke(AbstractInvoker.java:75)
    at org.apache.cxf.interceptor.ServiceInvokerInterceptor$1.run(ServiceInvokerInterceptor.java:58)
    at java.util.concurrent.Executors$RunnableAdapter.call(Executors.java:511)
    at java.util.concurrent.FutureTask.run(FutureTask.java:266)
    at org.apache.cxf.workqueue.SynchronousExecutor.execute(SynchronousExecutor.java:37)
    at org.apache.cxf.interceptor.ServiceInvokerInterceptor.handleMessage(ServiceInvokerInterceptor.java:107)
    - locked <0x00000006fa326948> (a org.apache.cxf.interceptor.ServiceInvokerInterceptor$2)
    at org.apache.cxf.phase.PhaseInterceptorChain.doIntercept(PhaseInterceptorChain.java:272)
    - locked <0x00000006fa3269b8> (a org.apache.cxf.phase.PhaseInterceptorChain)
    at org.apache.cxf.transport.ChainInitiationObserver.onMessage(ChainInitiationObserver.java:121)
    at org.apache.cxf.transport.http.AbstractHTTPDestination.invoke(AbstractHTTPDestination.java:249)
    at org.apache.cxf.transport.servlet.ServletController.invokeDestination(ServletController.java:248)
    at org.apache.cxf.transport.servlet.ServletController.invoke(ServletController.java:222)
    at org.apache.cxf.transport.servlet.ServletController.invoke(ServletController.java:153)
    at org.apache.cxf.transport.servlet.CXFNonSpringServlet.invoke(CXFNonSpringServlet.java:181)
    at org.apache.cxf.transport.servlet.AbstractHTTPServlet.handleRequest(AbstractHTTPServlet.java:289)
    at org.apache.cxf.transport.servlet.AbstractHTTPServlet.doPost(AbstractHTTPServlet.java:209)
    at javax.servlet.http.HttpServlet.service(HttpServlet.java:661)
    at org.apache.cxf.transport.servlet.AbstractHTTPServlet.service(AbstractHTTPServlet.java:265)
    at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:231)
    at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166)
    at org.apache.tomcat.websocket.server.WsFilter.doFilter(WsFilter.java:52)
    at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:193)
    at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166)
    at org.springframework.web.filter.CharacterEncodingFilter.doFilterInternal(CharacterEncodingFilter.java:88)
    at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:76)
    at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:193)
    at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166)
    at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:322)
    at org.springframework.security.web.access.intercept.FilterSecurityInterceptor.invoke(FilterSecurityInterceptor.java:116)
    at org.springframework.security.web.access.intercept.FilterSecurityInterceptor.doFilter(FilterSecurityInterceptor.java:83)
    at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:334)
    at org.springframework.security.web.access.ExceptionTranslationFilter.doFilter(ExceptionTranslationFilter.java:113)
    at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:334)
    at org.springframework.security.web.session.SessionManagementFilter.doFilter(SessionManagementFilter.java:103)
    at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:334)
    at org.springframework.security.web.authentication.AnonymousAuthenticationFilter.doFilter(AnonymousAuthenticationFilter.java:113)
    at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:334)
    at org.springframework.security.web.servletapi.SecurityContextHolderAwareRequestFilter.doFilter(SecurityContextHolderAwareRequestFilter.java:54)
    at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:334)
    at org.springframework.security.web.savedrequest.RequestCacheAwareFilter.doFilter(RequestCacheAwareFilter.java:45)
    at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:334)
    at org.springframework.security.web.authentication.www.BasicAuthenticationFilter.doFilter(BasicAuthenticationFilter.java:150)
    at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:334)
    at org.springframework.security.web.authentication.AbstractAuthenticationProcessingFilter.doFilter(AbstractAuthenticationProcessingFilter.java:182)
    at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:334)
    at org.springframework.web.filter.CompositeFilter$VirtualFilterChain.doFilter(CompositeFilter.java:98)
    at org.springframework.security.web.authentication.AbstractAuthenticationProcessingFilter.doFilter(AbstractAuthenticationProcessingFilter.java:182)
    at org.springframework.web.filter.CompositeFilter$VirtualFilterChain.doFilter(CompositeFilter.java:102)
    at org.springframework.security.web.authentication.AbstractAuthenticationProcessingFilter.doFilter(AbstractAuthenticationProcessingFilter.java:182)
    at org.springframework.web.filter.CompositeFilter$VirtualFilterChain.doFilter(CompositeFilter.java:102)
    at org.springframework.web.filter.CompositeFilter.doFilter(CompositeFilter.java:82)
    at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:334)
    at org.springframework.security.web.authentication.logout.LogoutFilter.doFilter(LogoutFilter.java:105)
    at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:334)
    at org.springframework.security.web.context.SecurityContextPersistenceFilter.doFilter(SecurityContextPersistenceFilter.java:87)
    at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:334)
    at org.springframework.security.web.FilterChainProxy.doFilterInternal(FilterChainProxy.java:184)
    at org.springframework.security.web.FilterChainProxy.doFilter(FilterChainProxy.java:155)
    at org.springframework.web.filter.DelegatingFilterProxy.invokeDelegate(DelegatingFilterProxy.java:346)
    at org.springframework.web.filter.DelegatingFilterProxy.doFilter(DelegatingFilterProxy.java:259)
    at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:193)
    at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166)
    at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:198)
    at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:96)
    at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:478)
    at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:140)
    at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:80)
    at org.apache.catalina.valves.AbstractAccessLogValve.invoke(AbstractAccessLogValve.java:650)
    at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:87)
    at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:342)
    at org.apache.coyote.ajp.AjpProcessor.service(AjpProcessor.java:486)
    at org.apache.coyote.AbstractProcessorLight.process(AbstractProcessorLight.java:66)
    at org.apache.coyote.AbstractProtocol$ConnectionHandler.process(AbstractProtocol.java:868)
    at org.apache.tomcat.util.net.NioEndpoint$SocketProcessor.doRun(NioEndpoint.java:1457)
    at org.apache.tomcat.util.net.SocketProcessorBase.run(SocketProcessorBase.java:49)
    - locked <0x00000006fa326e38> (a org.apache.tomcat.util.net.NioEndpoint$NioSocketWrapper)
    at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149)
    at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624)
    at org.apache.tomcat.util.threads.TaskThread$WrappingRunnable.run(TaskThread.java:61)
    at java.lang.Thread.run(Thread.java:748)

En una de las caídas del pasado 28/Noviembre/2022, detectamos en el nodo servapp-pro-sa4 que había 47 threads bloqueados org.hibernate.id.enhanced.TableGenerator.generate(TableGenerator.java:464) que estaban agotando el pool de conexiones (configurado a un máximo de 50). E el momento de generar el dump, había 168 Threads en el espacio de memoria de la JVM, de los cuales 113 se correspondían con peticiones AJP, y de ellas:

• 47 estaban en estado BLOCKED org.hibernate.id.enhanced.TableGenerator.generate(TableGenerator.java:464)
• 63 estaban en estado TIMED_WAITING at org.apache.tomcat.dbcp.dbcp2.PoolingDataSource.getConnection(PoolingDataSource.java:134), esperando conexión del pool (por timeout)
• 3 estaban en estado RUNNABLE y se correspondían con el propio dispatcher de peticiones del tomcat.

En este link https://stackoverflow.com/questions/62293140/what-is-the-difference-between-thread-state-waiting-parking-vs-blocked-at-sun podemos encontrar el significado de cada uno de los estados en los que puede estar un thread.

Parece que el uso de @TableGenerator con MySQL presenta deadlocks como afirman en este BUG de Hibernate Deadlock using sequence ID generator in MySQL, como subraya este otro artículo Why you should never use the TABLE identifier generator with JPA and Hibernate y se queja este otro Multi-Tenant Sequence generator in Hibernate

También hemos examinado la última versión de Inside 3.18.3:

• Tanto la que tenemos desplegada (2.0.8.3), como la última (3.18.3), usan la misma versión de Hibernate: 3.5.6-FINAL
• En las dos versiones, usan el @TableGenerator de la misma manera,
• La nueva versión no trae scripts para Oracle, sólo para MySQL.

Por tanto, actualizar Inside a la última versión para evitar este deadlock o el uso de MySQL no parece que solucionará nuestros problemas.

Así, se opta por modificar el generador de IDs y llevarlo a secuencias de Oracle con transacciones de duración mínima:

• https://glpi.carm.es/front/ticket.form.php?id=1055980
• https://glpi.carm.es/front/ticket.form.php?id=1055993