Concurrent requests adding the same session attribute result in duplicate key constraint violation #1213
Comments
|
Duplicate of #1031 |
|
I think I incorrectly labeled this as duplicate of #1031 while in fact it is a different problem - apologies for that @ovanekem. Can you provide a stack trace, or even better, a minimal sample to reproduce the problem? Also can you take a look at #1216 and see whether that appears to be the same problem? |
|
@vpavic I'll try to create a sample project this weekend that reproduces the issue. |
|
@vpavic I now have created some test cases that reproduce the issue. Attached is a ZIP file that contains the project. It is a Spring MVC project running on Wildfly and using Spring session with JDBC persistence.
You can also see that doing the same REST calls without the asynchronous flag, we do not have an issue. Regards Olivier |
|
Thanks for following up and providing the sample @ovanekem - I now have the understanding of what's going on. After giving this issue some thought recently, I'm afraid this is a difficult problem to address without making some compromises in other places. We've done quite a few optimizations to Typically, you can address a problem like this in two ways:
Do you have any thoughts on this @rwinch? It's also worth noting that in some cases these problems can be address in application logic - see #1216 reported by @bonhamcm and in particular this comment. |
|
Could you add an option to perform a merge or upsert depending on the database? You can use |
|
We are trying to stick with standard SQL for obvious reasons. There is |
|
We just upgraded and are getting the error below. We have the following from Spring: Spring Core: 5.0.5.RELEASE
|
|
I know you are trying to reduce the number of SQL queries for performance reasons. Looking at the code where we are getting the Exception from: `private void insertSessionAttributes(JdbcSession session, List attributeNames) { // Exception is thrown from this command Unless you do an "Insert or Update" type statement here; you pretty much have to either
If you can do "Insert or Update" great! But you may have to go the route of catching, or checking. |
|
Thanks for following up on this issue @pminearo. As explained in my previous comment, neither of the options is ideal and presents a certain compromise. That's why I'm hesitant to do any of those changes as we would negate some of the optimization efforts that have been made to Is there anything you could do on application level in order to reduce the likelihood of concurrent requests that operate on the same session attribute? |
|
Unfortunately no, it is out of our hands with when AJAX calls get made. We use a product called SmartClient on the front end and don't have much control over when it makes the calls. Plus, having sites ensure only 1 AJAX call is made at a time defeats a lot of the performance gains from making multiple calls at the same time. Since the exception is caught and logged anyways; why would catching an exception and ignoring it be a compromise to performance? Wouldn't that be a slightly better performance because it is not getting logged which slows the performance? Could it be assumed that if Spring Session tries to insert an attribute that already exists; it has the same attribute value? I do not know what is actually being stored, but I would think it would be the same thing. If it is the same BLOB being stored, you could just ignore the exception? If you are worried about not showing an error, you could catch and log as Debug; that way if there is a problem we could turn on Debug Logging and see what it is. The problem we have is we have no control over the logging of the error. This is done in Tomcat. Which, sets off our alerting system ; sending out false-positives. |
That seems like a more likely of the two error handling scenarios described in my previous comment, however to implement that we'd have to drop our batching optimization. |
|
On my side, we indeed solved this from an application point of view. But also in my case I have full control on the client code (which not seems to be the case of @pminearo ). With this in mind, I think a potential workaround implemented in the framework directly would be to do an update when the insert fails. I do not think (but I have not tested ;-)). The risk of course is that the AJAX calls that did the insert in the first place was supposed to come second and this value will be erased, but this is a consequence of the initial bad design I guess. Now we need to check that this does not indeed fails the batch optimization. |
|
A quick update: I pulled down the 2.1.2.RELEASE tag of the cod base. I then edited JdbcOperationsSessionRepository.insertSessionAttributes() to have a try/catch around the 'if/else' statement. Then logged what info I had. What's interesting about the output right now is the only Attribute Name listed was "SPRING_SECURITY_CONTEXT". Even though, when I look in the DB; I see multiple attributes for that user's session. The exception does happen right after they log in; which would make sense as to why there is only 1 attribute being inserted. So, far this quick hack is not causing any side affects. But that does not mean there aren't any. Just means I haven't seen, or heard, any. I will give you more updates as I see them. |
|
I encountered a similar stack on 2.1.2: In happens in the context of spring-oauth2 I initially felt it might be related to #1031 but @vpavic suggests my case might rather be related to this ticket. Given this (production) stack, do you feel this is the same issue ? (I'm not able to pronounce myself). If no, I shall create a dedicated ticket but I doubt I will be able to provide a reproduction case. |
|
By reading this thread, i understand that's It's an issue that is more complicated that it could appears. Even if the smarter SQL need to be stored in my project, I would do it without hesitation, but at least guide on the way to follow to fix this. |
|
I wish #1481 could be reconsidered... imho it's a really good solution for this problem. |
|
It would be really helpful if someone could write up a guide or point to a guide explaining how to eliminate this error. I've been experiencing this in production for a long time and I've just grown tired of my log filling up with these errors. I run spring boot with spring session jdbc, nothing special. It's time to solve this problem once and for all. I appreciate the help! |
|
@hubbaba I'm using this workaround for a while. For DB specific inserts, check candrews's commits. import javax.annotation.PostConstruct;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.boot.autoconfigure.condition.ConditionalOnProperty;
import org.springframework.context.annotation.Configuration;
import org.springframework.session.jdbc.JdbcIndexedSessionRepository;
import org.springframework.util.StringUtils;
/**
* see: <a
* href="https://github.com/spring-projects/spring-session/issues/1213#issuecomment-516466012">When
* you fire multiple AJAX calls to a controller and call setAttribute you can get a ERROR: duplicate
* key value violates unique constraint "spring_session_attributes_pk"</a>
*/
@Configuration
@ConditionalOnProperty(value = "spring.session.use-mysql-specific-insert", havingValue = "true")
public class CustomSessionInsertConfigurator {
private static final String CREATE_SESSION_ATTRIBUTE_QUERY_ON_DUPLICATE_KEY_UPDATE =
"INSERT INTO %TABLE_NAME%_ATTRIBUTES(SESSION_PRIMARY_ID, ATTRIBUTE_NAME, ATTRIBUTE_BYTES) "
+ "SELECT PRIMARY_ID, ?, ? "
+ "FROM %TABLE_NAME% "
+ "WHERE SESSION_ID = ? ON DUPLICATE KEY UPDATE ATTRIBUTE_BYTES=VALUES(ATTRIBUTE_BYTES)";
private final JdbcIndexedSessionRepository originalRepository;
@Autowired
public CustomSessionInsertConfigurator(JdbcIndexedSessionRepository originalRepository) {
this.originalRepository = originalRepository;
}
@PostConstruct
public void customizedJdbcOperationsSessionRepository() {
originalRepository.setCreateSessionAttributeQuery(
StringUtils.replace(
CREATE_SESSION_ATTRIBUTE_QUERY_ON_DUPLICATE_KEY_UPDATE,
"%TABLE_NAME%",
JdbcIndexedSessionRepository.DEFAULT_TABLE_NAME));
}
} |
|
Here's what I ended up using for MS SQL Server to fix both this issue as well as #1550: |
|
Hi, I am getting this same error when I were to click on a submit button multiple times. Is there an official fix to this at the moment? |
This commit provides JdbcIndexedSessionRepository customizers for the following databases: - PostgreSQL - Oracle - MySQL (TODO) - SQL Server (TODO) These customizers are intended to address the concurrency issues occurring on insert of new session attribute by applying database specific SQL upsert/merge statement instead of a generic insert. Closes: spring-projects#1213
|
I've just opened a WIP PR (see #1726) that intends to address this by providing a database specific Once we have that in place, the plan is to propose enhancement to Spring Boot's auto-configuration support for Spring Session so that the appropriate customizer gets registered automatically. |
Why not use #1481 which solves this problem without requiring changes in Spring Boot? |
|
@candrews I believe we went at length about the reasons in the #1481 itself, but for completeness sake I'll only offer a brief recap here:
Spring Boot already detects which database you're working with, and acts on that information in several aspects of its auto-configuration facilities. With that in mind, it should be the best fitting place to (automatically) apply the configuration support we're looking to provide here. Without Spring Boot, it will be just a matter of registering a single bean in order to opt into a more optimized, database specific SQL. |
This commit provides JdbcIndexedSessionRepository customizers for the following databases: - PostgreSQL - MySQL - Oracle - SQL Server (TODO) These customizers are intended to address the concurrency issues occurring on insert of new session attribute by applying database specific SQL upsert/merge statement instead of a generic insert. Closes: spring-projects#1213
This commit provides JdbcIndexedSessionRepository customizers for the following databases: - PostgreSQL - MySQL - Oracle - SQL Server (TODO) These customizers are intended to address the concurrency issues occurring on insert of new session attribute by applying database specific SQL upsert/merge statement instead of a generic insert. Closes: spring-projects#1213
This commit provides JdbcIndexedSessionRepository customizers for the following SQL dialects: - Standard SQL (used by IBM DB2, Oracle, SQL Server) - PostgreSQL - MySQL (also used by MariaDB) These customizers are intended to address the concurrency issues occurring on insert of new session attribute by applying SQL dialect specific SQL upsert/merge statement instead of a generic insert. Closes: spring-projects#1213
This commit provides JdbcIndexedSessionRepository customizers for the following SQL dialects: - PostgreSQL - MySQL (also used by MariaDB) - SQL Server - IBM DB2 - Oracle These customizers are intended to address the concurrency issues occurring on insert of new session attribute by applying SQL dialect specific SQL upsert/merge statement instead of a generic insert. Closes: spring-projects#1213
|
I too encountered this issue on Spring Boot 2.7.12. A somewhat mysterious circumstance is that it only started manifesting itself after Spring WebFlux was integrated for streaming large data from a WebClient response directly to the Anyway, I looked up the Autoconfiguration code for Spring Session with JDBC and the specific customizers don't seem to have been picked up yet. Any idea when we can expect this? Of course I can already use the appropriate Customizer in my own configuration, however, I think it would be helpful for other lost souls to know whether the issue can be fixed by just updating to a newer version of Spring Boot. |
|
If you are using MySQL, following steps below to fix the DuplicateKeyException: INSERT INTO SPRING_SESSION_ATTRIBUTES triggered by concurrent requests:
Hope this tip helps. |
We are using Spring session 2.0.5.RELEASE. We are using JDBC session persistence.
We have migrated those applications that were previously using container in memory session (Wildfly/Undertow). The code has stayed as-is.
We have html pages that fire up multiple calls in AJAX simultaneously to a controller.
In the controller we have code that set an attribute into the session.
The issue is that multiple threads of the application server are busy in parallel treating those AJAX calls, the first thread will insert the new attribute in the database. The other threads are then supposed to call an update of the attribute in the database because the attribute already exists. But the issue is that the other threads have not detected (yet) that the attribute already exists and try to do an insert as well resulting in a duplicate key exception.
I must say that as a workaround we have patched the application to do a sort of initialisation AJAX call at page load and this initialisation just set the attribute with a blank value, this allows the attribute row to be created and when further calls are being made in parallel, the JDBC implementation of Spring session sees that only updates are needed.
However I wanted to report this in order to support those use cases natively (without workarounds) using JDBC persistence.
The text was updated successfully, but these errors were encountered: