LGF Technical Detour: Mysterious Lockup Solved
Charles Johnson
Fri May 18, 2007 at 1:10 pm PDT • Views: 193
Why didn’t anybody tell me about these table locking issues (nooooo!) in MySQL?
This is actually good news, because the overwhelmingly geeky page linked above helped me to solve an extremely annoying problem that has been causing sporadic, mysterious lockups at LGF since the database makeover. Who knew that there was a major contention issue when using the UPDATE statement on a table that’s also processing lots of concurrent SELECTs? Who, I ask you?
What this means in English is that if I edited one of my existing posts and hit the ‘Save Changes’ button, at a time when lots of people were loading the front page, the database would launch itself into an infinite loop of table locking, and suddenly nobody could access the posts at all. Server meltdown usually ensued, followed by unhappy hamsters demanding raises. And we were offline for anywhere from 10 minutes to an hour, while visitors saw the dreaded message, “MDB2 Error: connect failed.” Bummer all around, dude.
Problem is now solved, thanks to that indecipherable MySQL page above. (For the geeks, the quick fix is to add the ‘LOW_PRIORITY’ attribute to the offending UPDATE statement. Heh.)
Frank says: