[cas-user] Re: Deadlocks and Uncommited Transaction
Thanks a lot Tim. We came across this in our load test and we were missing indexes for foreign keys in ticketregistryticket and serviceticket table. Adding this has solved issue in load test but we are seeing same in production and deadlocks with indexes on. We have cleaner running on one of the servers. Did you include these two parameters? Do you know what these parameters do? 1 2 # cas.ticket.registry.jpa.ticketLockType=NONE # cas.ticket.registry.jpa.jpaLockingTimeout=3600 Any help is greatly appreciated. On Wednesday, July 31, 2019 at 3:28:18 AM UTC-4, Tim Evdokimov wrote: > > We have faced the very same issue (deadlocks on delete from TGT table) > during our load tests, and resorted to re-write the ticket registry using > pure JDBC (with Spring JdbcTemplate), taking Cassandra ticket registry as a > reference/example. > > It yields more than 2x lower overall request latency, and there are no > deadlocks observed, either. > Apparently because it doesn't use the default Java serialisation, as the > JPA ticket registry does. > > And, last but not least, it is encrypted at rest. > (surprisingly enough, current JPA implementation does accept crypto > parameters (and complains about the absence of encryption/signing keys in > config), but does not do any encryption) > > Unfortunately, we can't yet submit our JDBC ticket registry code back to > CAS open source, because we need a formal approval. > But it's like 2-3 days of work, not more than that, so you could try it > yourself. > > Kind regards, > Tim > > > On Saturday, October 6, 2018 at 1:50:40 AM UTC+2, Trevor Fong wrote: >> >> Hi There, >> >> We've trying out CAS 5.2.4 in a clustered environment with the ticket >> registry in an Oracle 12c database. We've been seeing tons of persistent >> deadlock errors after a load test - you kill one locker and another >> deadlock springs up. >> Our DBA tells us that deadlocks were seen against the tables LOCKS, >> SERVICETICKET, TICKETGRANTINGTICKET >> Checking the catalina.out log, tons of messages like this: >> >> 2018-10-04 22:45:06,347 WARN >> [org.hibernate.engine.jdbc.spi.SqlExceptionHelper] - > SQLState: 61000> >> 2018-10-04 22:45:06,347 ERROR >> [org.hibernate.engine.jdbc.spi.SqlExceptionHelper] - > detected while waiting for resource >> > >> 2018-10-04 22:45:06,347 ERROR >> [org.apereo.cas.ticket.registry.DefaultTicketRegistryCleaner] - >> > statement> >> javax.persistence.PersistenceException: >> org.hibernate.exception.LockAcquisitionException: could not execute >> statement >> at >> org.hibernate.internal.ExceptionConverterImpl.convert(ExceptionConverterImpl.java:149) >> >> ~[hibernate-core-5.2.13.Final.jar:5.2.13.Final] >> at >> org.hibernate.internal.ExceptionConverterImpl.convert(ExceptionConverterImpl.java:157) >> >> ~[hibernate-core-5.2.13.Final.jar:5.2.13.Final] >> at >> org.hibernate.query.internal.AbstractProducedQuery.executeUpdate(AbstractProducedQuery.java:1514) >> >> ~[hibernate-core-5.2.13.Final.jar:5.2.13.Final] >> at >> org.apereo.cas.ticket.registry.JpaTicketRegistry.deleteTicketGrantingTickets(JpaTicketRegistry.java:177) >> >> ~[cas-server-support-jpa-ticket-registry-5.2.4.jar:5.2.4] >> at >> org.apereo.cas.ticket.registry.JpaTicketRegistry.deleteSingleTicket(JpaTicketRegistry.java:145) >> >> ~[cas-server-support-jpa-ticket-registry-5.2.4.jar:5.2.4] >> at >> org.apereo.cas.ticket.registry.AbstractTicketRegistry.deleteTicket(AbstractTicketRegistry.java:126) >> >> ~[cas-server-core-tickets-5.2.4.jar:5.2.4] >> at >> org.apereo.cas.ticket.registry.AbstractTicketRegistry$$FastClassBySpringCGLIB$$d3c67a11.invoke() >> >> ~[cas-server-core-tickets-5.2.4.jar:5.2.4] >> at >> org.springframework.cglib.proxy.MethodProxy.invoke(MethodProxy.java:204) >> ~[spring-core-4.3.16.RELEASE.jar:4.3.16.RELEASE] >> at >> org.springframework.aop.framework.CglibAopProxy$DynamicAdvisedInterceptor.intercept(CglibAopProxy.java:669) >> >> ~[spring-aop-4.3.16.RELEASE.jar:4.3.16.RELEASE] >> at >> org.apereo.cas.ticket.registry.JpaTicketRegistry$$EnhancerBySpringCGLIB$$45967896.deleteTicket() >> >> ~[cas-server-support-jpa-ticket-registry-5.2.4.jar:5.2.4] >> at sun.reflect.GeneratedMethodAccessor351.invoke(Unknown Source) >> ~[?:?] >> at >> sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) >> >> ~[?:1.8.0_172] >> at java.lang.reflect.Method.invoke(Method.java:498) ~[?:1.8.0_172] >> at >> org.springframework.aop.support.AopUtils.invokeJoinpointUsingReflection(AopUtils.java:333) >> >> ~[spring-aop-4.3.16.RELEASE.jar:4.3.16.RELEASE] >> at >> org.springframework.aop.framework.ReflectiveMethodInvocation.invokeJoinpoint(ReflectiveMethodInvocation.java:190) >> >> ~[spring-aop-4.3.16.RELEASE.jar:4.3.16.RELEASE] >> at >>
[cas-user] Re: Deadlocks and Uncommited Transaction
We have faced the very same issue (deadlocks on delete from TGT table) during our load tests, and resorted to re-write the ticket registry using pure JDBC (with Spring JdbcTemplate), taking Cassandra ticket registry as a reference/example. It yields more than 2x lower overall request latency, and there are no deadlocks observed, either. Apparently because it doesn't use the default Java serialisation, as the JPA ticket registry does. And, last but not least, it is encrypted at rest. (surprisingly enough, current JPA implementation does accept crypto parameters (and complains about the absence of encryption/signing keys in config), but does not do any encryption) Unfortunately, we can't yet submit our JDBC ticket registry code back to CAS open source, because we need a formal approval. But it's like 2-3 days of work, not more than that, so you could try it yourself. Kind regards, Tim On Saturday, October 6, 2018 at 1:50:40 AM UTC+2, Trevor Fong wrote: > > Hi There, > > We've trying out CAS 5.2.4 in a clustered environment with the ticket > registry in an Oracle 12c database. We've been seeing tons of persistent > deadlock errors after a load test - you kill one locker and another > deadlock springs up. > Our DBA tells us that deadlocks were seen against the tables LOCKS, > SERVICETICKET, TICKETGRANTINGTICKET > Checking the catalina.out log, tons of messages like this: > > 2018-10-04 22:45:06,347 WARN > [org.hibernate.engine.jdbc.spi.SqlExceptionHelper] - SQLState: 61000> > 2018-10-04 22:45:06,347 ERROR > [org.hibernate.engine.jdbc.spi.SqlExceptionHelper] - detected while waiting for resource > > > 2018-10-04 22:45:06,347 ERROR > [org.apereo.cas.ticket.registry.DefaultTicketRegistryCleaner] - > statement> > javax.persistence.PersistenceException: > org.hibernate.exception.LockAcquisitionException: could not execute > statement > at > org.hibernate.internal.ExceptionConverterImpl.convert(ExceptionConverterImpl.java:149) > > ~[hibernate-core-5.2.13.Final.jar:5.2.13.Final] > at > org.hibernate.internal.ExceptionConverterImpl.convert(ExceptionConverterImpl.java:157) > > ~[hibernate-core-5.2.13.Final.jar:5.2.13.Final] > at > org.hibernate.query.internal.AbstractProducedQuery.executeUpdate(AbstractProducedQuery.java:1514) > > ~[hibernate-core-5.2.13.Final.jar:5.2.13.Final] > at > org.apereo.cas.ticket.registry.JpaTicketRegistry.deleteTicketGrantingTickets(JpaTicketRegistry.java:177) > > ~[cas-server-support-jpa-ticket-registry-5.2.4.jar:5.2.4] > at > org.apereo.cas.ticket.registry.JpaTicketRegistry.deleteSingleTicket(JpaTicketRegistry.java:145) > > ~[cas-server-support-jpa-ticket-registry-5.2.4.jar:5.2.4] > at > org.apereo.cas.ticket.registry.AbstractTicketRegistry.deleteTicket(AbstractTicketRegistry.java:126) > > ~[cas-server-core-tickets-5.2.4.jar:5.2.4] > at > org.apereo.cas.ticket.registry.AbstractTicketRegistry$$FastClassBySpringCGLIB$$d3c67a11.invoke() > > ~[cas-server-core-tickets-5.2.4.jar:5.2.4] > at > org.springframework.cglib.proxy.MethodProxy.invoke(MethodProxy.java:204) > ~[spring-core-4.3.16.RELEASE.jar:4.3.16.RELEASE] > at > org.springframework.aop.framework.CglibAopProxy$DynamicAdvisedInterceptor.intercept(CglibAopProxy.java:669) > > ~[spring-aop-4.3.16.RELEASE.jar:4.3.16.RELEASE] > at > org.apereo.cas.ticket.registry.JpaTicketRegistry$$EnhancerBySpringCGLIB$$45967896.deleteTicket() > > ~[cas-server-support-jpa-ticket-registry-5.2.4.jar:5.2.4] > at sun.reflect.GeneratedMethodAccessor351.invoke(Unknown Source) > ~[?:?] > at > sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) > > ~[?:1.8.0_172] > at java.lang.reflect.Method.invoke(Method.java:498) ~[?:1.8.0_172] > at > org.springframework.aop.support.AopUtils.invokeJoinpointUsingReflection(AopUtils.java:333) > > ~[spring-aop-4.3.16.RELEASE.jar:4.3.16.RELEASE] > at > org.springframework.aop.framework.ReflectiveMethodInvocation.invokeJoinpoint(ReflectiveMethodInvocation.java:190) > > ~[spring-aop-4.3.16.RELEASE.jar:4.3.16.RELEASE] > at > org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:157) > > ~[spring-aop-4.3.16.RELEASE.jar:4.3.16.RELEASE] > at > org.springframework.aop.support.DelegatingIntroductionInterceptor.doProceed(DelegatingIntroductionInterceptor.java:133) > > ~[spring-aop-4.3.16.RELEASE.jar:4.3.16.RELEASE] > at > org.springframework.aop.support.DelegatingIntroductionInterceptor.invoke(DelegatingIntroductionInterceptor.java:121) > > ~[spring-aop-4.3.16.RELEASE.jar:4.3.16.RELEASE] > at > org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179) > > ~[spring-aop-4.3.16.RELEASE.jar:4.3.16.RELEASE] > at >
[cas-user] Re: Deadlocks and Uncommited Transaction
Hi, We are facing same issue. Were you able to resolve the problem. Any pointers of how you resolved it will help me. Thank you On Monday, October 8, 2018 at 11:57:53 AM UTC-4, Trevor Fong wrote: > > > > Anyone have any ideas why we're getting deadlocks against the tables > LOCKS, SERVICETICKET, TICKETGRANTINGTICKET? > > Thanks a lot > Trev > > > On Friday, October 5, 2018 at 4:50:40 PM UTC-7, Trevor Fong wrote: > > Hi There, > > > > > > We've trying out CAS 5.2.4 in a clustered environment with the ticket > registry in an Oracle 12c database. We've been seeing tons of persistent > deadlock errors after a load test - you kill one locker and another > deadlock springs up. > > Our DBA tells us that deadlocks were seen against the tables LOCKS, > SERVICETICKET, TICKETGRANTINGTICKET > > Checking the catalina.out log, tons of messages like this: > > > > > > > > 2018-10-04 22:45:06,347 WARN > [org.hibernate.engine.jdbc.spi.SqlExceptionHelper] - SQLState: 61000> > > 2018-10-04 22:45:06,347 ERROR > [org.hibernate.engine.jdbc.spi.SqlExceptionHelper] - detected while waiting for resource > > > > > 2018-10-04 22:45:06,347 ERROR > [org.apereo.cas.ticket.registry.DefaultTicketRegistryCleaner] - > statement> > > javax.persistence.PersistenceException: > org.hibernate.exception.LockAcquisitionException: could not execute > statement > > at > org.hibernate.internal.ExceptionConverterImpl.convert(ExceptionConverterImpl.java:149) > > ~[hibernate-core-5.2.13.Final.jar:5.2.13.Final] > > at > org.hibernate.internal.ExceptionConverterImpl.convert(ExceptionConverterImpl.java:157) > > ~[hibernate-core-5.2.13.Final.jar:5.2.13.Final] > > at > org.hibernate.query.internal.AbstractProducedQuery.executeUpdate(AbstractProducedQuery.java:1514) > > ~[hibernate-core-5.2.13.Final.jar:5.2.13.Final] > > at > org.apereo.cas.ticket.registry.JpaTicketRegistry.deleteTicketGrantingTickets(JpaTicketRegistry.java:177) > > ~[cas-server-support-jpa-ticket-registry-5.2.4.jar:5.2.4] > > at > org.apereo.cas.ticket.registry.JpaTicketRegistry.deleteSingleTicket(JpaTicketRegistry.java:145) > > ~[cas-server-support-jpa-ticket-registry-5.2.4.jar:5.2.4] > > at > org.apereo.cas.ticket.registry.AbstractTicketRegistry.deleteTicket(AbstractTicketRegistry.java:126) > > ~[cas-server-core-tickets-5.2.4.jar:5.2.4] > > at > org.apereo.cas.ticket.registry.AbstractTicketRegistry$$FastClassBySpringCGLIB$$d3c67a11.invoke() > > ~[cas-server-core-tickets-5.2.4.jar:5.2.4] > > at > org.springframework.cglib.proxy.MethodProxy.invoke(MethodProxy.java:204) > ~[spring-core-4.3.16.RELEASE.jar:4.3.16.RELEASE] > > at > org.springframework.aop.framework.CglibAopProxy$DynamicAdvisedInterceptor.intercept(CglibAopProxy.java:669) > > ~[spring-aop-4.3.16.RELEASE.jar:4.3.16.RELEASE] > > at > org.apereo.cas.ticket.registry.JpaTicketRegistry$$EnhancerBySpringCGLIB$$45967896.deleteTicket() > > ~[cas-server-support-jpa-ticket-registry-5.2.4.jar:5.2.4] > > at sun.reflect.GeneratedMethodAccessor351.invoke(Unknown Source) > ~[?:?] > > at > sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) > > ~[?:1.8.0_172] > > at java.lang.reflect.Method.invoke(Method.java:498) > ~[?:1.8.0_172] > > at > org.springframework.aop.support.AopUtils.invokeJoinpointUsingReflection(AopUtils.java:333) > > ~[spring-aop-4.3.16.RELEASE.jar:4.3.16.RELEASE] > > at > org.springframework.aop.framework.ReflectiveMethodInvocation.invokeJoinpoint(ReflectiveMethodInvocation.java:190) > > ~[spring-aop-4.3.16.RELEASE.jar:4.3.16.RELEASE] > > at > org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:157) > > ~[spring-aop-4.3.16.RELEASE.jar:4.3.16.RELEASE] > > at > org.springframework.aop.support.DelegatingIntroductionInterceptor.doProceed(DelegatingIntroductionInterceptor.java:133) > > ~[spring-aop-4.3.16.RELEASE.jar:4.3.16.RELEASE] > > at > org.springframework.aop.support.DelegatingIntroductionInterceptor.invoke(DelegatingIntroductionInterceptor.java:121) > > ~[spring-aop-4.3.16.RELEASE.jar:4.3.16.RELEASE] > > at > org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179) > > ~[spring-aop-4.3.16.RELEASE.jar:4.3.16.RELEASE] > > at > org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:213) > > ~[spring-aop-4.3.16.RELEASE.jar:4.3.16.RELEASE] > > at com.sun.proxy.$Proxy104.deleteTicket(Unknown Source) ~[?:?] > > at > org.apereo.cas.ticket.registry.DefaultTicketRegistryCleaner.cleanTicket(DefaultTicketRegistryCleaner.java:78) > > ~[cas-server-core-tickets-5.2.4.jar:5.2.4] > > at > java.util.stream.ReferencePipeline$4$1.accept(ReferencePipeline.java:210) > ~[?:1.8.0_172] > >
[cas-user] Re: Deadlocks and Uncommited Transaction
Anyone have any ideas why we're getting deadlocks against the tables LOCKS, SERVICETICKET, TICKETGRANTINGTICKET? Thanks a lot Trev On Friday, October 5, 2018 at 4:50:40 PM UTC-7, Trevor Fong wrote: > Hi There, > > > We've trying out CAS 5.2.4 in a clustered environment with the ticket > registry in an Oracle 12c database. We've been seeing tons of persistent > deadlock errors after a load test - you kill one locker and another deadlock > springs up. > Our DBA tells us that deadlocks were seen against the tables LOCKS, > SERVICETICKET, TICKETGRANTINGTICKET > Checking the catalina.out log, tons of messages like this: > > > > 2018-10-04 22:45:06,347 WARN > [org.hibernate.engine.jdbc.spi.SqlExceptionHelper] - SQLState: 61000> > 2018-10-04 22:45:06,347 ERROR > [org.hibernate.engine.jdbc.spi.SqlExceptionHelper] - detected while waiting for resource > > > 2018-10-04 22:45:06,347 ERROR > [org.apereo.cas.ticket.registry.DefaultTicketRegistryCleaner] - > statement> > javax.persistence.PersistenceException: > org.hibernate.exception.LockAcquisitionException: could not execute statement > at > org.hibernate.internal.ExceptionConverterImpl.convert(ExceptionConverterImpl.java:149) > ~[hibernate-core-5.2.13.Final.jar:5.2.13.Final] > at > org.hibernate.internal.ExceptionConverterImpl.convert(ExceptionConverterImpl.java:157) > ~[hibernate-core-5.2.13.Final.jar:5.2.13.Final] > at > org.hibernate.query.internal.AbstractProducedQuery.executeUpdate(AbstractProducedQuery.java:1514) > ~[hibernate-core-5.2.13.Final.jar:5.2.13.Final] > at > org.apereo.cas.ticket.registry.JpaTicketRegistry.deleteTicketGrantingTickets(JpaTicketRegistry.java:177) > ~[cas-server-support-jpa-ticket-registry-5.2.4.jar:5.2.4] > at > org.apereo.cas.ticket.registry.JpaTicketRegistry.deleteSingleTicket(JpaTicketRegistry.java:145) > ~[cas-server-support-jpa-ticket-registry-5.2.4.jar:5.2.4] > at > org.apereo.cas.ticket.registry.AbstractTicketRegistry.deleteTicket(AbstractTicketRegistry.java:126) > ~[cas-server-core-tickets-5.2.4.jar:5.2.4] > at > org.apereo.cas.ticket.registry.AbstractTicketRegistry$$FastClassBySpringCGLIB$$d3c67a11.invoke() > ~[cas-server-core-tickets-5.2.4.jar:5.2.4] > at > org.springframework.cglib.proxy.MethodProxy.invoke(MethodProxy.java:204) > ~[spring-core-4.3.16.RELEASE.jar:4.3.16.RELEASE] > at > org.springframework.aop.framework.CglibAopProxy$DynamicAdvisedInterceptor.intercept(CglibAopProxy.java:669) > ~[spring-aop-4.3.16.RELEASE.jar:4.3.16.RELEASE] > at > org.apereo.cas.ticket.registry.JpaTicketRegistry$$EnhancerBySpringCGLIB$$45967896.deleteTicket() > ~[cas-server-support-jpa-ticket-registry-5.2.4.jar:5.2.4] > at sun.reflect.GeneratedMethodAccessor351.invoke(Unknown Source) > ~[?:?] > at > sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) > ~[?:1.8.0_172] > at java.lang.reflect.Method.invoke(Method.java:498) ~[?:1.8.0_172] > at > org.springframework.aop.support.AopUtils.invokeJoinpointUsingReflection(AopUtils.java:333) > ~[spring-aop-4.3.16.RELEASE.jar:4.3.16.RELEASE] > at > org.springframework.aop.framework.ReflectiveMethodInvocation.invokeJoinpoint(ReflectiveMethodInvocation.java:190) > ~[spring-aop-4.3.16.RELEASE.jar:4.3.16.RELEASE] > at > org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:157) > ~[spring-aop-4.3.16.RELEASE.jar:4.3.16.RELEASE] > at > org.springframework.aop.support.DelegatingIntroductionInterceptor.doProceed(DelegatingIntroductionInterceptor.java:133) > ~[spring-aop-4.3.16.RELEASE.jar:4.3.16.RELEASE] > at > org.springframework.aop.support.DelegatingIntroductionInterceptor.invoke(DelegatingIntroductionInterceptor.java:121) > ~[spring-aop-4.3.16.RELEASE.jar:4.3.16.RELEASE] > at > org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179) > ~[spring-aop-4.3.16.RELEASE.jar:4.3.16.RELEASE] > at > org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:213) > ~[spring-aop-4.3.16.RELEASE.jar:4.3.16.RELEASE] > at com.sun.proxy.$Proxy104.deleteTicket(Unknown Source) ~[?:?] > at > org.apereo.cas.ticket.registry.DefaultTicketRegistryCleaner.cleanTicket(DefaultTicketRegistryCleaner.java:78) > ~[cas-server-core-tickets-5.2.4.jar:5.2.4] > at > java.util.stream.ReferencePipeline$4$1.accept(ReferencePipeline.java:210) > ~[?:1.8.0_172] > at > java.util.stream.ReferencePipeline$2$1.accept(ReferencePipeline.java:175) > ~[?:1.8.0_172] > at java.util.Iterator.forEachRemaining(Iterator.java:116) > ~[?:1.8.0_172] > at > java.util.Spliterators$IteratorSpliterator.forEachRemaining(Spliterators.java:1801) > ~[?:1.8.0_172] > at >
