search

chill117 / express-mysql-session

A MySQL session store for the express framework in node
MIT License
313 stars 106 forks source link

Getting intermittent deadlocks on regenerateSession #106

Open rabbitfufu opened 4 years ago

rabbitfufu commented 4 years ago

Hello!

Firstly, thank you for taking the time to maintain this library, it has been very helpful to me.

As per the docs, I use an external connection pool (sidorares/mysql2) when I instantiate this library:

const session = require('express-session') 
const MySQLStore = require('express-mysql-session')(session) 
const store = new MySQLStore({ 
    clearExpired: true, 
    checkExpirationInterval: 1000 * 60 * 60, 
    expiration: 1000 * 60 * 60 * 48
}, mysql2Pool)

For the most part it seems to work great.

However, intermittently in my code, when I then call req.session.regnerate() I get the following deadlock:

Error: Lock wait timeout exceeded; try restarting transaction at PromisePool.query (/var/app/current/node_modules/mysql2/promise.js:330:22) at MySQLStore.query (/var/app/current/node_modules/express-mysql-session/index.js:432:33) at MySQLStore.destroy (/var/app/current/node_modules/express-mysql-session/index.js:310:8) at MySQLStore.Store.regenerate (/var/app/current/node_modules/express-session/session/store.js:52:8) at Session.regenerate (/var/app/current/node_modules/express-session/session/session.js:124:25)

Any ideas?

chill117 commented 4 years ago

I think what is happening is a SQL query is running somewhere that is holding a record lock on the record (or many records) in the session table. Then when you execute regenerate on that session, it gives you that lock error.

I suspect that it could be caused by the interval to clear expired sessions - by default this runs every 15 minutes but it looks like you've got it set to run once per hour. Try running your app with clearExpired: false. If the problem continues, then it's likely not the source of the problem.

About how many rows does your sessions table have typically?

OsteoStrongAdmin commented 3 years ago

We have this same issue intermittently. I haven't set any configuration values other than the db+login. Our sessions table has millions of rows - but we only have a few thousand users. Can this be solved via configuration?

nodejs.log innodb.log

chill117 commented 3 years ago

If you have millions of sessions with only a few thousand users, then I suspect you have a very very long expires time (e.g 1 year). If that's the case, then you might try setting the "clearExpired" option to false. And also it might be a good idea to not use sessions for clients (browsers) that are not logged in (guests), unless you maintain state on your server for such guest users.