MediaWiki: lock wait timeout exceeded

Abstract

How to fix MediaWiki locked tables

I struggled after lunch using Google and MySQL-foo trying to fix some errant pages on a MediaWiki installation. The persistent message was

“Lock wait timeout exceeded;
  try restarting the transaction”,

which I could not fix straight away. There are some debugging instructions, but they did not help this time. The tables were OK when CHECKed. There are apparently some problems with InnoDB scrubbing whole transactions when there is an error in one query, but knowing that did not help much. There were long-running processing locking tables, and loading the frontpage worked again each time the processes were killed, but loading one specific broken page started them again.

There is a solution I worked out: running mysqlcheck -u<...> -p --optimize deletes any InnoDB tables and recreates them. This seemed to do the trick. Why, I don’t know, because if there was a corrupt row, CHECK should have caught it. Still, let this be Google-fodder.