Lock wait timeout exceeded в Mysql

Thank you for reading this post, don't forget to subscribe!

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

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

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

&lt;code&gt;UPDATE users SET registered_ago = UNIX_TIMESTAMP() - registered_timestamp;&lt;/code&gt;

1	<code>UPDATE users SET registered_ago = UNIX_TIMESTAMP() - registered_timestamp;</code>

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

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

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

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

&lt;code&gt;UPDATE stats SET pageviews = pageviews + 1 WHERE date = DATE(NOW())&lt;/code&gt;

1	<code>UPDATE stats SET pageviews = pageviews + 1 WHERE date = DATE(NOW())</code>

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

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

show processlist

1	show processlist

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

+------+------+-----------------+--------------------------------------------------------------------------+

+------+------+-----------------+--------------------------------------------------------------------------+

+------+------+-----------------+--------------------------------------------------------------------------+

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

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

kill <b>1834</b>

1	kill <b>1834</b>

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

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

&lt;code&gt;set innodb_lock_wait_timeout=100&lt;/code&gt;

1	<code>set innodb_lock_wait_timeout=100</code>

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

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

innodb_lock_wait_timeout=100

1	innodb_lock_wait_timeout=100

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

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

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

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

id | user_name | page_views

1	id \| user_name \| page_views

Типичный запрос, который вызывает проблемы:

UPDATE users SET page_views = page_views + 1 WHERE id = 437

1	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);

1	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;

1	INSERT INTO log SET user_id = 4, page_views = 1;

# Т.е. вместо обновления, постоянно дописываем новые записи в таблицу "log"

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

&lt;?
<span class="comment">
# получаем последний ID в логе
</span>$id = db("SELECT MAX(id) id FROM log");

<span class="comment">
# выбираем и агрегируем все данные из лога
</span>$log_data = db("SELECT user_id, sum(pageviews) pageviews FROM log GROUP BY user_id");
foreach ( $log_data as $log )
{
<span class="comment">	# обновляем агрегированные данные
</span>	db("UPDATE users SET pageviews = {$log['pageviews']} WHERE id = {$log['id']}");
}

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

# получаем последний 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. Асинхронная агрегация и обновление позволят избавиться от этой проблемы.

Пн	Вт	Ср	Чт	Пт	Сб	Вс
						1
2	3	4	5	6	7	8
9	10	11	12	13	14	15
16	17	18	19	20	21	22
23	24	25	26	27	28	29
30

Centos

Lock wait timeout exceeded в Mysql

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

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

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

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

https://github.com/midnight47/