Skip to content

Session JDBC mysql innodb deadlocks #1027

New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

Sign up for GitHub

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Jump to bottom
Closed
isalnikov opened this issue Mar 21, 2018 · 8 comments
Closed

Session JDBC mysql innodb deadlocks #1027

isalnikov opened this issue Mar 21, 2018 · 8 comments
Assignees
@vpavic
Labels
status: duplicate A duplicate of another issue

Comments

@isalnikov
Copy link

isalnikov commented Mar 21, 2018 •
edited
Loading

private static final String CREATE_SESSION_QUERY =
"INSERT INTO %TABLE_NAME%(PRIMARY_ID, SESSION_ID, CREATION_TIME, LAST_ACCESS_TIME, MAX_INACTIVE_INTERVAL, EXPIRY_TIME, PRINCIPAL_NAME) " +
"VALUES (?, ?, ?, ?, ?, ?, ?)";

private static final String DELETE_SESSIONS_BY_EXPIRY_TIME_QUERY =
"DELETE FROM %TABLE_NAME% " +
"WHERE EXPIRY_TIME < ?";

I found the cause of the deadlock when working with mysql.
When one thread inserts a new record into the table it gets a s lock

the second thread does the deletie of the old sessions and gets the exclusive X lock

so in the mysql engine it is necessary for the key to be monotonously increasing.

CREATE TABLE SPRING_SESSION (

  • PRIMARY_ID CHAR(36) NOT NULL,
  • SESSION_ID CHAR(36) NOT NULL,

https://stackoverflow.com/questions/46106485/mysql-using-a-unique-char-as-primary-key
It is valid to use a UUID as a primary key. It meets the two conditions required of a primary key:
It is unique.
It is never NULL.
However, it is a bad idea. Why? MySQL automatically clusters the data by the primary key. That is, the data is actually sorted by the primary key. UUIDs are not sequential. Inserts can occur anywhere, requiring movement of data.

I would recommend a simple auto incremented primary key and declare the UUID as unique
@vpavic vpavic self-assigned this Apr 17, 2018
@vpavic
Copy link
Contributor

vpavic commented Apr 17, 2018

Thanks for the report @isalnikov. Are you getting these deadlocks running on single instance of your app, or in cluster (perhaps with MySQL in multi-master setup)?

We've had a couple of similar reports - see #676, #838. Multi-master setup is troublesome in highly concurrent environments, and there's little we can do about that. If you're running in a cluster I'd recommend setting different cron expression for each of your nodes, so the session cleaning jobs don't overlap.

Regarding the auto incremented primary key, I'm afraid that's not a viable solution for us as we need to support multiple RDBMS vendors and that means vendor specific features like that aren't suitable.

@vpavic vpavic added for: stack-overflow A question that's better suited to stackoverflow.com status: waiting-for-feedback We need additional information before we can continue labels Apr 17, 2018
@vpavic
Copy link
Contributor

vpavic commented May 2, 2018

Closing due to lack of feedback. Please re-open the issue if you can provided more details.

@vpavic vpavic closed this as completed May 2, 2018
@vpavic vpavic removed the status: waiting-for-feedback We need additional information before we can continue label May 2, 2018
@TorosyanV
Copy link

TorosyanV commented May 30, 2018 •
edited
Loading

Hi, maybe we need understand and fix this issue. Why it has closed status?

@TorosyanV
Copy link

More details from logs.
running on tomcat 8.5
MySql sversion 5.5.7

2018-05-30 10:50:06.754  INFO 29715 --- [io-9780-exec-41] o.s.b.f.xml.XmlBeanDefinitionReader      : Loading XML bean definitions from class path resource [org/springframework/jdbc/support/sql-error-codes.xml]
2018-05-30 10:50:08.220  INFO 29715 --- [io-9780-exec-41] o.s.jdbc.support.SQLErrorCodesFactory    : SQLErrorCodes loaded: [DB2, Derby, H2, HDB, HSQL, Informix, MS-SQL, MySQL, Oracle, PostgreSQL, Sybase]
2018-05-30 10:50:08.225 ERROR 29715 --- [io-9780-exec-41] org.thymeleaf.TemplateEngine             : [THYMELEAF][http-nio-9780-exec-41] Exception processing template "car/detail": PreparedStatementCallback; SQL [INSERT INTO SPRING_SESSION_ATTRIBUTES(SESSION_ID, ATTRIBUTE_NAME, ATTRIBUTE_BYTES) VALUES (?, ?, ?)]; Deadlock found when trying to get lock; try restarting transaction; nested exception is java.sql.BatchUpdateException: Deadlock found when trying to get lock; try restarting transaction
2018-05-30 10:50:08.348 ERROR 29715 --- [io-9780-exec-41] o.s.boot.web.support.ErrorPageFilter     : Forwarding to error page from request [/car/detail/2809] due to exception [Exception processing template (car/detail)]

org.thymeleaf.exceptions.TemplateProcessingException: Exception processing template (car/detail)
        at org.thymeleaf.TemplateEngine.process(TemplateEngine.java:1091) ~[thymeleaf-2.1.6.RELEASE.jar:2.1.6.RELEASE]
        at org.thymeleaf.TemplateEngine.process(TemplateEngine.java:1011) ~[thymeleaf-2.1.6.RELEASE.jar:2.1.6.RELEASE]
        at org.thymeleaf.spring4.view.ThymeleafView.renderFragment(ThymeleafView.java:335) ~[thymeleaf-spring4-2.1.6.RELEASE.jar:2.1.6.RELEASE]
        at org.thymeleaf.spring4.view.ThymeleafView.render(ThymeleafView.java:190) ~[thymeleaf-spring4-2.1.6.RELEASE.jar:2.1.6.RELEASE]
        at org.springframework.web.servlet.DispatcherServlet.render(DispatcherServlet.java:1286) ~[spring-webmvc-4.3.17.RELEASE.jar:4.3.17.RELEASE]
        at org.springframework.web.servlet.DispatcherServlet.processDispatchResult(DispatcherServlet.java:1041) ~[spring-webmvc-4.3.17.RELEASE.jar:4.3.17.RELEASE]
        at org.springframework.web.servlet.DispatcherServlet.doDispatch(DispatcherServlet.java:984) ~[spring-webmvc-4.3.17.RELEASE.jar:4.3.17.RELEASE]
        at org.springframework.web.servlet.DispatcherServlet.doService(DispatcherServlet.java:901) ~[spring-webmvc-4.3.17.RELEASE.jar:4.3.17.RELEASE]
        at org.springframework.web.servlet.FrameworkServlet.processRequest(FrameworkServlet.java:970) ~[spring-webmvc-4.3.17.RELEASE.jar:4.3.17.RELEASE]
        at org.springframework.web.servlet.FrameworkServlet.doGet(FrameworkServlet.java:861) ~[spring-webmvc-4.3.17.RELEASE.jar:4.3.17.RELEASE]
        at javax.servlet.http.HttpServlet.service(HttpServlet.java:635) ~[servlet-api.jar:na]
        at org.springframework.web.servlet.FrameworkServlet.service(FrameworkServlet.java:846) ~[spring-webmvc-4.3.17.RELEASE.jar:4.3.17.RELEASE]
        at javax.servlet.http.HttpServlet.service(HttpServlet.java:742) ~[servlet-api.jar:na]
        at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:231) [catalina.jar:9.0.0.M21]
        at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166) [catalina.jar:9.0.0.M21]
        at org.apache.tomcat.websocket.server.WsFilter.doFilter(WsFilter.java:53) ~[tomcat-websocket.jar:9.0.0.M21]
        at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:193) [catalina.jar:9.0.0.M21]
        at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166) [catalina.jar:9.0.0.M21]
        at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:317) ~[spring-security-web-4.2.6.RELEASE.jar:4.2.6.RELEASE]
        at org.springframework.security.web.access.intercept.FilterSecurityInterceptor.invoke(FilterSecurityInterceptor.java:127) ~[spring-security-web-4.2.6.RELEASE.jar:4.2.6.RELEASE]
        at org.springframework.security.web.access.intercept.FilterSecurityInterceptor.doFilter(FilterSecurityInterceptor.java:91) ~[spring-security-web-4.2.6.RELEASE.jar:4.2.6.RELEASE]
        at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:331) ~[spring-security-web-4.2.6.RELEASE.jar:4.2.6.RELEASE]
        at org.springframework.security.web.access.ExceptionTranslationFilter.doFilter(ExceptionTranslationFilter.java:114) ~[spring-security-web-4.2.6.RELEASE.jar:4.2.6.RELEASE]
        at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:331) ~[spring-security-web-4.2.6.RELEASE.jar:4.2.6.RELEASE]
        at org.springframework.security.web.session.SessionManagementFilter.doFilter(SessionManagementFilter.java:137) ~[spring-security-web-4.2.6.RELEASE.jar:4.2.6.RELEASE]
        at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:331) ~[spring-security-web-4.2.6.RELEASE.jar:4.2.6.RELEASE]
        at org.springframework.security.web.authentication.AnonymousAuthenticationFilter.doFilter(AnonymousAuthenticationFilter.java:111) ~[spring-security-web-4.2.6.RELEASE.jar:4.2.6.RELEASE]
        at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:331) ~[spring-security-web-4.2.6.RELEASE.jar:4.2.6.RELEASE]
        at org.springframework.security.web.authentication.rememberme.RememberMeAuthenticationFilter.doFilter(RememberMeAuthenticationFilter.java:150) ~[spring-security-web-4.2.6.RELEASE.jar:4.2.6.RELEASE]
        at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:331) ~[spring-security-web-4.2.6.RELEASE.jar:4.2.6.RELEASE]
        at org.springframework.security.web.servletapi.SecurityContextHolderAwareRequestFilter.doFilter(SecurityContextHolderAwareRequestFilter.java:170) ~[spring-security-web-4.2.6.RELEASE.jar:4.2.6.RELEASE]
        at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:331) ~[spring-security-web-4.2.6.RELEASE.jar:4.2.6.RELEASE]
        at org.springframework.security.web.savedrequest.RequestCacheAwareFilter.doFilter(RequestCacheAwareFilter.java:63) ~[spring-security-web-4.2.6.RELEASE.jar:4.2.6.RELEASE]
        at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:331) ~[spring-security-web-4.2.6.RELEASE.jar:4.2.6.RELEASE]
        at org.springframework.security.web.authentication.AbstractAuthenticationProcessingFilter.doFilter(AbstractAuthenticationProcessingFilter.java:200) ~[spring-security-web-4.2.6.RELEASE.jar:4.2.6.RELEASE]
        at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:331) ~[spring-security-web-4.2.6.RELEASE.jar:4.2.6.RELEASE]
        at org.springframework.security.web.authentication.logout.LogoutFilter.doFilter(LogoutFilter.java:116) ~[spring-security-web-4.2.6.RELEASE.jar:4.2.6.RELEASE]
        at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:331) ~[spring-security-web-4.2.6.RELEASE.jar:4.2.6.RELEASE]
        at org.springframework.security.web.csrf.CsrfFilter.doFilterInternal(CsrfFilter.java:100) ~[spring-security-web-4.2.6.RELEASE.jar:4.2.6.RELEASE]
        at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:107) [spring-web-4.3.17.RELEASE.jar:4.3.17.RELEASE]
        at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:331) ~[spring-security-web-4.2.6.RELEASE.jar:4.2.6.RELEASE]
        at org.springframework.security.web.header.HeaderWriterFilter.doFilterInternal(HeaderWriterFilter.java:66) ~[spring-security-web-4.2.6.RELEASE.jar:4.2.6.RELEASE]
        at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:107) [spring-web-4.3.17.RELEASE.jar:4.3.17.RELEASE]
        at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:331) ~[spring-security-web-4.2.6.RELEASE.jar:4.2.6.RELEASE]
        at org.springframework.security.web.context.SecurityContextPersistenceFilter.doFilter(SecurityContextPersistenceFilter.java:105) ~[spring-security-web-4.2.6.RELEASE.jar:4.2.6.RELEASE]
        at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:331) ~[spring-security-web-4.2.6.RELEASE.jar:4.2.6.RELEASE]
        at org.springframework.security.web.context.request.async.WebAsyncManagerIntegrationFilter.doFilterInternal(WebAsyncManagerIntegrationFilter.java:56) ~[spring-security-web-4.2.6.RELEASE.jar:4.2.6.RELEASE]
        at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:107) [spring-web-4.3.17.RELEASE.jar:4.3.17.RELEASE]
        at org.springframework.security.web.FilterChainProxy$VirtualFilterChain.doFilter(FilterChainProxy.java:331) ~[spring-security-web-4.2.6.RELEASE.jar:4.2.6.RELEASE]
        at org.springframework.security.web.FilterChainProxy.doFilterInternal(FilterChainProxy.java:214) ~[spring-security-web-4.2.6.RELEASE.jar:4.2.6.RELEASE]
        at org.springframework.security.web.FilterChainProxy.doFilter(FilterChainProxy.java:177) ~[spring-security-web-4.2.6.RELEASE.jar:4.2.6.RELEASE]
        at org.springframework.web.filter.DelegatingFilterProxy.invokeDelegate(DelegatingFilterProxy.java:347) ~[spring-web-4.3.17.RELEASE.jar:4.3.17.RELEASE]
        at org.springframework.web.filter.DelegatingFilterProxy.doFilter(DelegatingFilterProxy.java:263) ~[spring-web-4.3.17.RELEASE.jar:4.3.17.RELEASE]
        at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:193) [catalina.jar:9.0.0.M21]
        at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166) [catalina.jar:9.0.0.M21]
        at org.springframework.web.filter.RequestContextFilter.doFilterInternal(RequestContextFilter.java:99) ~[spring-web-4.3.17.RELEASE.jar:4.3.17.RELEASE]
        at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:107) [spring-web-4.3.17.RELEASE.jar:4.3.17.RELEASE]
        at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:193) [catalina.jar:9.0.0.M21]
        at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166) [catalina.jar:9.0.0.M21]
        at org.springframework.web.filter.HttpPutFormContentFilter.doFilterInternal(HttpPutFormContentFilter.java:109) ~[spring-web-4.3.17.RELEASE.jar:4.3.17.RELEASE]
        at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:107) [spring-web-4.3.17.RELEASE.jar:4.3.17.RELEASE]
        at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:193) [catalina.jar:9.0.0.M21]
        at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166) [catalina.jar:9.0.0.M21]
        at org.springframework.web.filter.HiddenHttpMethodFilter.doFilterInternal(HiddenHttpMethodFilter.java:81) ~[spring-web-4.3.17.RELEASE.jar:4.3.17.RELEASE]
        at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:107) [spring-web-4.3.17.RELEASE.jar:4.3.17.RELEASE]
        at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:193) [catalina.jar:9.0.0.M21]
        at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166) [catalina.jar:9.0.0.M21]
        at org.springframework.session.web.http.SessionRepositoryFilter.doFilterInternal(SessionRepositoryFilter.java:167) ~[spring-session-1.3.2.RELEASE.jar:na]
        at org.springframework.session.web.http.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:80) ~[spring-session-1.3.2.RELEASE.jar:na]
        at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:193) [catalina.jar:9.0.0.M21]
        at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166) [catalina.jar:9.0.0.M21]
        at org.springframework.web.filter.CharacterEncodingFilter.doFilterInternal(CharacterEncodingFilter.java:197) ~[spring-web-4.3.17.RELEASE.jar:4.3.17.RELEASE]
        at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:107) [spring-web-4.3.17.RELEASE.jar:4.3.17.RELEASE]
        at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:193) [catalina.jar:9.0.0.M21]
        at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166) [catalina.jar:9.0.0.M21]
        at org.springframework.boot.web.support.ErrorPageFilter.doFilter(ErrorPageFilter.java:117) [spring-boot-1.5.13.RELEASE.jar:1.5.13.RELEASE]
        at org.springframework.boot.web.support.ErrorPageFilter.access$000(ErrorPageFilter.java:61) [spring-boot-1.5.13.RELEASE.jar:1.5.13.RELEASE]
        at org.springframework.boot.web.support.ErrorPageFilter$1.doFilterInternal(ErrorPageFilter.java:92) [spring-boot-1.5.13.RELEASE.jar:1.5.13.RELEASE]
        at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:107) [spring-web-4.3.17.RELEASE.jar:4.3.17.RELEASE]
        at org.springframework.boot.web.support.ErrorPageFilter.doFilter(ErrorPageFilter.java:110) [spring-boot-1.5.13.RELEASE.jar:1.5.13.RELEASE]
        at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:193) [catalina.jar:9.0.0.M21]
        at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166) [catalina.jar:9.0.0.M21]
        at org.apache.catalina.core.StandardWrapperValve.invoke(StandardWrapperValve.java:199) [catalina.jar:9.0.0.M21]
        at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:96) [catalina.jar:9.0.0.M21]
        at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:475) [catalina.jar:9.0.0.M21]
        at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:140) [catalina.jar:9.0.0.M21]
        at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:80) [catalina.jar:9.0.0.M21]
        at org.apache.catalina.valves.AbstractAccessLogValve.invoke(AbstractAccessLogValve.java:624) [catalina.jar:9.0.0.M21]
        at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:87) [catalina.jar:9.0.0.M21]
        at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:342) [catalina.jar:9.0.0.M21]
        at org.apache.coyote.http11.Http11Processor.service(Http11Processor.java:498) [tomcat-coyote.jar:9.0.0.M21]
        at org.apache.coyote.AbstractProcessorLight.process(AbstractProcessorLight.java:66) [tomcat-coyote.jar:9.0.0.M21]
        at org.apache.coyote.AbstractProtocol$ConnectionHandler.process(AbstractProtocol.java:796) [tomcat-coyote.jar:9.0.0.M21]
        at org.apache.tomcat.util.net.NioEndpoint$SocketProcessor.doRun(NioEndpoint.java:1366) [tomcat-coyote.jar:9.0.0.M21]
        at org.apache.tomcat.util.net.SocketProcessorBase.run(SocketProcessorBase.java:49) [tomcat-coyote.jar:9.0.0.M21]
        at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149) [na:1.8.0_144]
        at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624) [na:1.8.0_144]
        at org.apache.tomcat.util.threads.TaskThread$WrappingRunnable.run(TaskThread.java:61) [tomcat-util.jar:9.0.0.M21]
        at java.lang.Thread.run(Thread.java:748) [na:1.8.0_144]
