Возможные причины ошибки Lock wait timeout exceeded; try restarting transaction.

Столкнулся с такой ошибкой на большом проекте, перечитал в интернете информацию о ней. Много советов вроде определите блокирующий процесс и прибейте его. Но откуда берется такая проблема, пришлось разбираться самому.

Такая ошибка появляется при конкурирующих транзакциях. Когда одно подключение к БД начало транзакцию и заблокировало определенную таблицу или строку, а другое пытается что то с этой таблицей или строкой сделать. У меня в приложении как раз было два одновременных подключения к БД, но хотелось разобраться в первопричине, ведь эти два потока не обращались одновременно к одним и тем же записям, а блокироваться таблица целиком не должна.

Как оказалось, при использовании связей, блокируется не только основная таблица, но и добавление в дочернюю таблицу элемента, связанного с заблокированным невозможно. Это стало для меня сюрпризом. Вот пример:

table user
  id
  name
  updated

table user_log
  id
  user_id
  description

START TRANSACTION;
UPDATE user set updated=NOW() WHERE id=1;

Если из второго подключения к БД попробовать выполнить такой запрос

INSERT INTO user_log SET user_id=1, description='XXX' 

То будет ошибка "Lock wait timeout exceeded; try restarting transaction. ", при условии конечно, что определена связь order.id с order_log.order_id.

06.01.2016