vpavic
commented
6 years ago 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.
TorosyanV
isalnikov
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 (
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