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

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

Команда

SHOW PROCESSLIST;

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

KILL 3;

Документация для синтаксиса УНИЧТОЖЕНИЯ предполагает, что транзакция, выполняемая потоком с тем идентификатором, откатывалась бы (хотя я не протестировал это, так быть осторожным).

Но как автоматизировать это и уничтожать все сверхурочные сценарии каждые 5 секунд? Первый комментарий к той же самой странице дает нам подсказку, но код находится в PHP; кажется, что мы не можем сделать a 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 выпущен, пользователь, соединение которого является уничтоженными проблемами a START TRANSACTION команда. Они затем KILLредактор они отправляют UPDATE и затем, думая дважды, проблема a ROLLBACK. Однако, потому что KILL откатываемый их исходная транзакция, обновление сразу прошло и не может откатываться! См. это сообщение для тестового сценария.

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

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

Нет, это не будет. Я думаю, что Вы не понимаете innodb_lock_wait_timeout. Это делает точно, в чем Вы нуждаетесь.

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

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

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

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

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

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

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

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

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

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

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

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

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

Если это - на самом деле проблема, Вы пытаетесь решить, то я боюсь, что Вы спросили это относительно неправильного форума. Вы описали проблему программирования прикладного уровня, которая требует решения для программирования, то, которое не может обеспечить MySQL, и за пределами объема этого сообщества. Ваш последний ответ отвечает на вопрос, "Как я предотвращаю программу Ruby от бесконечно цикличного выполнения?". Тот вопрос вне темы для этого сообщества и должен быть задан на StackOverflow.

Как hobodave предложенный в комментарии, это возможно в некоторых клиентах (хотя не, по-видимому, 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 также имеют преимущество того, чтобы быть безопасным если a KILL выпущен, в отличие от транзакций, сделанных из командной строки. См. https://dba.stackexchange.com/questions/1561/mysql-client-believes-theyre-in-a-transaction-gets-killed-wreaks-havoc.

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

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

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

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

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

Мы хотим убить транзакции, которые простаивают и блокируют таблицу(s), которую я хочу использовать.

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

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

innodb_rollback_on_timeout-делает не совсем то, на что похоже (InnoDB всегда будет откатывать что-то по тайм-ауту, если есть что откатывать --это определяет, сколько (см. документацию)

Итак, давайте действовать хирургически и убивайте только те потоки, которые нужно убить.

show processlistвам не поможет, потому что он показывает только тех, кто спит, но не показывает, у кого есть какая-либо блокировка, но этот запрос будет (это MySQL 8 может работать в более старых версиях, я не пробовал):

SELECT i.trx_mysql_thread_id thread, 
    i.trx_state, 
    trx_tables_in_use tbl_used, 
    trx_tables_locked tbl_locked, 
    p.USER, 
    p.DB, 
    p.COMMAND, 
    p.TIME, 
    TIMESTAMPDIFF(SECOND, trx_started, now()) trx_time, 
    SUBSTRING(trx_query, 1, 40) 
FROM INFORMATION_SCHEMA.PROCESSLIST p 
  JOIN INFORMATION_SCHEMA.INNODB_TRX i 
    ON i.trx_mysql_thread_id = p.ID;

Обратите внимание -столбцы ВРЕМЯ и trx_время --первый — это то, что вы хотите видеть в списке процессов, а второй — как долго выполняется эта транзакция. Не ошибитесь, если значение TIME очень низкое, 1 или 2 секунды, значит, эта транзакция что-то делает. Также обратите внимание на столбцы trx_tables_in_use и trx_tables_locked tbl_locked, если они равны 0, эта транзакция не будет блокировать другой запрос --, но будет блокировать такие вещи, как ALTER TABLE, которые требуют «блокировки метаданных». Наконец, обратите внимание на состояние trx_, когда вы пытаетесь это сделать, оно, скорее всего, будет «РАБОТАЕТ», после того как вы завершите транзакцию, это может быть «ОТКАТ», и вам все равно придется ждать.

Теперь изменим приведенный выше запрос, чтобы найти только поток_id(s), который вы хотите убить.:

SELECT i.trx_mysql_thread_id
FROM INFORMATION_SCHEMA.PROCESSLIST p 
  JOIN INFORMATION_SCHEMA.INNODB_TRX i 
    ON i.trx_mysql_thread_id = p.ID
WHERE i.trx_state = 'RUNNING'    
    AND trx_tables_locked > 0
    AND p.COMMAND = 'Sleep'
    AND p.TIME > 60

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

Удачи, Frank