Назад | Перейти на главную страницу

Установка лимита времени для транзакции в MySQL / InnoDB

Это возникло из этот связанный вопрос, где я хотел знать, как заставить две транзакции выполняться последовательно в тривиальном случае (когда обе работают только с одной строкой). Получил ответ - используйте SELECT ... FOR UPDATE в качестве первой строки обеих транзакций, но это приводит к проблеме: Если первая транзакция никогда не фиксируется или не откатывается, вторая транзакция будет заблокирована на неопределенный срок. В innodb_lock_wait_timeout переменная устанавливает количество секунд, по истечении которых клиенту, пытающемуся совершить вторую транзакцию, будет сказано: «Извините, попробуйте еще раз» ... но, насколько я могу судить, они будут пытаться снова до следующей перезагрузки сервера. Так:

Конечно, должен быть способ заставить ROLLBACK если транзакция длится вечно? Должен ли я прибегать к использованию демона для уничтожения таких транзакций, и если да, то как будет выглядеть такой демон?
Если соединение разорвано wait_timeout или interactive_timeout в середине транзакции, откат транзакции? Есть ли способ проверить это с консоли?

Разъяснение: innodb_lock_wait_timeout устанавливает количество секунд, в течение которых транзакция будет ждать снятия блокировки, прежде чем отказаться от нее; то, что я хочу, это способ принудительное снятие блокировки.

Обновление 1: Вот простой пример, демонстрирующий, почему innodb_lock_wait_timeout недостаточно для того, чтобы вторая транзакция не была заблокирована первой:

START TRANSACTION;
SELECT SLEEP(55);
COMMIT;

При настройке по умолчанию innodb_lock_wait_timeout = 50, эта транзакция завершится без ошибок через 55 секунд. И если вы добавите UPDATE перед SLEEP линии, затем инициируйте вторую транзакцию от другого клиента, который пытается SELECT ... FOR UPDATE в той же строке истекает время ожидания второй транзакции, а не той, которая заснула.

Я ищу способ принудительно положить конец спокойному сну этой транзакции.

Обновление 2: В ответ на опасения hobodave относительно реалистичности приведенного выше примера, вот альтернативный сценарий: администратор базы данных подключается к действующему серверу и запускает

START TRANSACTION
SELECT ... FOR UPDATE

где вторая строка блокирует строку, в которую приложение часто записывает. Затем администратор базы данных прерывается и уходит, забывая завершить транзакцию. Приложение останавливается, пока строка не будет разблокирована. Я хотел бы минимизировать время зависания приложения из-за этой ошибки.

mysql innodb

Более половины этой темы, похоже, посвящено тому, как задавать вопросы о ServerFault. Я думаю, что вопрос имеет смысл и довольно прост: как автоматически откатить приостановленную транзакцию?

Одно из решений, если вы хотите убить все соединение, - установить wait_timeout / interactive_timeout. Видеть https://stackoverflow.com/questions/9936699/mysql-rollback-on-transaction-with-lost-disconnected-connection.

Поскольку ваш вопрос задается здесь, на ServerFault, логично предположить, что вы ищете решение MySQL для проблемы MySQL, особенно в области знаний, в которых системный администратор и / или администратор баз данных могли бы иметь опыт. Таким образом, следующие Раздел отвечает на ваши вопросы:

Если первая транзакция никогда не фиксируется или не откатывается, вторая транзакция будет заблокирована на неопределенный срок.

Нет, не будет. Я думаю ты не понимаешь innodb_lock_wait_timeout. Он делает именно то, что вам нужно.

Он вернется с ошибкой, как указано в руководстве:

ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

По определению это не неопределенный. Если ваше приложение повторно подключается и блокируется, то ваше приложение "блокировка на неопределенный срок", а не транзакция. Вторая транзакция очень точно блокируется на innodb_lock_wait_timeout секунд.

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

Если вам нужен автоматический откат, это также объясняется в руководстве:

Текущая транзакция не откатывается. (Чтобы выполнить откат всей транзакции, запустите сервер с --innodb_rollback_on_timeout вариант.

RE: Ваши многочисленные обновления и комментарии

Во-первых, вы заявили в своих комментариях, что вы «хотели» сказать, что хотите, чтобы тайм-аут первый транзакция, заблокированная на неопределенный срок. Это не очевидно из вашего исходного вопроса и конфликтует с «Если первая транзакция никогда не фиксируется или не откатывается, то вторая транзакция будет заблокирована на неопределенный срок».

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

Единственная другая альтернатива - использовать или написать библиотеку mysql, которая использует неблокирующий ввод-вывод, что позволит вам применение чтобы убить поток / вилку, выполняющую запрос, через N секунд. Реализация и использование такой библиотеки выходят за рамки ServerFault. Это подходящий вопрос для Переполнение стека.

Во-вторых, в своих комментариях вы заявили следующее:

На самом деле меня больше интересовал сценарий, в котором клиентское приложение зависает (скажем, зацикливается) во время транзакции, чем сценарий, в котором транзакция занимает много времени на стороне MySQL.

В вашем исходном вопросе этого совсем не было, и все еще нет. Это можно было заметить только после того, как вы поделились этим довольно важным лакомым кусочком в комментарии.

Если вы действительно пытаетесь решить эту проблему, то, боюсь, вы задали ее не на том форуме. Вы описали проблему программирования на уровне приложения, которая требует программного решения, которое MySQL не может предоставить, и выходит за рамки этого сообщества. Ваш последний ответ отвечает на вопрос «Как предотвратить бесконечный цикл программы Ruby?». Этот вопрос не по теме для этого сообщества, и его следует задать на Переполнение стека.

В аналогичной ситуации моя команда решила использовать pt-kill (https://www.percona.com/doc/percona-toolkit/2.1/pt-kill.html). Он может сообщать или уничтожать запросы, которые (среди прочего) выполняются слишком долго. Затем его можно запускать в cron каждые X минут.

Проблема заключалась в том, что запрос, который имел неожиданно долгое (например, неопределенное) время выполнения, заблокировал процедуру резервного копирования, которая пыталась сбросить таблицы с блокировкой чтения, которая, в свою очередь, заблокировала все другие запросы на «ожидание сброса таблиц», которые затем никогда не истекали. потому что они не ждут блокировки, что привело к отсутствию ошибок в приложении, что в конечном итоге привело к отсутствию предупреждений для администраторов и остановке приложения.

Очевидно, что исправление заключалось в исправлении начального запроса, но во избежание случайного возникновения ситуации в будущем было бы здорово, если бы можно было автоматически уничтожать (или сообщать) транзакции / запросы, которые длятся дольше определенного времени, какой автор вопроса, кажется, задает. Это можно сделать с помощью pt-kill.

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

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

Команда

SHOW PROCESSLIST;

дает вам таблицу со всеми командами, выполняемыми в данный момент, и временем (в секундах) с момента их запуска. Когда клиент перестал давать команды, они описываются как дающие Sleep команда, с Time столбец - количество секунд с момента выполнения последней команды. Так что вы можете вручную войти и KILL все, что имеет Time значение, скажем, 5 секунд, если вы уверены, что ни один запрос к вашей базе данных не должен занимать более 5 секунд. Например, если процесс с идентификатором 3 имеет значение 12 в своем Time колонка, вы могли бы сделать

KILL 3;

Документация для KILL синтаксис предполагает, что транзакция, выполняемая потоком с этим идентификатором, будет отменена (хотя я этого не тестировал, поэтому будьте осторожны).

Но как это автоматизировать и каждые 5 секунд убивать все сверхурочные скрипты? Первый комментарий к та самая страница дает нам подсказку, но код написан на PHP; кажется, что мы не можем сделать SELECT на SHOW PROCESSLIST из клиента MySQL. Тем не менее, предположим, что у нас есть демон PHP, который мы запускаем каждые $MAX_TIME секунд, это может выглядеть примерно так:

$result = mysql_query("SHOW FULL PROCESSLIST");
while ($row=mysql_fetch_array($result)) {
  $process_id=$row["Id"];
    if ($row["Time"] > $MAX_TIME) {
      $sql="KILL $process_id";
      mysql_query($sql);
  }
}

Обратите внимание, что это будет иметь побочный эффект принуждения все простаивающие клиентские соединения для повторного подключения, а не только те, которые плохо себя ведут.

Если у кого-то есть лучший ответ, я бы хотел его услышать.

Редактировать: Существует как минимум один серьезный риск использования KILL в этом случае. Предположим, вы используете приведенный выше сценарий для KILL каждое соединение, которое не используется в течение 10 секунд. После того, как соединение простаивало в течение 10 секунд, но до KILL выдается, пользователь, чье соединение разрывается, выдает START TRANSACTION команда. Они тогда KILLизд. Они отправляют UPDATE а затем, дважды подумав, выдайте ROLLBACK. Однако, поскольку KILL откатил исходную транзакцию, обновление было выполнено немедленно и не может быть отменено! Видеть эта почта для тестового случая.

Как предположил Хободаве в комментарии, это возможно в некоторых клиентах (хотя, по-видимому, не mysql утилита командной строки), чтобы установить ограничение по времени для транзакции. Вот демонстрация использования ActiveRecord в Ruby:

require 'rubygems'
require 'timeout'
require 'active_record'

Timeout::timeout(5) {
  Foo.transaction do
    Foo.create(:name => 'Bar')
    sleep 10
  end
}

В этом примере транзакция истекает через 5 секунд и автоматически откатывается. (Обновление в ответ на комментарий hobodave: Если базе данных требуется более 5 секунд для ответа, транзакция будет отменена, как только это произойдет - не раньше.) Если вы хотите убедиться, что все ваши транзакции истекут по истечении n секунд, вы можете создать оболочку вокруг ActiveRecord. Я предполагаю, что это также относится к наиболее популярным библиотекам Java, .NET, Python и т.д., но я еще не тестировал их. (Если да, оставьте комментарий к этому ответу.)

Транзакции в ActiveRecord также имеют то преимущество, что они безопасны, если KILL выдается, в отличие от транзакций, выполняемых из командной строки. Видеть https://dba.stackexchange.com/questions/1561/mysql-client-believes-theyre-in-a-transaction-gets-killed-wreaks-havoc.

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