Sessions table (InnoDB) locked when expired sessions are being deleted

The module does not do an explicit LOCK TABLE. All it does is:

db_query("DELETE FROM {sessions} WHERE timestamp < %d $extra_cond", $timestamp);

However, you may have set a certain variable, such as innodb_lock_wait_timeout too low, or the default of your distro is too low. It is set to 50 on the distro I use (Ubuntu). If the operation of deleting takes longer than 50 seconds, then you may get those errors.

SHOW INNODB STATUS, SHOW VARIABLES and, SHOW PROCESSLIST may be of help when cron is running. You need to catch it in the action.

Increasing the timeout on your LOCK is not a fix IMO, because people visiting the site during that period will just see a hang.

As I understand it, all rows in the table will be locked for SELECT as long as the DELETE query is running. The solution is to reduce the time your DB server spends in DELETE.

The simple but more brutal approach is to simply do TRUNCATE sessions in your DB. This will remove all existing sessions, and log out all currently active users. If the issue is that your sessions table got too fat before you installed Session Expire module, then this may well bring your sessions table to a manageable level, and after that Session Expire will have an easier job keeping things in order.

If that's not an option, a more complex approach which allows you to avoid disturbing "current" sessions is by executing a looped statement. You could time queries like DELETE FROM sessions WHERE timestamp < UNIX_TIMESTAMP()-1*24*60*60 LIMIT x and decide on what an acceptable delay is, then repeat the query for that value of x until you've flushed the table sufficiently.

(1*24*60*60 in the query above represents the number of seconds in a day; to keep sessions up to a week old, use 7*24*60*60, etc.)

If your question is resolved, please take a moment to mark this issue fixed.

There might be a feature request here, then: when enabling Session Expire, check and see the size of the sessions table. If it's "large" (eg, if select count(*) from sessions where sessions.timestamp < unix_timestamp()-7*24*60*60 ; takes more than a second or two to execute) then warn the person installing the module to do a manual cleanup.

Alternatively, we could do something like this in expiry phase:

• Count the number of expirable sessions. Note the time taken to do the COUNT() above as well.
• If the time taken to do the count is large, restrict the number of deletes using LIMIT with some configurable value.
• Warn the site admin that there are enough sessions in the DB table to cause a performance slowdown, and that a throttle is in effect to prevent site outages.
• Recommend alternate processes to purge the sessions table.

#263130: Big session tables can crash SQL server records similar issues experienced by other sites.

#934876: Avoid long {sessions} table locks is the feature request which grew from this issue.