Deadlock in SQL Server when INSERT INTO SPRING_SESSION_ATTRIBUTES by JdbcOperationsSessionRepository

[gemslinks]

I have a deadlock in SQL Server.

LOG

Forwarding to error page from request [/login] due to exception [PreparedStatementCallback; SQL [INSERT INTO SPRING_SESSION_ATTRIBUTES(SESSION_PRIMARY_ID, ATTRIBUTE_NAME, ATTRIBUTE_BYTES) SELECT PRIMARY_ID, ?, ? FROM SPRING_SESSION WHERE SESSION_ID = ?]; ***deadlock error message from sql server is omitted because Japanese language***
org.springframework.dao.DeadlockLoserDataAccessException: PreparedStatementCallback; SQL [INSERT INTO SPRING_SESSION_ATTRIBUTES(SESSION_PRIMARY_ID, ATTRIBUTE_NAME, ATTRIBUTE_BYTES) SELECT PRIMARY_ID, ?, ? FROM SPRING_SESSION WHERE SESSION_ID = ?]; ***deadlock error message from sql server is omitted because Japanese language***
	at org.springframework.jdbc.support.SQLErrorCodeSQLExceptionTranslator.doTranslate(SQLErrorCodeSQLExceptionTranslator.java:266)
	at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:72)
	at org.springframework.jdbc.core.JdbcTemplate.translateException(JdbcTemplate.java:1442)
	at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:632)
	at org.springframework.jdbc.core.JdbcTemplate.update(JdbcTemplate.java:861)
	at org.springframework.jdbc.core.JdbcTemplate.update(JdbcTemplate.java:916)
	at org.springframework.session.jdbc.JdbcOperationsSessionRepository.insertSessionAttributes(JdbcOperationsSessionRepository.java:548)
	at org.springframework.session.jdbc.JdbcOperationsSessionRepository.access$300(JdbcOperationsSessionRepository.java:135)

I read all the previous issues about this problem.

As #1083 @TorosyanV said:

we need to always keep order to avoid deadlock on parent and child relationship table's operation.

like as below:

parnet = SPRING_SESSION
child = SPRING_SESSION_ATTRIBUTES

insert parent first, then child
update parent first, then child
delete parent first, then child
select parent first, then child

I looked at the JdbcOperationsSessionRepository code,
and I understood it looks like keeping transaction order.

but major problem here is DELETE_SESSIONS_BY_EXPIRY_TIME_QUERY is depending on each relational database's CASCADE ON DELETE algorithm
which might not be the same order deleting parent and child.
and also, we are difficult position to know or change relational database's CASCADE ON DELETE algorithm.

It is better not rely on to database's CASCADE ON DELETE.
I wish, Spring framework will ensure the every session table's transaction operation order by own.

[ShruthiPitta]

Hi,

We are facing similar issue when there are two parallel session inserts. Currently we are using Sql server.

The following is the deadlock graph:
<deadlock> <victim-list> <victimProcess id="process2a5b11ae8c8"/> </victim-list> <process-list> <process id="process2a5b11ae8c8" taskpriority="0" logused="840" waitresource="KEY: 25:72057594057326592 (929d4314f859)" waittime="100" ownerId="16528331821" transactionname="implicit_transaction" lasttranstarted="2020-05-14T23:38:25.543" XDES="0x2ef3655eee0" lockMode="S" schedulerid="44" kpid="12216" status="suspended" spid="351" sbid="0" ecid="0" priority="0" trancount="2" lastbatchstarted="2020-05-14T23:38:25.547" lastbatchcompleted="2020-05-14T23:38:25.543" lastattention="1900-01-01T00:00:00.543" clientapp="Microsoft JDBC Driver for SQL Server" hostname="xxxxxx" hostpid="0" loginname="xxxxxx" isolationlevel="read committed (2)" xactid="16528331821" currentdb="25" currentdbname="xyz" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128058"> <executionStack> <frame procname="adhoc" line="1" stmtstart="118" stmtend="498" sqlhandle="0x020000009acce52cc3c4ed8aa368718aca6003ca9866579b0000000000000000000000000000000000000000"> unknown </frame> <frame procname="unknown" line="1" sqlhandle="0x0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000"> unknown </frame> </executionStack> <inputbuf> (@P0 nvarchar(4000),@P1 varbinary(8000),@P2 nvarchar(4000))INSERT INTO UserManagement.SPRING_SESSION_ATTRIBUTES(SESSION_PRIMARY_ID, ATTRIBUTE_NAME, ATTRIBUTE_BYTES) SELECT PRIMARY_ID, @P0, @P1 FROM UserManagement.SPRING_SESSION WHERE SESSION_ID = @P2 </inputbuf> </process> <process id="process24d550c9088" taskpriority="0" logused="840" waitresource="KEY: 25:72057594057326592 (929d4314f859)" waittime="100" ownerId="16528332242" transactionname="implicit_transaction" lasttranstarted="2020-05-14T23:38:26.177" XDES="0x29969426430" lockMode="S" schedulerid="62" kpid="16676" status="suspended" spid="218" sbid="0" ecid="0" priority="0" trancount="2" lastbatchstarted="2020-05-14T23:38:26.180" lastbatchcompleted="2020-05-14T23:38:26.177" lastattention="1900-01-01T00:00:00.177" clientapp="Microsoft JDBC Driver for SQL Server" hostname="xxxxxx" hostpid="0" loginname="xxxxxx" isolationlevel="read committed (2)" xactid="16528332242" currentdb="25" currentdbname="xyz" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128058"> <executionStack> <frame procname="adhoc" line="1" stmtstart="118" stmtend="498" sqlhandle="0x020000009acce52cc3c4ed8aa368718aca6003ca9866579b0000000000000000000000000000000000000000"> unknown </frame> <frame procname="unknown" line="1" sqlhandle="0x0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000"> unknown </frame> </executionStack> <inputbuf> (@P0 nvarchar(4000),@P1 varbinary(8000),@P2 nvarchar(4000))INSERT INTO UserManagement.SPRING_SESSION_ATTRIBUTES(SESSION_PRIMARY_ID, ATTRIBUTE_NAME, ATTRIBUTE_BYTES) SELECT PRIMARY_ID, @P0, @P1 FROM UserManagement.SPRING_SESSION WHERE SESSION_ID = @P2 </inputbuf> </process> <process id="process2770f19d848" taskpriority="0" logused="2552" waitresource="KEY: 25:72057594057326592 (40c57bbdc6c3)" waittime="100" ownerId="16528331937" transactionname="implicit_transaction" lasttranstarted="2020-05-14T23:38:25.760" XDES="0x239a947aee0" lockMode="S" schedulerid="19" kpid="13816" status="suspended" spid="526" sbid="0" ecid="0" priority="0" trancount="2" lastbatchstarted="2020-05-14T23:38:25.763" lastbatchcompleted="2020-05-14T23:38:25.760" lastattention="1900-01-01T00:00:00.760" clientapp="Microsoft JDBC Driver for SQL Server" hostname="xxxxxx" hostpid="0" loginname="xxxxxx" isolationlevel="read committed (2)" xactid="16528331937" currentdb="25" currentdbname="xyz" lockTimeout="4294967295" clientoption1="671088672" clientoption2="128058"> <executionStack> <frame procname="adhoc" line="1" stmtstart="118" stmtend="498" sqlhandle="0x020000009acce52cc3c4ed8aa368718aca6003ca9866579b0000000000000000000000000000000000000000"> unknown </frame> <frame procname="unknown" line="1" sqlhandle="0x0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000"> unknown </frame> </executionStack> <inputbuf> (@P0 nvarchar(4000),@P1 varbinary(8000),@P2 nvarchar(4000))INSERT INTO UserManagement.SPRING_SESSION_ATTRIBUTES(SESSION_PRIMARY_ID, ATTRIBUTE_NAME, ATTRIBUTE_BYTES) SELECT PRIMARY_ID, @P0, @P1 FROM UserManagement.SPRING_SESSION WHERE SESSION_ID = @P2 </inputbuf> </process> </process-list> <resource-list> <keylock hobtid="72057594057326592" dbid="25" objectname="xyz.UserManagement.SPRING_SESSION" indexname="SPRING_SESSION_IX1" id="lock2963cdbc380" mode="X" associatedObjectId="72057594057326592"> <owner-list> <owner id="process24d550c9088" mode="S" requestType="wait"/> </owner-list> <waiter-list> <waiter id="process2a5b11ae8c8" mode="S" requestType="wait"/> </waiter-list> </keylock> <keylock hobtid="72057594057326592" dbid="25" objectname="xyz.UserManagement.SPRING_SESSION" indexname="SPRING_SESSION_IX1" id="lock2963cdbc380" mode="X" associatedObjectId="72057594057326592"> <owner-list> <owner id="process2770f19d848" mode="X"/> </owner-list> <waiter-list> <waiter id="process24d550c9088" mode="S" requestType="wait"/> </waiter-list> </keylock> <keylock hobtid="72057594057326592" dbid="25" objectname="xyz.UserManagement.SPRING_SESSION" indexname="SPRING_SESSION_IX1" id="lock2a6187ea600" mode="X" associatedObjectId="72057594057326592"> <owner-list> <owner id="process2a5b11ae8c8" mode="X"/> </owner-list> <waiter-list> <waiter id="process2770f19d848" mode="S" requestType="wait"/> </waiter-list> </keylock> </resource-list> </deadlock>

Please let us know your thoughts on this.

[jkuipers]

I ran into this issue as well, which is quite easy to reproduce. I also think I've fixed it, by simply changing the SQL that inserts new attributes to take the primary key of the session directly rather than deriving it from the session ID using a nested select. Based on the deadlock XML that was posted it looks like the deadlock is caused by locks on the unique index on that session ID.
I don't understand why the code doesn't work like this already, as it's simpler and the PK is always known when inserting the attributes.

[jkuipers]

Reproduces this error, plus another bug, here: https://github.com/jkuipers/spring-session-jdbc-sql-server-bugs/

Confirmed that my suggestion fixes the deadlock problem.

The other bug I found is that Spring Session JDBC performs an INSERT when it thinks an attribute was added to the session, while a concurrent request might have added the same attribute already causing the INSERT to fail. This really requires some sort of UPSERT, but there's no standard way to do that in SQL.

[eleftherias]

Thank you for providing a sample @jkuipers.

I also think I've fixed it, by simply changing the SQL that inserts new attributes to take the primary key of the session directly rather than deriving it from the session ID using a nested select.

Could you describe which section of the code you are referring to here?

[jkuipers]

Here's the full class with my edits:

/*
 * Copyright 2014-2019 the original author or authors.
 *
 * Licensed under the Apache License, Version 2.0 (the "License");
 * you may not use this file except in compliance with the License.
 * You may obtain a copy of the License at
 *
 *      https://www.apache.org/licenses/LICENSE-2.0
 *
 * Unless required by applicable law or agreed to in writing, software
 * distributed under the License is distributed on an "AS IS" BASIS,
 * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
 * See the License for the specific language governing permissions and
 * limitations under the License.
 */

package org.springframework.session.jdbc;

import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
import org.springframework.core.convert.ConversionService;
import org.springframework.core.convert.TypeDescriptor;
import org.springframework.core.convert.support.GenericConversionService;
import org.springframework.core.serializer.support.DeserializingConverter;
import org.springframework.core.serializer.support.SerializingConverter;
import org.springframework.dao.DataAccessException;
import org.springframework.jdbc.core.BatchPreparedStatementSetter;
import org.springframework.jdbc.core.JdbcOperations;
import org.springframework.jdbc.core.ResultSetExtractor;
import org.springframework.jdbc.support.lob.DefaultLobHandler;
import org.springframework.jdbc.support.lob.LobHandler;
import org.springframework.session.DelegatingIndexResolver;
import org.springframework.session.FindByIndexNameSessionRepository;
import org.springframework.session.FlushMode;
import org.springframework.session.IndexResolver;
import org.springframework.session.MapSession;
import org.springframework.session.PrincipalNameIndexResolver;
import org.springframework.session.SaveMode;
import org.springframework.session.Session;
import org.springframework.transaction.support.TransactionOperations;
import org.springframework.util.Assert;
import org.springframework.util.StringUtils;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.time.Duration;
import java.time.Instant;
import java.util.ArrayList;
import java.util.Collections;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.Set;
import java.util.UUID;
import java.util.function.Supplier;
import java.util.stream.Collectors;

/**
 * <b>Updated to fix deadlocks in MS SQL Server when concurrently saving new sessions!</b>.
 * <p>
 * A {@link org.springframework.session.SessionRepository} implementation that uses
 * Spring's {@link JdbcOperations} to store sessions in a relational database. This
 * implementation does not support publishing of session events.
 * <p>
 * An example of how to create a new instance can be seen below:
 *
 * <pre class="code">
 * JdbcTemplate jdbcTemplate = new JdbcTemplate();
 *
 * // ... configure jdbcTemplate ...
 *
 * TransactionTemplate transactionTemplate = new TransactionTemplate();
 *
 * // ... configure transactionTemplate ...
 *
 * JdbcIndexedSessionRepository sessionRepository =
 *         new JdbcIndexedSessionRepository(jdbcTemplate, transactionTemplate);
 * </pre>
 *
 * For additional information on how to create and configure {@code JdbcTemplate} and
 * {@code TransactionTemplate}, refer to the <a href=
 * "https://docs.spring.io/spring/docs/current/spring-framework-reference/html/spring-data-tier.html">
 * Spring Framework Reference Documentation</a>.
 * <p>
 * By default, this implementation uses <code>SPRING_SESSION</code> and
 * <code>SPRING_SESSION_ATTRIBUTES</code> tables to store sessions. Note that the table
 * name can be customized using the {@link #setTableName(String)} method. In that case the
 * table used to store attributes will be named using the provided table name, suffixed
 * with <code>_ATTRIBUTES</code>.
 *
 * Depending on your database, the table definition can be described as below:
 *
 * <pre class="code">
 * CREATE TABLE SPRING_SESSION (
 *   PRIMARY_ID CHAR(36) NOT NULL,
 *   SESSION_ID CHAR(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)
 * );
 *
 * CREATE UNIQUE INDEX SPRING_SESSION_IX1 ON SPRING_SESSION (SESSION_ID);
 * CREATE INDEX SPRING_SESSION_IX1 ON SPRING_SESSION (EXPIRY_TIME);
 * CREATE INDEX SPRING_SESSION_IX3 ON SPRING_SESSION (PRINCIPAL_NAME);
 *
 * CREATE TABLE SPRING_SESSION_ATTRIBUTES (
 *  SESSION_PRIMARY_ID CHAR(36) NOT NULL,
 *  ATTRIBUTE_NAME VARCHAR(200) NOT NULL,
 *  ATTRIBUTE_BYTES BYTEA 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
 * );
 *
 * CREATE INDEX SPRING_SESSION_ATTRIBUTES_IX1 ON SPRING_SESSION_ATTRIBUTES (SESSION_PRIMARY_ID);
 * </pre>
 *
 * Due to the differences between the various database vendors, especially when it comes
 * to storing binary data, make sure to use SQL script specific to your database. Scripts
 * for most major database vendors are packaged as
 * <code>org/springframework/session/jdbc/schema-*.sql</code>, where <code>*</code> is the
 * target database type.
 *
 * @author Vedran Pavic
 * @author Craig Andrews
 * @since 2.2.0
 */
