spring-session: Deadlock in SQL Server when INSERT INTO SPRING_SESSION_ATTRIBUTES by JdbcOperationsSessionRepository

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.

About this issue

Original URL
State: open
Created 5 years ago
Reactions: 2
Comments: 16 (12 by maintainers)

Commits related to this issue

Optimize insert attribute statement in JdbcIndexedSessionRepository At present, the SQL statement used to insert a session attribute record contains a nested select statement that verifies the existe... — committed to vpavic/spring-session by vpavic 4 years ago
Optimize insert attribute statement in JdbcIndexedSessionRepository At present, the SQL statement used to insert a session attribute record contains a nested select statement that verifies the existe... — committed to vpavic/spring-session by vpavic 4 years ago
Optimize insert attribute statement in JdbcIndexedSessionRepository At present, the SQL statement used to insert a session attribute record contains a nested select statement that verifies the existe... — committed to vpavic/spring-session by vpavic 4 years ago

Most upvoted comments

@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 on Nov 14, 2020

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?

jkuipers on Jun 23, 2020

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.

vpavic on Jun 23, 2020