Caused by: org.springframework.dao.DeadlockLoserDataAccessException: PreparedStatementCallback; SQL [INSERT INTO SPRING_SESSION_ATTRIBUTES(SESSION_ID, ATTRIBUTE_NAME, ATTRIBUTE_BYTES) VALUES (?, ?, ?)]; Deadlock found when trying to get lock; try restarting transaction; nested exception is java.sql.BatchUpdateException: Deadlock found when trying to get lock; try restarting transaction
        at org.springframework.jdbc.support.SQLErrorCodeSQLExceptionTranslator.doTranslate(SQLErrorCodeSQLExceptionTranslator.java:263) ~[spring-jdbc-4.3.17.RELEASE.jar:4.3.17.RELEASE]
        at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:73) ~[spring-jdbc-4.3.17.RELEASE.jar:4.3.17.RELEASE]
        at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:655) ~[spring-jdbc-4.3.17.RELEASE.jar:4.3.17.RELEASE]
        at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:668) ~[spring-jdbc-4.3.17.RELEASE.jar:4.3.17.RELEASE]
        at org.springframework.jdbc.core.JdbcTemplate.batchUpdate(JdbcTemplate.java:956) ~[spring-jdbc-4.3.17.RELEASE.jar:4.3.17.RELEASE]
        at org.springframework.session.jdbc.JdbcOperationsSessionRepository$1.doInTransactionWithoutResult(JdbcOperationsSessionRepository.java:396) ~[spring-session-1.3.2.RELEASE.jar:na]
        at org.springframework.transaction.support.TransactionCallbackWithoutResult.doInTransaction(TransactionCallbackWithoutResult.java:34) ~[spring-tx-4.3.17.RELEASE.jar:4.3.17.RELEASE]
        at org.springframework.transaction.support.TransactionTemplate.execute(TransactionTemplate.java:133) ~[spring-tx-4.3.17.RELEASE.jar:4.3.17.RELEASE]
        at org.springframework.session.jdbc.JdbcOperationsSessionRepository.save(JdbcOperationsSessionRepository.java:378) ~[spring-session-1.3.2.RELEASE.jar:na]
        at org.springframework.session.jdbc.JdbcOperationsSessionRepository.save(JdbcOperationsSessionRepository.java:130) ~[spring-session-1.3.2.RELEASE.jar:na]
        at org.springframework.session.web.http.SessionRepositoryFilter$SessionRepositoryRequestWrapper.commitSession(SessionRepositoryFilter.java:245) ~[spring-session-1.3.2.RELEASE.jar:na]
        at org.springframework.session.web.http.SessionRepositoryFilter$SessionRepositoryRequestWrapper.access$100(SessionRepositoryFilter.java:217) ~[spring-session-1.3.2.RELEASE.jar:na]
        at org.springframework.session.web.http.SessionRepositoryFilter$SessionRepositoryResponseWrapper.onResponseCommitted(SessionRepositoryFilter.java:205) ~[spring-session-1.3.2.RELEASE.jar:na]
        at org.springframework.session.web.http.OnCommittedResponseWrapper.doOnResponseCommitted(OnCommittedResponseWrapper.java:226) ~[spring-session-1.3.2.RELEASE.jar:na]
        at org.springframework.session.web.http.OnCommittedResponseWrapper.checkContentLength(OnCommittedResponseWrapper.java:216) ~[spring-session-1.3.2.RELEASE.jar:na]
        at org.springframework.session.web.http.OnCommittedResponseWrapper.trackContentLength(OnCommittedResponseWrapper.java:200) ~[spring-session-1.3.2.RELEASE.jar:na]
        at org.springframework.session.web.http.OnCommittedResponseWrapper.access$400(OnCommittedResponseWrapper.java:37) ~[spring-session-1.3.2.RELEASE.jar:na]
        at org.springframework.session.web.http.OnCommittedResponseWrapper$SaveContextPrintWriter.write(OnCommittedResponseWrapper.java:293) ~[spring-session-1.3.2.RELEASE.jar:na]
        at org.springframework.security.web.util.OnCommittedResponseWrapper$SaveContextPrintWriter.write(OnCommittedResponseWrapper.java:325) ~[spring-security-web-4.2.6.RELEASE.jar:4.2.6.RELEASE]
        at org.springframework.security.web.util.OnCommittedResponseWrapper$SaveContextPrintWriter.write(OnCommittedResponseWrapper.java:325) ~[spring-security-web-4.2.6.RELEASE.jar:4.2.6.RELEASE]
        at org.thymeleaf.templatewriter.AbstractGeneralTemplateWriter.writeText(AbstractGeneralTemplateWriter.java:326) ~[thymeleaf-2.1.6.RELEASE.jar:2.1.6.RELEASE]
        at org.thymeleaf.templatewriter.AbstractGeneralTemplateWriter.writeNode(AbstractGeneralTemplateWriter.java:149) ~[thymeleaf-2.1.6.RELEASE.jar:2.1.6.RELEASE]
        at org.thymeleaf.templatewriter.AbstractGeneralTemplateWriter.writeElement(AbstractGeneralTemplateWriter.java:257) ~[thymeleaf-2.1.6.RELEASE.jar:2.1.6.RELEASE]
        at org.thymeleaf.templatewriter.AbstractGeneralTemplateWriter.writeNode(AbstractGeneralTemplateWriter.java:145) ~[thymeleaf-2.1.6.RELEASE.jar:2.1.6.RELEASE]
        at org.thymeleaf.templatewriter.AbstractGeneralTemplateWriter.writeElement(AbstractGeneralTemplateWriter.java:257) ~[thymeleaf-2.1.6.RELEASE.jar:2.1.6.RELEASE]
        at org.thymeleaf.templatewriter.AbstractGeneralTemplateWriter.writeNode(AbstractGeneralTemplateWriter.java:145) ~[thymeleaf-2.1.6.RELEASE.jar:2.1.6.RELEASE]
        at org.thymeleaf.templatewriter.AbstractGeneralTemplateWriter.writeElement(AbstractGeneralTemplateWriter.java:257) ~[thymeleaf-2.1.6.RELEASE.jar:2.1.6.RELEASE]
        at org.thymeleaf.templatewriter.AbstractGeneralTemplateWriter.writeNode(AbstractGeneralTemplateWriter.java:145) ~[thymeleaf-2.1.6.RELEASE.jar:2.1.6.RELEASE]
        at org.thymeleaf.templatewriter.AbstractGeneralTemplateWriter.writeElement(AbstractGeneralTemplateWriter.java:257) ~[thymeleaf-2.1.6.RELEASE.jar:2.1.6.RELEASE]
        at org.thymeleaf.templatewriter.AbstractGeneralTemplateWriter.writeNode(AbstractGeneralTemplateWriter.java:145) ~[thymeleaf-2.1.6.RELEASE.jar:2.1.6.RELEASE]
        at org.thymeleaf.templatewriter.AbstractGeneralTemplateWriter.writeElement(AbstractGeneralTemplateWriter.java:257) ~[thymeleaf-2.1.6.RELEASE.jar:2.1.6.RELEASE]
        at org.thymeleaf.templatewriter.AbstractGeneralTemplateWriter.writeNode(AbstractGeneralTemplateWriter.java:145) ~[thymeleaf-2.1.6.RELEASE.jar:2.1.6.RELEASE]
        at org.thymeleaf.templatewriter.AbstractGeneralTemplateWriter.writeElement(AbstractGeneralTemplateWriter.java:257) ~[thymeleaf-2.1.6.RELEASE.jar:2.1.6.RELEASE]
        at org.thymeleaf.templatewriter.AbstractGeneralTemplateWriter.writeNode(AbstractGeneralTemplateWriter.java:145) ~[thymeleaf-2.1.6.RELEASE.jar:2.1.6.RELEASE]
        at org.thymeleaf.templatewriter.AbstractGeneralTemplateWriter.writeElement(AbstractGeneralTemplateWriter.java:257) ~[thymeleaf-2.1.6.RELEASE.jar:2.1.6.RELEASE]
        at org.thymeleaf.templatewriter.AbstractGeneralTemplateWriter.writeNode(AbstractGeneralTemplateWriter.java:145) ~[thymeleaf-2.1.6.RELEASE.jar:2.1.6.RELEASE]
        at org.thymeleaf.templatewriter.AbstractGeneralTemplateWriter.writeElement(AbstractGeneralTemplateWriter.java:257) ~[thymeleaf-2.1.6.RELEASE.jar:2.1.6.RELEASE]
        at org.thymeleaf.templatewriter.AbstractGeneralTemplateWriter.writeNode(AbstractGeneralTemplateWriter.java:145) ~[thymeleaf-2.1.6.RELEASE.jar:2.1.6.RELEASE]
        at org.thymeleaf.templatewriter.AbstractGeneralTemplateWriter.writeElement(AbstractGeneralTemplateWriter.java:257) ~[thymeleaf-2.1.6.RELEASE.jar:2.1.6.RELEASE]
        at org.thymeleaf.templatewriter.AbstractGeneralTemplateWriter.writeNode(AbstractGeneralTemplateWriter.java:145) ~[thymeleaf-2.1.6.RELEASE.jar:2.1.6.RELEASE]
        at org.thymeleaf.templatewriter.AbstractGeneralTemplateWriter.writeElement(AbstractGeneralTemplateWriter.java:257) ~[thymeleaf-2.1.6.RELEASE.jar:2.1.6.RELEASE]
        at org.thymeleaf.templatewriter.AbstractGeneralTemplateWriter.writeNode(AbstractGeneralTemplateWriter.java:145) ~[thymeleaf-2.1.6.RELEASE.jar:2.1.6.RELEASE]
        at org.thymeleaf.templatewriter.AbstractGeneralTemplateWriter.writeElement(AbstractGeneralTemplateWriter.java:257) ~[thymeleaf-2.1.6.RELEASE.jar:2.1.6.RELEASE]
        at org.thymeleaf.templatewriter.AbstractGeneralTemplateWriter.writeNode(AbstractGeneralTemplateWriter.java:145) ~[thymeleaf-2.1.6.RELEASE.jar:2.1.6.RELEASE]
        at org.thymeleaf.templatewriter.AbstractGeneralTemplateWriter.writeDocument(AbstractGeneralTemplateWriter.java:97) ~[thymeleaf-2.1.6.RELEASE.jar:2.1.6.RELEASE]
        at org.thymeleaf.templatewriter.AbstractGeneralTemplateWriter.write(AbstractGeneralTemplateWriter.java:72) ~[thymeleaf-2.1.6.RELEASE.jar:2.1.6.RELEASE]
        at org.thymeleaf.TemplateEngine.process(TemplateEngine.java:1175) ~[thymeleaf-2.1.6.RELEASE.jar:2.1.6.RELEASE]
        at org.thymeleaf.TemplateEngine.process(TemplateEngine.java:1060) ~[thymeleaf-2.1.6.RELEASE.jar:2.1.6.RELEASE]
        ... 98 common frames omitted
Caused by: java.sql.BatchUpdateException: Deadlock found when trying to get lock; try restarting transaction
        at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method) ~[na:1.8.0_144]
        at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:62) ~[na:1.8.0_144]
        at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45) ~[na:1.8.0_144]
        at java.lang.reflect.Constructor.newInstance(Constructor.java:423) ~[na:1.8.0_144]
        at com.mysql.jdbc.Util.handleNewInstance(Util.java:425) ~[mysql-connector-java-5.1.46.jar:5.1.46]
        at com.mysql.jdbc.Util.getInstance(Util.java:408) ~[mysql-connector-java-5.1.46.jar:5.1.46]
        at com.mysql.jdbc.SQLError.createBatchUpdateException(SQLError.java:1163) ~[mysql-connector-java-5.1.46.jar:5.1.46]
        at com.mysql.jdbc.PreparedStatement.executeBatchSerially(PreparedStatement.java:1772) ~[mysql-connector-java-5.1.46.jar:5.1.46]
        at com.mysql.jdbc.PreparedStatement.executeBatchInternal(PreparedStatement.java:1262) ~[mysql-connector-java-5.1.46.jar:5.1.46]
        at com.mysql.jdbc.StatementImpl.executeBatch(StatementImpl.java:970) ~[mysql-connector-java-5.1.46.jar:5.1.46]
        at sun.reflect.GeneratedMethodAccessor424.invoke(Unknown Source) ~[na:na]
        at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) ~[na:1.8.0_144]
        at java.lang.reflect.Method.invoke(Method.java:498) ~[na:1.8.0_144]
        at org.apache.tomcat.jdbc.pool.StatementFacade$StatementProxy.invoke(StatementFacade.java:114) ~[tomcat-jdbc-8.5.31.jar:na]
        at com.sun.proxy.$Proxy165.executeBatch(Unknown Source) ~[na:na]
        at org.springframework.jdbc.core.JdbcTemplate$4.doInPreparedStatement(JdbcTemplate.java:972) ~[spring-jdbc-4.3.17.RELEASE.jar:4.3.17.RELEASE]
        at org.springframework.jdbc.core.JdbcTemplate$4.doInPreparedStatement(JdbcTemplate.java:956) ~[spring-jdbc-4.3.17.RELEASE.jar:4.3.17.RELEASE]
        at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:639) ~[spring-jdbc-4.3.17.RELEASE.jar:4.3.17.RELEASE]
        ... 143 common frames omitted
Caused by: com.mysql.jdbc.exceptions.jdbc4.MySQLTransactionRollbackException: Deadlock found when trying to get lock; try restarting transaction
        at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method) ~[na:1.8.0_144]
        at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:62) ~[na:1.8.0_144]
        at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45) ~[na:1.8.0_144]
        at java.lang.reflect.Constructor.newInstance(Constructor.java:423) ~[na:1.8.0_144]
        at com.mysql.jdbc.Util.handleNewInstance(Util.java:425) ~[mysql-connector-java-5.1.46.jar:5.1.46]
        at com.mysql.jdbc.Util.getInstance(Util.java:408) ~[mysql-connector-java-5.1.46.jar:5.1.46]
        at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:952) ~[mysql-connector-java-5.1.46.jar:5.1.46]
        at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3976) ~[mysql-connector-java-5.1.46.jar:5.1.46]
        at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3912) ~[mysql-connector-java-5.1.46.jar:5.1.46]
        at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2530) ~[mysql-connector-java-5.1.46.jar:5.1.46]
        at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2683) ~[mysql-connector-java-5.1.46.jar:5.1.46]
        at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2486) ~[mysql-connector-java-5.1.46.jar:5.1.46]
        at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:1858) ~[mysql-connector-java-5.1.46.jar:5.1.46]
        at com.mysql.jdbc.PreparedStatement.executeUpdateInternal(PreparedStatement.java:2079) ~[mysql-connector-java-5.1.46.jar:5.1.46]
        at com.mysql.jdbc.PreparedStatement.executeBatchSerially(PreparedStatement.java:1756) ~[mysql-connector-java-5.1.46.jar:5.1.46]
        ... 153 common frames omitted