public class JdbcIndexedSessionRepository
		implements FindByIndexNameSessionRepository<JdbcIndexedSessionRepository.JdbcSession> {

	/**
	 * The default name of database table used by Spring Session to store sessions.
	 */
	public static final String DEFAULT_TABLE_NAME = "SPRING_SESSION";

	private static final String SPRING_SECURITY_CONTEXT = "SPRING_SECURITY_CONTEXT";

	// @formatter:off
	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 (?, ?, ?, ?, ?, ?, ?)";
	// @formatter:on

	// @formatter:off
	// FIX Joris: updated to take session primary key directly, rather than session ID with nested select
	private static final String CREATE_SESSION_ATTRIBUTE_QUERY = "INSERT INTO %TABLE_NAME%_ATTRIBUTES(SESSION_PRIMARY_ID, ATTRIBUTE_NAME, ATTRIBUTE_BYTES) "
			+ "VALUES (?, ?, ?)";
	// @formatter:on

	// @formatter:off
	private static final String GET_SESSION_QUERY = "SELECT S.PRIMARY_ID, S.SESSION_ID, S.CREATION_TIME, S.LAST_ACCESS_TIME, S.MAX_INACTIVE_INTERVAL, SA.ATTRIBUTE_NAME, SA.ATTRIBUTE_BYTES "
			+ "FROM %TABLE_NAME% S "
			+ "LEFT OUTER JOIN %TABLE_NAME%_ATTRIBUTES SA ON S.PRIMARY_ID = SA.SESSION_PRIMARY_ID "
			+ "WHERE S.SESSION_ID = ?";
	// @formatter:on

	// @formatter:off
	private static final String UPDATE_SESSION_QUERY = "UPDATE %TABLE_NAME% SET SESSION_ID = ?, LAST_ACCESS_TIME = ?, MAX_INACTIVE_INTERVAL = ?, EXPIRY_TIME = ?, PRINCIPAL_NAME = ? "
			+ "WHERE PRIMARY_ID = ?";
	// @formatter:on

	// @formatter:off
	private static final String UPDATE_SESSION_ATTRIBUTE_QUERY = "UPDATE %TABLE_NAME%_ATTRIBUTES SET ATTRIBUTE_BYTES = ? "
			+ "WHERE SESSION_PRIMARY_ID = ? "
			+ "AND ATTRIBUTE_NAME = ?";
	// @formatter:on

	// @formatter:off
	private static final String DELETE_SESSION_ATTRIBUTE_QUERY = "DELETE FROM %TABLE_NAME%_ATTRIBUTES "
			+ "WHERE SESSION_PRIMARY_ID = ? "
			+ "AND ATTRIBUTE_NAME = ?";
	// @formatter:on

	// @formatter:off
	private static final String DELETE_SESSION_QUERY = "DELETE FROM %TABLE_NAME% "
			+ "WHERE SESSION_ID = ?";
	// @formatter:on

	// @formatter:off
	private static final String LIST_SESSIONS_BY_PRINCIPAL_NAME_QUERY = "SELECT S.PRIMARY_ID, S.SESSION_ID, S.CREATION_TIME, S.LAST_ACCESS_TIME, S.MAX_INACTIVE_INTERVAL, SA.ATTRIBUTE_NAME, SA.ATTRIBUTE_BYTES "
			+ "FROM %TABLE_NAME% S "
			+ "LEFT OUTER JOIN %TABLE_NAME%_ATTRIBUTES SA ON S.PRIMARY_ID = SA.SESSION_PRIMARY_ID "
			+ "WHERE S.PRINCIPAL_NAME = ?";
	// @formatter:on

	// @formatter:off
	private static final String DELETE_SESSIONS_BY_EXPIRY_TIME_QUERY = "DELETE FROM %TABLE_NAME% "
			+ "WHERE EXPIRY_TIME < ?";
	// @formatter:on

	private static final Log logger = LogFactory.getLog(JdbcIndexedSessionRepository.class);

	private final JdbcOperations jdbcOperations;

	private final TransactionOperations transactionOperations;

	private final ResultSetExtractor<List<JdbcSession>> extractor = new SessionResultSetExtractor();

	/**
	 * The name of database table used by Spring Session to store sessions.
	 */
	private String tableName = DEFAULT_TABLE_NAME;

	private String createSessionQuery;

	private String createSessionAttributeQuery;

	private String getSessionQuery;

	private String updateSessionQuery;

	private String updateSessionAttributeQuery;

	private String deleteSessionAttributeQuery;

	private String deleteSessionQuery;

	private String listSessionsByPrincipalNameQuery;

	private String deleteSessionsByExpiryTimeQuery;

	/**
	 * If non-null, this value is used to override the default value for
	 * {@link JdbcSession#setMaxInactiveInterval(Duration)}.
	 */
	private Integer defaultMaxInactiveInterval;

	private IndexResolver<Session> indexResolver = new DelegatingIndexResolver<>(new PrincipalNameIndexResolver<>());

	private ConversionService conversionService = createDefaultConversionService();

	private LobHandler lobHandler = new DefaultLobHandler();

	private FlushMode flushMode = FlushMode.ON_SAVE;

	private SaveMode saveMode = SaveMode.ON_SET_ATTRIBUTE;

	/**
	 * Create a new {@link JdbcIndexedSessionRepository} instance which uses the provided
	 * {@link JdbcOperations} and {@link TransactionOperations} to manage sessions.
	 * @param jdbcOperations the {@link JdbcOperations} to use
	 * @param transactionOperations the {@link TransactionOperations} to use
	 */
	public JdbcIndexedSessionRepository(JdbcOperations jdbcOperations, TransactionOperations transactionOperations) {
		Assert.notNull(jdbcOperations, "jdbcOperations must not be null");
		Assert.notNull(transactionOperations, "transactionOperations must not be null");
		this.jdbcOperations = jdbcOperations;
		this.transactionOperations = transactionOperations;
		prepareQueries();
	}

	/**
	 * Set the name of database table used to store sessions.
	 * @param tableName the database table name
	 */
	public void setTableName(String tableName) {
		Assert.hasText(tableName, "Table name must not be empty");
		this.tableName = tableName.trim();
		prepareQueries();
	}

	/**
	 * Set the custom SQL query used to create the session.
	 * @param createSessionQuery the SQL query string
	 */
	public void setCreateSessionQuery(String createSessionQuery) {
		Assert.hasText(createSessionQuery, "Query must not be empty");
		this.createSessionQuery = createSessionQuery;
	}

	/**
	 * Set the custom SQL query used to create the session attribute.
	 * @param createSessionAttributeQuery the SQL query string
	 */
	public void setCreateSessionAttributeQuery(String createSessionAttributeQuery) {
		Assert.hasText(createSessionAttributeQuery, "Query must not be empty");
		this.createSessionAttributeQuery = createSessionAttributeQuery;
	}

	/**
	 * Set the custom SQL query used to retrieve the session.
	 * @param getSessionQuery the SQL query string
	 */
	public void setGetSessionQuery(String getSessionQuery) {
		Assert.hasText(getSessionQuery, "Query must not be empty");
		this.getSessionQuery = getSessionQuery;
	}

	/**
	 * Set the custom SQL query used to update the session.
	 * @param updateSessionQuery the SQL query string
	 */
	public void setUpdateSessionQuery(String updateSessionQuery) {
		Assert.hasText(updateSessionQuery, "Query must not be empty");
		this.updateSessionQuery = updateSessionQuery;
	}

	/**
	 * Set the custom SQL query used to update the session attribute.
	 * @param updateSessionAttributeQuery the SQL query string
	 */
	public void setUpdateSessionAttributeQuery(String updateSessionAttributeQuery) {
		Assert.hasText(updateSessionAttributeQuery, "Query must not be empty");
		this.updateSessionAttributeQuery = updateSessionAttributeQuery;
	}

	/**
	 * Set the custom SQL query used to delete the session attribute.
	 * @param deleteSessionAttributeQuery the SQL query string
	 */
	public void setDeleteSessionAttributeQuery(String deleteSessionAttributeQuery) {
		Assert.hasText(deleteSessionAttributeQuery, "Query must not be empty");
		this.deleteSessionAttributeQuery = deleteSessionAttributeQuery;
	}

	/**
	 * Set the custom SQL query used to delete the session.
	 * @param deleteSessionQuery the SQL query string
	 */
	public void setDeleteSessionQuery(String deleteSessionQuery) {
		Assert.hasText(deleteSessionQuery, "Query must not be empty");
		this.deleteSessionQuery = deleteSessionQuery;
	}

	/**
	 * Set the custom SQL query used to retrieve the sessions by principal name.
	 * @param listSessionsByPrincipalNameQuery the SQL query string
	 */
	public void setListSessionsByPrincipalNameQuery(String listSessionsByPrincipalNameQuery) {
		Assert.hasText(listSessionsByPrincipalNameQuery, "Query must not be empty");
		this.listSessionsByPrincipalNameQuery = listSessionsByPrincipalNameQuery;
	}

	/**
	 * Set the custom SQL query used to delete the sessions by last access time.
	 * @param deleteSessionsByExpiryTimeQuery the SQL query string
	 */
	public void setDeleteSessionsByExpiryTimeQuery(String deleteSessionsByExpiryTimeQuery) {
		Assert.hasText(deleteSessionsByExpiryTimeQuery, "Query must not be empty");
		this.deleteSessionsByExpiryTimeQuery = deleteSessionsByExpiryTimeQuery;
	}

	/**
	 * Set the maximum inactive interval in seconds between requests before newly created
	 * sessions will be invalidated. A negative time indicates that the session will never
	 * timeout. The default is 1800 (30 minutes).
	 * @param defaultMaxInactiveInterval the maximum inactive interval in seconds
	 */
	public void setDefaultMaxInactiveInterval(Integer defaultMaxInactiveInterval) {
		this.defaultMaxInactiveInterval = defaultMaxInactiveInterval;
	}

	/**
	 * Set the {@link IndexResolver} to use.
	 * @param indexResolver the index resolver
	 */
	public void setIndexResolver(IndexResolver<Session> indexResolver) {
		Assert.notNull(indexResolver, "indexResolver cannot be null");
		this.indexResolver = indexResolver;
	}

	public void setLobHandler(LobHandler lobHandler) {
		Assert.notNull(lobHandler, "LobHandler must not be null");
		this.lobHandler = lobHandler;
	}

	/**
	 * Sets the {@link ConversionService} to use.
	 * @param conversionService the converter to set
	 */
	public void setConversionService(ConversionService conversionService) {
		Assert.notNull(conversionService, "conversionService must not be null");
		this.conversionService = conversionService;
	}

	/**
	 * Set the flush mode. Default is {@link FlushMode#ON_SAVE}.
	 * @param flushMode the flush mode
	 */
	public void setFlushMode(FlushMode flushMode) {
		Assert.notNull(flushMode, "flushMode must not be null");
		this.flushMode = flushMode;
	}

	/**
	 * Set the save mode.
	 * @param saveMode the save mode
	 */
	public void setSaveMode(SaveMode saveMode) {
		Assert.notNull(saveMode, "saveMode must not be null");
		this.saveMode = saveMode;
	}

	@Override
	public JdbcSession createSession() {
		MapSession delegate = new MapSession();
		if (this.defaultMaxInactiveInterval != null) {
			delegate.setMaxInactiveInterval(Duration.ofSeconds(this.defaultMaxInactiveInterval));
		}
		JdbcSession session = new JdbcSession(delegate, UUID.randomUUID().toString(), true);
		session.flushIfRequired();
		return session;
	}

	@Override
	public void save(final JdbcSession session) {
		session.save();
	}

	@Override
	public JdbcSession findById(final String id) {
		final JdbcSession session = this.transactionOperations.execute((status) -> {
			List<JdbcSession> sessions = JdbcIndexedSessionRepository.this.jdbcOperations.query(
					JdbcIndexedSessionRepository.this.getSessionQuery, (ps) -> ps.setString(1, id),
					JdbcIndexedSessionRepository.this.extractor);
			if (sessions.isEmpty()) {
				return null;
			}
			return sessions.get(0);
		});

		if (session != null) {
			if (session.isExpired()) {
				deleteById(id);
			}
			else {
				return session;
			}
		}
		return null;
	}

	@Override
	public void deleteById(final String id) {
		this.transactionOperations.executeWithoutResult((status) -> JdbcIndexedSessionRepository.this.jdbcOperations
				.update(JdbcIndexedSessionRepository.this.deleteSessionQuery, id));
	}

	@Override
	public Map<String, JdbcSession> findByIndexNameAndIndexValue(String indexName, final String indexValue) {
		if (!PRINCIPAL_NAME_INDEX_NAME.equals(indexName)) {
			return Collections.emptyMap();
		}

		List<JdbcSession> sessions = this.transactionOperations
				.execute((status) -> JdbcIndexedSessionRepository.this.jdbcOperations.query(
						JdbcIndexedSessionRepository.this.listSessionsByPrincipalNameQuery,
						(ps) -> ps.setString(1, indexValue), JdbcIndexedSessionRepository.this.extractor));

		Map<String, JdbcSession> sessionMap = new HashMap<>(sessions.size());

		for (JdbcSession session : sessions) {
			sessionMap.put(session.getId(), session);
		}

		return sessionMap;
	}

	private void insertSessionAttributes(JdbcSession session, List<String> attributeNames) {
		Assert.notEmpty(attributeNames, "attributeNames must not be null or empty");
		if (attributeNames.size() > 1) {
			this.jdbcOperations.batchUpdate(this.createSessionAttributeQuery, new BatchPreparedStatementSetter() {

				@Override
				public void setValues(PreparedStatement ps, int i) throws SQLException {
					// FIX Joris: use primary key rather than session ID, and move it to the front
					ps.setString(1, session.primaryKey);
					String attributeName = attributeNames.get(i);
					ps.setString(2, attributeName);
					getLobHandler().getLobCreator().setBlobAsBytes(ps, 3,
							serialize(session.getAttribute(attributeName)));
				}

				@Override
				public int getBatchSize() {
					return attributeNames.size();
				}

			});
		}
		else {
			this.jdbcOperations.update(this.createSessionAttributeQuery, (ps) -> {
				// FIX Joris: use primary key rather than session ID, and move it to the front
				ps.setString(1, session.primaryKey);
				String attributeName = attributeNames.get(0);
				ps.setString(2, attributeName);
				getLobHandler().getLobCreator().setBlobAsBytes(ps, 3, serialize(session.getAttribute(attributeName)));
			});
		}
	}

	private void updateSessionAttributes(JdbcSession session, List<String> attributeNames) {
		Assert.notEmpty(attributeNames, "attributeNames must not be null or empty");
		if (attributeNames.size() > 1) {
			this.jdbcOperations.batchUpdate(this.updateSessionAttributeQuery, new BatchPreparedStatementSetter() {

				@Override
				public void setValues(PreparedStatement ps, int i) throws SQLException {
					String attributeName = attributeNames.get(i);
					getLobHandler().getLobCreator().setBlobAsBytes(ps, 1,
							serialize(session.getAttribute(attributeName)));
					ps.setString(2, session.primaryKey);
					ps.setString(3, attributeName);
				}

				@Override
				public int getBatchSize() {
					return attributeNames.size();
				}

			});
		}
		else {
			this.jdbcOperations.update(this.updateSessionAttributeQuery, (ps) -> {
				String attributeName = attributeNames.get(0);
				getLobHandler().getLobCreator().setBlobAsBytes(ps, 1, serialize(session.getAttribute(attributeName)));
				ps.setString(2, session.primaryKey);
				ps.setString(3, attributeName);
			});
		}
	}

	private void deleteSessionAttributes(JdbcSession session, List<String> attributeNames) {
		Assert.notEmpty(attributeNames, "attributeNames must not be null or empty");
		if (attributeNames.size() > 1) {
			this.jdbcOperations.batchUpdate(this.deleteSessionAttributeQuery, new BatchPreparedStatementSetter() {

				@Override
				public void setValues(PreparedStatement ps, int i) throws SQLException {
					String attributeName = attributeNames.get(i);
					ps.setString(1, session.primaryKey);
					ps.setString(2, attributeName);
				}

				@Override
				public int getBatchSize() {
					return attributeNames.size();
				}

			});
		}
		else {
			this.jdbcOperations.update(this.deleteSessionAttributeQuery, (ps) -> {
				String attributeName = attributeNames.get(0);
				ps.setString(1, session.primaryKey);
				ps.setString(2, attributeName);
			});
		}
	}

	public void cleanUpExpiredSessions() {
		Integer deletedCount = this.transactionOperations
				.execute((status) -> JdbcIndexedSessionRepository.this.jdbcOperations.update(
						JdbcIndexedSessionRepository.this.deleteSessionsByExpiryTimeQuery, System.currentTimeMillis()));

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

	private static GenericConversionService createDefaultConversionService() {
		GenericConversionService converter = new GenericConversionService();
		converter.addConverter(Object.class, byte[].class, new SerializingConverter());
		converter.addConverter(byte[].class, Object.class, new DeserializingConverter());
		return converter;
	}

	private String getQuery(String base) {
		return StringUtils.replace(base, "%TABLE_NAME%", this.tableName);
	}

	private void prepareQueries() {
		this.createSessionQuery = getQuery(CREATE_SESSION_QUERY);
		this.createSessionAttributeQuery = getQuery(CREATE_SESSION_ATTRIBUTE_QUERY);
		this.getSessionQuery = getQuery(GET_SESSION_QUERY);
		this.updateSessionQuery = getQuery(UPDATE_SESSION_QUERY);
		this.updateSessionAttributeQuery = getQuery(UPDATE_SESSION_ATTRIBUTE_QUERY);
		this.deleteSessionAttributeQuery = getQuery(DELETE_SESSION_ATTRIBUTE_QUERY);
		this.deleteSessionQuery = getQuery(DELETE_SESSION_QUERY);
		this.listSessionsByPrincipalNameQuery = getQuery(LIST_SESSIONS_BY_PRINCIPAL_NAME_QUERY);
		this.deleteSessionsByExpiryTimeQuery = getQuery(DELETE_SESSIONS_BY_EXPIRY_TIME_QUERY);
	}

	private LobHandler getLobHandler() {
		return this.lobHandler;
	}

	private byte[] serialize(Object object) {
		return (byte[]) this.conversionService.convert(object, TypeDescriptor.valueOf(Object.class),
				TypeDescriptor.valueOf(byte[].class));
	}

	private Object deserialize(byte[] bytes) {
		return this.conversionService.convert(bytes, TypeDescriptor.valueOf(byte[].class),
				TypeDescriptor.valueOf(Object.class));
	}

	private enum DeltaValue {

		ADDED, UPDATED, REMOVED

	}

	private static <T> Supplier<T> value(T value) {
		return (value != null) ? () -> value : null;
	}

	private static <T> Supplier<T> lazily(Supplier<T> supplier) {
		Supplier<T> lazySupplier = new Supplier<T>() {

			private T value;

			@Override
			public T get() {
				if (this.value == null) {
					this.value = supplier.get();
				}
				return this.value;
			}

		};

		return (supplier != null) ? lazySupplier : null;
	}

	/**
	 * The {@link Session} to use for {@link JdbcIndexedSessionRepository}.
	 *
	 * @author Vedran Pavic
	 */
	final class JdbcSession implements Session {

		private final Session delegate;

		private final String primaryKey;

		private boolean isNew;

		private boolean changed;

		private Map<String, DeltaValue> delta = new HashMap<>();

		JdbcSession(MapSession delegate, String primaryKey, boolean isNew) {
			this.delegate = delegate;
			this.primaryKey = primaryKey;
			this.isNew = isNew;
			if (this.isNew || (JdbcIndexedSessionRepository.this.saveMode == SaveMode.ALWAYS)) {
				getAttributeNames().forEach((attributeName) -> this.delta.put(attributeName, DeltaValue.UPDATED));
			}
		}

		boolean isNew() {
			return this.isNew;
		}

		boolean isChanged() {
			return this.changed;
		}

		Map<String, DeltaValue> getDelta() {
			return this.delta;
		}

		void clearChangeFlags() {
			this.isNew = false;
			this.changed = false;
			this.delta.clear();
		}

		Instant getExpiryTime() {
			return getLastAccessedTime().plus(getMaxInactiveInterval());
		}

		@Override
		public String getId() {
			return this.delegate.getId();
		}

		@Override
		public String changeSessionId() {
			this.changed = true;
			return this.delegate.changeSessionId();
		}

		@Override
		public <T> T getAttribute(String attributeName) {
			Supplier<T> supplier = this.delegate.getAttribute(attributeName);
			if (supplier == null) {
				return null;
			}
			T attributeValue = supplier.get();
			if (attributeValue != null
					&& JdbcIndexedSessionRepository.this.saveMode.equals(SaveMode.ON_GET_ATTRIBUTE)) {
				this.delta.put(attributeName, DeltaValue.UPDATED);
			}
			return attributeValue;
		}

		@Override
		public Set<String> getAttributeNames() {
			return this.delegate.getAttributeNames();
		}

		@Override
		public void setAttribute(String attributeName, Object attributeValue) {
			boolean attributeExists = (this.delegate.getAttribute(attributeName) != null);
			boolean attributeRemoved = (attributeValue == null);
			if (!attributeExists && attributeRemoved) {
				return;
			}
			if (attributeExists) {
				if (attributeRemoved) {
					this.delta.merge(attributeName, DeltaValue.REMOVED,
							(oldDeltaValue, deltaValue) -> (oldDeltaValue == DeltaValue.ADDED) ? null : deltaValue);
				}
				else {
					this.delta.merge(attributeName, DeltaValue.UPDATED, (oldDeltaValue,
							deltaValue) -> (oldDeltaValue == DeltaValue.ADDED) ? oldDeltaValue : deltaValue);
				}
			}
			else {
				this.delta.merge(attributeName, DeltaValue.ADDED, (oldDeltaValue,
						deltaValue) -> (oldDeltaValue == DeltaValue.ADDED) ? oldDeltaValue : DeltaValue.UPDATED);
			}
			this.delegate.setAttribute(attributeName, value(attributeValue));
			if (PRINCIPAL_NAME_INDEX_NAME.equals(attributeName) || SPRING_SECURITY_CONTEXT.equals(attributeName)) {
				this.changed = true;
			}
			flushIfRequired();
		}

		@Override
		public void removeAttribute(String attributeName) {
			setAttribute(attributeName, null);
		}

		@Override
		public Instant getCreationTime() {
			return this.delegate.getCreationTime();
		}

		@Override
		public void setLastAccessedTime(Instant lastAccessedTime) {
			this.delegate.setLastAccessedTime(lastAccessedTime);
			this.changed = true;
			flushIfRequired();
		}

		@Override
		public Instant getLastAccessedTime() {
			return this.delegate.getLastAccessedTime();
		}

		@Override
		public void setMaxInactiveInterval(Duration interval) {
			this.delegate.setMaxInactiveInterval(interval);
			this.changed = true;
			flushIfRequired();
		}

		@Override
		public Duration getMaxInactiveInterval() {
			return this.delegate.getMaxInactiveInterval();
		}

		@Override
		public boolean isExpired() {
			return this.delegate.isExpired();
		}

		private void flushIfRequired() {
			if (JdbcIndexedSessionRepository.this.flushMode == FlushMode.IMMEDIATE) {
				save();
			}
		}

		private void save() {
			if (this.isNew) {
				JdbcIndexedSessionRepository.this.transactionOperations.executeWithoutResult((status) -> {
					Map<String, String> indexes = JdbcIndexedSessionRepository.this.indexResolver
							.resolveIndexesFor(JdbcSession.this);
					JdbcIndexedSessionRepository.this.jdbcOperations
							.update(JdbcIndexedSessionRepository.this.createSessionQuery, (ps) -> {
								ps.setString(1, JdbcSession.this.primaryKey);
								ps.setString(2, getId());
								ps.setLong(3, getCreationTime().toEpochMilli());
								ps.setLong(4, getLastAccessedTime().toEpochMilli());
								ps.setInt(5, (int) getMaxInactiveInterval().getSeconds());
								ps.setLong(6, getExpiryTime().toEpochMilli());
								ps.setString(7, indexes.get(PRINCIPAL_NAME_INDEX_NAME));
							});
					Set<String> attributeNames = getAttributeNames();
					if (!attributeNames.isEmpty()) {
						insertSessionAttributes(JdbcSession.this, new ArrayList<>(attributeNames));
					}
				});
			}
			else {
				JdbcIndexedSessionRepository.this.transactionOperations.executeWithoutResult((status) -> {
					if (JdbcSession.this.changed) {
						Map<String, String> indexes = JdbcIndexedSessionRepository.this.indexResolver
								.resolveIndexesFor(JdbcSession.this);
						JdbcIndexedSessionRepository.this.jdbcOperations
								.update(JdbcIndexedSessionRepository.this.updateSessionQuery, (ps) -> {
									ps.setString(1, getId());
									ps.setLong(2, getLastAccessedTime().toEpochMilli());
									ps.setInt(3, (int) getMaxInactiveInterval().getSeconds());
									ps.setLong(4, getExpiryTime().toEpochMilli());
									ps.setString(5, indexes.get(PRINCIPAL_NAME_INDEX_NAME));
									ps.setString(6, JdbcSession.this.primaryKey);
								});
					}
					List<String> addedAttributeNames = JdbcSession.this.delta.entrySet().stream()
							.filter((entry) -> entry.getValue() == DeltaValue.ADDED).map(Map.Entry::getKey)
							.collect(Collectors.toList());
					if (!addedAttributeNames.isEmpty()) {
						insertSessionAttributes(JdbcSession.this, addedAttributeNames);
					}
					List<String> updatedAttributeNames = JdbcSession.this.delta.entrySet().stream()
							.filter((entry) -> entry.getValue() == DeltaValue.UPDATED).map(Map.Entry::getKey)
							.collect(Collectors.toList());
					if (!updatedAttributeNames.isEmpty()) {
						updateSessionAttributes(JdbcSession.this, updatedAttributeNames);
					}
					List<String> removedAttributeNames = JdbcSession.this.delta.entrySet().stream()
							.filter((entry) -> entry.getValue() == DeltaValue.REMOVED).map(Map.Entry::getKey)
							.collect(Collectors.toList());
					if (!removedAttributeNames.isEmpty()) {
						deleteSessionAttributes(JdbcSession.this, removedAttributeNames);
					}
				});
			}
			clearChangeFlags();
		}

	}

	private class SessionResultSetExtractor implements ResultSetExtractor<List<JdbcSession>> {

		@Override
		public List<JdbcSession> extractData(ResultSet rs) throws SQLException, DataAccessException {
			List<JdbcSession> sessions = new ArrayList<>();
			while (rs.next()) {
				String id = rs.getString("SESSION_ID");
				JdbcSession session;
				if (sessions.size() > 0 && getLast(sessions).getId().equals(id)) {
					session = getLast(sessions);
				}
				else {
					MapSession delegate = new MapSession(id);
					String primaryKey = rs.getString("PRIMARY_ID");
					delegate.setCreationTime(Instant.ofEpochMilli(rs.getLong("CREATION_TIME")));
					delegate.setLastAccessedTime(Instant.ofEpochMilli(rs.getLong("LAST_ACCESS_TIME")));
					delegate.setMaxInactiveInterval(Duration.ofSeconds(rs.getInt("MAX_INACTIVE_INTERVAL")));
					session = new JdbcSession(delegate, primaryKey, false);
				}
				String attributeName = rs.getString("ATTRIBUTE_NAME");
				if (attributeName != null) {
					byte[] bytes = getLobHandler().getBlobAsBytes(rs, "ATTRIBUTE_BYTES");
					session.delegate.setAttribute(attributeName, lazily(() -> deserialize(bytes)));
				}
				sessions.add(session);
			}
			return sessions;
		}

		private JdbcSession getLast(List<JdbcSession> sessions) {
			return sessions.get(sessions.size() - 1);
		}

	}

}

[jkuipers]

BTW, in case this wasn't clear already: please consider to incorporate the change. I'll create a separate issue for the other bug.

This change is breaking for people defining their own query for the insert attributes, as the parameter order changed, but honestly I don't think anyone does that and it would be trivial for them to switch the order in their own query. OTOH it does fix a showstopping bug that happens as soon as two people start using you app with SQL Server.

[eleftherias]

Thank you @jkuipers.
We will look into incorporating this change into our codebase.

[jkuipers]

I didn't include a license in my repo with the TestContainers test, but feel free to incorporate that one as well. The other test there will still fail, but that's more of a design flaw in spring-session-jdbc which seems to believe it has exclusive access to the session state from a single thread and can therefore derive if an INSERT or UPDATE should be used.

[vpavic]

Hi @jkuipers,

I don't understand why the code doesn't work like this already, as it's simpler and the PK is always known when inserting the attributes.

See #1031 for background on why this INSERT statement was changed to be based on session id, instead of primary key. We've had several users affected by concurrency issues and this change was made to address those.

The other bug I found is that Spring Session JDBC performs an INSERT when it thinks an attribute was added to the session, while a concurrent request might have added the same attribute already causing the INSERT to fail. This really requires some sort of UPSERT, but there's no standard way to do that in SQL.

See #1213.

I'll try to take a closer look over the next couple of days.
We need to be careful about introducing changes like this as are likely to impact scenarios involving concurrency, as seen from the linked issues.

[jkuipers]

I see: that's tricky, as the current code with the DB schema it provides makes spring-session-jdbc pretty much unusable on SQL Server (can't even have two users concurrently doing smth with their own session). The deadlock graph that was provided here shows the problem is with the unique index on the session table, which is updated in the same transaction that then performs the insert into the attributes using the nested select. Maybe some SQL Server specific change of index type could work around the issue, but I'm not a SQL Server DBA.

In general the whole approach taken by spring-session-jdbc seems to be a tricky one: it needs to reason about what changes are made to an existing session so that it knows how to update the DB, but doesn't account for concurrent updates (which could involve deletions of attributes or the session itself).
In the case of #1031, it looks like the code might simply have to catch the exception that indicates that the session no longer exists when trying to perform a change and accept that the session won't be updated then. I understand why there's a need to separate the primary key and session ID, since the session ID is allowed to be changed while still representing the same logical session, but it's weird that code should do the reverse and look up sessions by their session ID when there's a unique PK that should never change during the lifetime of a single logical session, right?

[pavankjadda]

I am facing similar issue in my project and we are using SQL Server

[mwftapi]

We are encountering a similar issue on our project using the following combination of main Spring Frameworks in conjunction with a MySQL 8.0.20 Server which is run as a docker container, using the official mysql:8.0.20 docker image:

• Spring Boot 2.3.2-RELEASE
• Spring Boot Starter Web, version provided by Spring Boot Dependencies BOM in version 2.3.2-RELEASE
• Spring Boot Starter Security, "
• Spring Boot Starter Data JPA, "
• Spring Session JDBC 2.3.0-RELEASE

We are using the mysql schema for the SPRING_SESSION and SPRING_SESSION_ATTRIBUTES tables which is shipped with Spring Session JDBC.

Also the SessionRepositoryFilter is the very first filter in our filter chain.

Since we are using the default session cleanup cron expression "0 * * * * *" we observed the issue that requests to our server which are happening around the 00 second of each minute might be responding with an error 500. Internally the already mentioned org.springframework.dao.DeadlockLoserDataAccessException is thrown:

2020-08-05 09:57:00.175 �[31mERROR�[m �[35m1�[m --- [nio-8443-exec-4] �[36mo.s.b.w.s.s.ErrorPageFilter             �[m : Forwarding to error page from request [REDACTED] due to exception [PreparedStatementCallback; SQL [UPDATE SPRING_SESSION SET SESSION_ID = ?, LAST_ACCESS_TIME = ?, MAX_INACTIVE_INTERVAL = ?, EXPIRY_TIME = ?, PRINCIPAL_NAME = ? WHERE PRIMARY_ID = ?]; Deadlock found when trying to get lock; try restarting transaction; nested exception is com.mysql.cj.jdbc.exceptions.MySQLTransactionRollbackException: Deadlock found when trying to get lock; try restarting transaction]
org.springframework.dao.DeadlockLoserDataAccessException: PreparedStatementCallback; SQL [UPDATE SPRING_SESSION SET SESSION_ID = ?, LAST_ACCESS_TIME = ?, MAX_INACTIVE_INTERVAL = ?, EXPIRY_TIME = ?, PRINCIPAL_NAME = ? WHERE PRIMARY_ID = ?]; Deadlock found when trying to get lock; try restarting transaction; nested exception is com.mysql.cj.jdbc.exceptions.MySQLTransactionRollbackException: Deadlock found when trying to get lock; try restarting transaction
	at org.springframework.jdbc.support.SQLErrorCodeSQLExceptionTranslator.doTranslate(SQLErrorCodeSQLExceptionTranslator.java:267)
	at org.springframework.jdbc.support.AbstractFallbackSQLExceptionTranslator.translate(AbstractFallbackSQLExceptionTranslator.java:72)
	at org.springframework.jdbc.core.JdbcTemplate.translateException(JdbcTemplate.java:1443)
	at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:633)
	at org.springframework.jdbc.core.JdbcTemplate.update(JdbcTemplate.java:862)
	at org.springframework.jdbc.core.JdbcTemplate.update(JdbcTemplate.java:917)
	at org.springframework.session.jdbc.JdbcIndexedSessionRepository$JdbcSession.lambda$save$10(JdbcIndexedSessionRepository.java:804)
	at org.springframework.transaction.support.TransactionOperations.lambda$executeWithoutResult$0(TransactionOperations.java:68)
	at org.springframework.transaction.support.TransactionTemplate.execute(TransactionTemplate.java:140)
	at org.springframework.transaction.support.TransactionOperations.executeWithoutResult(TransactionOperations.java:67)
	at org.springframework.session.jdbc.JdbcIndexedSessionRepository$JdbcSession.save(JdbcIndexedSessionRepository.java:799)
	at org.springframework.session.jdbc.JdbcIndexedSessionRepository$JdbcSession.access$200(JdbcIndexedSessionRepository.java:631)
	at org.springframework.session.jdbc.JdbcIndexedSessionRepository.save(JdbcIndexedSessionRepository.java:409)
	at org.springframework.session.jdbc.JdbcIndexedSessionRepository.save(JdbcIndexedSessionRepository.java:130)
	at org.springframework.session.web.http.SessionRepositoryFilter$SessionRepositoryRequestWrapper.commitSession(SessionRepositoryFilter.java:225)
	at org.springframework.session.web.http.SessionRepositoryFilter$SessionRepositoryRequestWrapper.access$100(SessionRepositoryFilter.java:192)
	at org.springframework.session.web.http.SessionRepositoryFilter.doFilterInternal(SessionRepositoryFilter.java:144)
	at org.springframework.session.web.http.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:82)
	at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:193)
	at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166)
	at org.springframework.boot.web.servlet.support.ErrorPageFilter.doFilter(ErrorPageFilter.java:128)
	at org.springframework.boot.web.servlet.support.ErrorPageFilter.access$000(ErrorPageFilter.java:66)
	at org.springframework.boot.web.servlet.support.ErrorPageFilter$1.doFilterInternal(ErrorPageFilter.java:103)
	at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:119)
	at org.springframework.boot.web.servlet.support.ErrorPageFilter.doFilter(ErrorPageFilter.java:121)
	at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:193)
	at org.apache.catalina.core.ApplicationFilterChain.doFilter(ApplicationFilterChain.java:166)
	at org.springframework.boot.actuate.metrics.web.servlet.WebMvcMetricsFilter.doFilterInternal(WebMvcMetricsFilter.java:93)
	at org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:119)
	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:202)
	at org.apache.catalina.core.StandardContextValve.invoke(StandardContextValve.java:96)
	at org.apache.catalina.authenticator.AuthenticatorBase.invoke(AuthenticatorBase.java:666)
	at org.apache.catalina.core.StandardHostValve.invoke(StandardHostValve.java:139)
	at org.apache.catalina.valves.ErrorReportValve.invoke(ErrorReportValve.java:92)
	at org.apache.catalina.valves.AbstractAccessLogValve.invoke(AbstractAccessLogValve.java:690)
	at org.apache.catalina.core.StandardEngineValve.invoke(StandardEngineValve.java:74)
	at org.apache.catalina.connector.CoyoteAdapter.service(CoyoteAdapter.java:343)
	at org.apache.coyote.http11.Http11Processor.service(Http11Processor.java:373)
	at org.apache.coyote.AbstractProcessorLight.process(AbstractProcessorLight.java:65)
	at org.apache.coyote.AbstractProtocol$ConnectionHandler.process(AbstractProtocol.java:868)
	at org.apache.tomcat.util.net.NioEndpoint$SocketProcessor.doRun(NioEndpoint.java:1589)
	at org.apache.tomcat.util.net.SocketProcessorBase.run(SocketProcessorBase.java:49)
	at java.base/java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1130)
	at java.base/java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:630)
	at org.apache.tomcat.util.threads.TaskThread$WrappingRunnable.run(TaskThread.java:61)
	at java.base/java.lang.Thread.run(Thread.java:832)
Caused by: com.mysql.cj.jdbc.exceptions.MySQLTransactionRollbackException: Deadlock found when trying to get lock; try restarting transaction
	at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:123)
	at com.mysql.cj.jdbc.exceptions.SQLError.createSQLException(SQLError.java:97)
	at com.mysql.cj.jdbc.exceptions.SQLExceptionsMapping.translateException(SQLExceptionsMapping.java:122)
	at com.mysql.cj.jdbc.ServerPreparedStatement.serverExecute(ServerPreparedStatement.java:637)
	at com.mysql.cj.jdbc.ServerPreparedStatement.executeInternal(ServerPreparedStatement.java:418)
	at com.mysql.cj.jdbc.ClientPreparedStatement.executeUpdateInternal(ClientPreparedStatement.java:1092)
	at com.mysql.cj.jdbc.ClientPreparedStatement.executeUpdateInternal(ClientPreparedStatement.java:1040)
	at com.mysql.cj.jdbc.ClientPreparedStatement.executeLargeUpdate(ClientPreparedStatement.java:1347)
	at com.mysql.cj.jdbc.ClientPreparedStatement.executeUpdate(ClientPreparedStatement.java:1025)
	at com.zaxxer.hikari.pool.ProxyPreparedStatement.executeUpdate(ProxyPreparedStatement.java:61)
	at com.zaxxer.hikari.pool.HikariProxyPreparedStatement.executeUpdate(HikariProxyPreparedStatement.java)
	at jdk.internal.reflect.GeneratedMethodAccessor523.invoke(Unknown Source)
	at java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
	at java.base/java.lang.reflect.Method.invoke(Method.java:564)
	at net.bull.javamelody.JdbcWrapper.doExecute(JdbcWrapper.java:422)
	at net.bull.javamelody.JdbcWrapper$StatementInvocationHandler.invoke(JdbcWrapper.java:142)
	at net.bull.javamelody.JdbcWrapper$DelegatingInvocationHandler.invoke(JdbcWrapper.java:300)
	at com.sun.proxy.$Proxy231.executeUpdate(Unknown Source)
	at org.springframework.jdbc.core.JdbcTemplate.lambda$update$0(JdbcTemplate.java:867)
	at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:617)
	... 44 more

