Session JDBC, handling of those frequent innodb deadlocks?

[hoene]

Using spring boot 1.4.2 and spring session via JDBC.

Using session handling with JDBC, connected to a galera cluster,

Frequently, we get innodb deadlocks (see below).

To my understanding, deadlocks

1. occur far too often
2. are not handled properly.

Any ideas to cope with it?

PS:

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.7.0_121]
at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:57) ~[na:1.7.0_121]
at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45) ~[na:1.7.0_121]
at java.lang.reflect.Constructor.newInstance(Constructor.java:526) ~[na:1.7.0_121]
at com.mysql.jdbc.Util.handleNewInstance(Util.java:425) ~[mysql-connector-java-5.1.40.jar:5.1.40]
at com.mysql.jdbc.Util.getInstance(Util.java:408) ~[mysql-connector-java-5.1.40.jar:5.1.40]
at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:951) ~[mysql-connector-java-5.1.40.jar:5.1.40]
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3970) ~[mysql-connector-java-5.1.40.jar:5.1.40]
at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3906) ~[mysql-connector-java-5.1.40.jar:5.1.40]
at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2524) ~[mysql-connector-java-5.1.40.jar:5.1.40]
at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2677) ~[mysql-connector-java-5.1.40.jar:5.1.40]
at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2545) ~[mysql-connector-java-5.1.40.jar:5.1.40]
at com.mysql.jdbc.ConnectionImpl.commit(ConnectionImpl.java:1614) ~[mysql-connector-java-5.1.40.jar:5.1.40]
at sun.reflect.GeneratedMethodAccessor272.invoke(Unknown Source) ~[na:na]
at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43) ~[na:1.7.0_121]
at java.lang.reflect.Method.invoke(Method.java:606) ~[na:1.7.0_121]
at org.apache.tomcat.jdbc.pool.ProxyConnection.invoke(ProxyConnection.java:126) ~[tomcat-jdbc-8.5.6.jar:na]
at org.apache.tomcat.jdbc.pool.JdbcInterceptor.invoke(JdbcInterceptor.java:108) ~[tomcat-jdbc-8.5.6.jar:na]
at org.apache.tomcat.jdbc.pool.DisposableConnectionFacade.invoke(DisposableConnectionFacade.java:81) ~[tomcat-jdbc-8.5.6.jar:na]
at com.sun.proxy.$Proxy82.commit(Unknown Source) ~[na:na]
at org.hibernate.resource.jdbc.internal.AbstractLogicalConnectionImplementor.commit(AbstractLogicalConnectionImplementor.java:80) ~[hibernate-core-5.0.11.Final.jar:5.0.11.Final]
at org.hibernate.resource.transaction.backend.jdbc.internal.JdbcResourceLocalTransactionCoordinatorImpl$TransactionDriverControlImpl.commit(JdbcResourceLocalTransactionCoordinatorImpl.java:232) ~[hibernate-core-5.0.11.Final.jar:5.0.11.Final]
at org.hibernate.engine.transaction.internal.TransactionImpl.commit(TransactionImpl.java:65) ~[hibernate-core-5.0.11.Final.jar:5.0.11.Final]
at org.hibernate.jpa.internal.TransactionImpl.commit(TransactionImpl.java:61) ~[hibernate-entitymanager-5.0.11.Final.jar:5.0.11.Final]
at org.springframework.orm.jpa.JpaTransactionManager.doCommit(JpaTransactionManager.java:517) ~[spring-orm-4.3.4.RELEASE.jar:4.3.4.RELEASE]
at org.springframework.transaction.support.AbstractPlatformTransactionManager.processCommit(AbstractPlatformTransactionManager.java:761) ~[spring-tx-4.3.4.RELEASE.jar:4.3.4.RELEASE]
at org.springframework.transaction.support.AbstractPlatformTransactionManager.commit(AbstractPlatformTransactionManager.java:730) ~[spring-tx-4.3.4.RELEASE.jar:4.3.4.RELEASE]
at org.springframework.transaction.support.TransactionTemplate.execute(TransactionTemplate.java:150) ~[spring-tx-4.3.4.RELEASE.jar:4.3.4.RELEASE]
at org.springframework.session.jdbc.JdbcOperationsSessionRepository.save(JdbcOperationsSessionRepository.java:314) ~[spring-session-1.2.2.RELEASE.jar:na]
at org.springframework.session.jdbc.JdbcOperationsSessionRepository.save(JdbcOperationsSessionRepository.java:130) ~[spring-session-1.2.2.RELEASE.jar:na]
at org.springframework.session.web.http.SessionRepositoryFilter$SessionRepositoryRequestWrapper.commitSession(SessionRepositoryFilter.java:244) ~[spring-session-1.2.2.RELEASE.jar:na]
at org.springframework.session.web.http.SessionRepositoryFilter$SessionRepositoryRequestWrapper.access$100(SessionRepositoryFilter.java:214) ~[spring-session-1.2.2.RELEASE.jar:na]
at org.springframework.session.web.http.SessionRepositoryFilter$SessionRepositoryResponseWrapper.onResponseCommitted(SessionRepositoryFilter.java:202) ~[spring-session-1.2.2.RELEASE.jar:na]
at org.springframework.session.web.http.OnCommittedResponseWrapper.doOnResponseCommitted(OnCommittedResponseWrapper.java:226) ~[spring-session-1.2.2.RELEASE.jar:na]
at org.springframework.session.web.http.OnCommittedResponseWrapper.access$000(OnCommittedResponseWrapper.java:37) ~[spring-session-1.2.2.RELEASE.jar:na]
at org.springframework.session.web.http.OnCommittedResponseWrapper$SaveContextServletOutputStream.flush(OnCommittedResponseWrapper.java:457) ~[spring-session-1.2.2.RELEASE.jar:na]
at org.springframework.security.web.util.OnCommittedResponseWrapper$SaveContextServletOutputStream.flush(OnCommittedResponseWrapper.java:513) ~[spring-security-web-4.1.3.RELEASE.jar:4.1.3.RELEASE]

[vpavic]

@Choene I'm not an expert in that domain, but from my experience with multi-master cluster like Galera, such setup is quite problematic in scenarios with increased writes, and session persistence certainly can be qualified as such.

I'm afraid there's not much we can do here from Spring Session perspective as this is effectively the limitation of the underlying store. IMO the best approach would be to ensure there's only one node you write your sessions to.

[vpavic]

Closing as answered. If you have additional questions or feel that your original question isn't properly answered, please re-open the issue.

[isalnikov]

https://severalnines.com/blog/avoiding-deadlocks-galera-set-haproxy-single-node-writes-and-multi-node-reads

[isalnikov]

https://github.com/spring-projects/spring-retry