@vpavic vpavic mentioned this issue May 30, 2018
Closed
@isalnikov
Copy link
Author

isalnikov commented May 31, 2018 •
edited
Loading

@TorosyanV , да тут бесполезно что то объяснять , они делают универсальную вещь .
Мы просто переписали весь проект - под mysql.

Проблема дедлока в том что : в мускуле нельзя делать PK как строка да еще и UUID - идея PK что это индекс и он монотонно должен расти.

Дедлок:

  1. один поток делает вставку - он лочит индекс

  2. второй поток когда удаляет - делает лок по PK который в данном примере будет строкой - UUID не монотонно возрастает из за этого конфликты .

  3. лечится : делать так : PRIMARY_ID - всегда bigint

`CREATE TABLE SPRING_SESSION (
PRIMARY_ID bigint NOT NULL AUTO_INCREMENT,
SESSION_ID VARCHAR(36) NOT NULL,
CREATION_TIME BIGINT NOT NULL,
LAST_ACCESS_TIME BIGINT NOT NULL,
MAX_INACTIVE_INTERVAL INT NOT NULL,
EXPIRY_TIME BIGINT NOT NULL,
PRINCIPAL_NAME VARCHAR(100),
CONSTRAINT SPRING_SESSION_PK PRIMARY KEY (PRIMARY_ID)
) ENGINE=InnoDB;

CREATE UNIQUE INDEX SPRING_SESSION_IX1 ON SPRING_SESSION (SESSION_ID);
CREATE INDEX SPRING_SESSION_IX2 ON SPRING_SESSION (EXPIRY_TIME);
CREATE INDEX SPRING_SESSION_IX3 ON SPRING_SESSION (PRINCIPAL_NAME);

CREATE TABLE SPRING_SESSION_ATTRIBUTES (
SESSION_PRIMARY_ID bigint NOT NULL,
ATTRIBUTE_NAME VARCHAR(200) NOT NULL,
ATTRIBUTE_BYTES mediumblob NOT NULL,
CONSTRAINT SPRING_SESSION_ATTRIBUTES_PK PRIMARY KEY (SESSION_PRIMARY_ID, ATTRIBUTE_NAME),
CONSTRAINT SPRING_SESSION_ATTRIBUTES_FK FOREIGN KEY (SESSION_PRIMARY_ID) REFERENCES SPRING_SESSION(PRIMARY_ID) ON DELETE CASCADE
) ENGINE=InnoDB;`