After some debugging we found out that this is triggered when the method getSession(false) is called on a by the SessionRepositoryFilter$SessionRepositoryRequestWrapper wrapped request. When calling this method the sessions LAST_ACCESS_TIME will be tried to be updated in the database thus triggering the query mentioned in the above shown exception message.

Specifically this issue happens in our application as soon as a browser requests static resources in parallel were each request contains the same session. If this is happening around the 00 second of each minute we have the chance to run into the described org.springframework.dao.DeadlockLoserDataAccessException exception.

From our point of view this seems to be a race condition which happens under the following circumstances:

• many parallel requests with the same session (in our cases, requests targeting static resources)
• hitting the server at the time where Spring Session JDBC performs its session cleanup cronjob

In order to mitigate this issue we tried to reduce access on sessions on parallel requests such as those that are aiming for our static resources. We could successfully eliminate those calls in our own code.

The only call to getSession(false) that we could not eliminate is the one that is triggered by the SessionFlashMapManager::retriveFlashMaps(HttpServletRequest) method. This method is called in the DispatcherServleton line 934. Both components are main building blocks of the Spring WebMVC framework.
Unfortunately through that call the issue still persists.

We explored other ways to eliminate this issue, for instance by changing the default session cleanup cron expression, but then again this is only a mitigating workaround, not a solution, since there is still a timing that could be hit by our clients.

Is there any news on this issue?

[vpavic]

There's a PR to address this (see #1726), together with #1213. See the description of that PR for more details.

[vpavic]

@mwftapi, your problem sounds like #838. That's a problem that was reported by MySQL users several time. Please also subscribe to that issue to track further updates.

[vpavic]

Reopening to consider some further improvements based on feedback from @jkuipers.

[Bhasski]

We are also facing the deadlocks in our production set up.
Is there any update on the issue ?
The current version we are using is 3.0.1 and SQL SERVER .