3

Infrequently, instead of my site loading I get a white screen of death with this error message:

Deadlock found when trying to get lock; try restarting transaction SQL=INSERT INTO jom_session (session_id, client_id, guest, time, userid, username) VALUES ('cv95odk14jbe8738ecg9se9de1', 0, 0, '1402423781', 101, 'username@example.com')

I can't duplicate it but I've seen it myself before, and its been reported by other users. Is there anyway to prevent this from happening?

  • It almost seems like mysql is overloaded. Is this a big site? If it is true (not making an answer because I am not confident this is the solution) try to adjust the settings in the my.cnf file to give mysql a bit more breathing room. If its shared hosting contact your host and see if you are hitting any kind of resource ceiling. MySQL can only do so many transactions per second depending on resources, optimizing mysql configuration for the server can help, as the defaults are built for low end machines usually. – Jordan Ramstad Jun 11 '14 at 21:31
  • We're hosting our own database, the max_connections are set well above what we should need, even on a high traffic day. – ContextSwitch Jun 12 '14 at 13:27
  • There are many more settings than max_connections, there are tools for it like github.com/major/MySQLTuner-perl which has given good results for me, but again just a thought that stems from the idea of it being server related, not Joomla. Also make sure to optimize/repair tables. – Jordan Ramstad Jun 12 '14 at 19:30
1

If there is a deadlock, it would indicate that index updates are done from different directions so to speak and there may be a bug in Joomla that causes this. Apart from documenting the symptoms or trying to find the bug you can mitigate the issue by allowing for the session table to be more performant.

In addition to Jordans comment, you can make the session table very efficient by turning it into a MEMORY type MySQL table. This will mean that sessions are lost however on MySQL reboot.

To turn it into a memory table, switch the data field from mediumtext to something like varchar(15000) (size depends on what extensions store what data), then turn the type to MEMORY.

You also need to allow for enough "heap" memory in your MySQL configuration as the table will now be held in memory. For big sites I manage I use up to 640MB.

For all of this you need to have a VPS at least of course.

  • In a multi server environment, would that table need to be put into something like memcached? – ContextSwitch Jun 12 '14 at 13:29
  • Nevermind I get it, why a VPS though? – ContextSwitch Jun 12 '14 at 13:33
  • I just meant in comparison to shared hosting. – jdog Jun 12 '14 at 21:51

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.