Удаление делать так :

private static final String DELETE_SESSIONS_BY_EXPIRY_TIME_QUERY = "DELETE FROM %TABLE_NAME% " + "WHERE EXPIRY_TIME < ?";

Делать ролбеки чтобы после дедлока можно было откатиться .

`
@SchedulerLock(name = EXPIRED_SESSION_DEL, lockAtLeastFor = 30 * 1000, lockAtMostFor = 60 * 1000)
@scheduled(initialDelay = 1000 * 60 * 10, fixedDelay = 1000 * 60 * 4)
public void cleanUpExpiredSessions() {
Integer deletedCount = this.transactionOperations.execute(transactionStatus ->
S3JdbcOperationsSessionRepository.this.jdbcOperations.update(
S3JdbcOperationsSessionRepository.this.deleteSessionsByExpiryTimeQuery,
ps -> {
ps.setQueryTimeout(5);
ps.setLong(1,System.currentTimeMillis());
}));

    if (log.isDebugEnabled()) {
        log.debug("Cleaned up " + deletedCount + " expired sessions");
    }
}`

@vpavic vpavic reopened this May 31, 2018
@vpavic
Copy link
Contributor

vpavic commented May 31, 2018

Thanks for following up @isalnikov, although please try to keep your comments in English.

I'm reopening this to double check if there's anything we can do on our side, and I'd appreciate if you could come up with a sample that reproduces this issue.

Also provide as much details as possible about your environment.

@vpavic vpavic added the status: waiting-for-feedback We need additional information before we can continue label Jun 1, 2018
@vpavic
Copy link
Contributor

vpavic commented Nov 12, 2018

Closing as it doesn't appear that we can do anything here in an RDBMS vendor neutral way. The thing that might help would be introduction of session id generation strategy that was proposed in #11 so please track that issue and feel free to vote on it.

@vpavic vpavic closed this as completed Nov 12, 2018
@vpavic vpavic removed the status: waiting-for-feedback We need additional information before we can continue label Nov 12, 2018
@vpavic
Copy link
Contributor

vpavic commented Nov 14, 2020

Marking this as a duplicate of #838. Please subscribe to that issue to track further updates.

@vpavic vpavic added status: duplicate A duplicate of another issue and removed for: stack-overflow A question that's better suited to stackoverflow.com labels Nov 14, 2020
@vpavic vpavic mentioned this issue Nov 14, 2020
Open
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Assignees

@vpavic vpavic

Labels
status: duplicate A duplicate of another issue
Projects
None yet
Milestone
No milestone
Development

No branches or pull requests
3 participants
@vpavic @isalnikov @TorosyanV