Tech Note: Live Comments, 2
I had to take down the front page live comment updates for a couple of days to figure out why MySQL was deadlocking. We all know how painful that can be.
Turns out that MyISAM tables suffer from an obscure problem related to concurrent reads and writes, that’s exacerbated when the SELECT query uses a GROUP BY clause. The comments at LGF need to be stored in MyISAM tables in order to allow the use of FULLTEXT indexing, which greatly helps with searching. But the Ajax DB query that periodically checked for new comments in each front page thread was causing that deadlock condition.
The solution was to use a separate table in InnoDB format (which doesn’t have the concurrent deadlocking problem) that is updated every time a comment is posted, with just the info needed to display the live comments on the front page. This ends up being much faster as well, which is a good thing since the code runs in every logged-in browser, every 15 seconds. It’s been running for over an hour with no sign of deadlocking, so I’m prepared to pronounce the bug dead.
If that made no sense to you, worry not. Hey, look! An open thread!
Frank says: