Lock wait timeout exceeded в Mysql

Ошибка “Lock wait timeout exceeded” возникает в MySQL’e, когда приложение пытается обновить запись, которая заблокирована. Такое может произойти в двух случаях:

1. Обновление большого количества записей

Приложение обновляет сразу много записей в таблице:

UPDATE users SET registered_ago = UNIX_TIMESTAMP() - registered_timestamp;

такой запрос обновит все записи в таблице

Пока запрос будет выполняться, будут заблокированы все записи. Причем каждая запись будет заблокирована, пока не произойдет полное обновление таблицы.

2. Частое обновление одной записи

Слишком частое обновление одной записи может привести к той же проблеме:

UPDATE stats SET pageviews = pageviews + 1 WHERE date = DATE(NOW())

такая операция может выполняться несколько тысяч раз в секунду

Что делать?

Если кровь уже льется на пол

Если проблема происходит прямо сейчас, необходимо узнать, какие процессы приводят к катастрофе. Обратить внимание нужно на процессы, время исполнения которых больше нескольких секунд:

show processlist

+------+------+-----------------+--------------------------------------------------------------------------+
| Id   | User | Host            | Command | Time | Info                 |
+------+------+-----------------+--------------------------------------------------------------------------+
| 1819 | root | localhost:38836 | Query   |    1 | UPDATE users SET ts = UNIX_TIMESTAMP() WHERE id = 723   |
| 1834 | root | localhost:38836 | Query   |    14 | UPDATE users SET premium = false                       |
+------+------+-----------------+--------------------------------------------------------------------------+

такой стремный запрос лучше вообще никогда не выполнять

Такой процесс необходимо уничтожить:
kill 1834

Если до выкатки еще неделя

Если проблема наблюдается часто, но нет возможности оперативно внести изменения в приложение, следует увеличить время ожидания в настройках:

set innodb_lock_wait_timeout=100

значение в секундах, по умолчанию 50 секунд

Или в my.cnf (но тогда нужно перезапускать MySQL):
innodb_lock_wait_timeout=100

Правильное решение

Нужно понимать, что причина этой ошибки — это проблема приложения, а не MySQL. Чтобы это починить, необходимо внести простые изменения в приложение.

Стратегия — уменьшить количество обновлений одних и тех же записей. Это достигается с помощью промежуточной таблицы и асинхронной задачи по обновлению данных:

#

Представим, что мы будем решать проблему для таблицы, в которой часто обновляются несколько записей. Такая структура:

id | user_name | page_views

Типичный запрос, который вызывает проблемы:
UPDATE users SET page_views = page_views + 1 WHERE id = 437
запросов очень много, получаем “Lock wait timeout exceeded”

Необходимо создать промежуточную таблицу:
CREATE TABLE log (id INT AUTO_INCREMENT PRIMARY KEY, user_id INT, page_views INT);

Вместо обновлений главной таблицы, все данные с обновлениями необходимо вставлять в таблицу log:
INSERT INTO log SET user_id = 4, page_views = 1;
Т.е. вместо обновления, постоянно дописываем новые записи в таблицу “log”

Завершающий этап — это асинхронная задача, которая будет агрегировать и обновлять главную таблицу раз в 10 секунд. Запускать агрегацию можно кроном, либо использовать supervisor для запуска процесса в фон.

# получаем последний ID в логе
$id = db("SELECT MAX(id) id FROM log");

# выбираем и агрегируем все данные из лога
$log_data = db("SELECT user_id, sum(pageviews) pageviews FROM log GROUP BY user_id");
foreach ( $log_data as $log )
{

# обновляем агрегированные данные
db("UPDATE users SET pageviews = {$log['pageviews']} WHERE id = {$log['id']}");
}

# очищаем ту часть лога, который обработан
db("DELETE FROM log WHERE id <= {$id}");

В качестве таблицы лога хорошо подойдет движок MEMORY. Если данные очень критичны к потерям — только InnoDB.

Самое важное

Ошибка “Lock wait timeout exceeded” встречается при частом обновлении одних и тех же данных. Для быстрого решения проблемы достаточно изменить настройку innodb_lock_wait_timeout. Асинхронная агрегация и обновление позволят избавиться от этой проблемы.

Этот текст был написан несколько лет назад. С тех пор упомянутые здесь инструменты и софт могли получить обновления. Пожалуйста, проверяйте их актуальность.