Блокировка таблиц INNODB в MySQL

Содержание

Вопрос или проблема
Ответ или решение
Шаг 1: Проверка состояния транзакций
Шаг 2: Завершение зависших транзакций
Шаг 3: Перезагрузка MySQL
Шаг 4: Настройка PDO
Шаг 5: Проверка файловых систем и параметров конфигурации
Шаг 6: Проверка логов MySQL
Заключение

Вопрос или проблема

Моя среда – Windows 7, и я использую XAMPP.

Я проводил тестирование производительности MySQL, симулируя добавление 100 строк за раз. Я делал это с помощью простого PHP-запроса с циклом for.

Однако MySQL, похоже, завис, так как я больше не могу добавлять что-либо в таблицу.

Я вошел в MySQL, чтобы посмотреть список процессов, как показано ниже:

mysql> SHOW PROCESSLIST \G;
*************************** 1. row ***************************
     Id: 1
   User: root
   Host: localhost:49349
     db: NULL
Command: Sleep
   Time: 2
  State:
   Info: NULL
*************************** 2. row ***************************
     Id: 17
   User: root
   Host: localhost:49759
     db: NULL
Command: Query
   Time: 0
  State: NULL
   Info: SHOW PROCESSLIST
2 rows in set (0.00 sec)

Я попробовал два способа:

A. KILL QUERY 17, на что получил следующий результат:

mysql> KILL QUERY 17; ERROR 1317 (70100): Query execution was interrupted

B. Затем я использовал контроллер XAMPP, чтобы остановить MySQL и затем снова запустить его, но это не помогло, так как таблица, похоже, заблокирована навсегда.

Я также не могу добавлять данные в другие таблицы INNODB в базе данных. Таблицы MyISAM, похоже, в порядке.

Есть ли способ заставить INNODB просто перезапуститься/перезагрузиться или что-то в этом роде, т.е. все запросы и соединения?

Заранее спасибо.

ДОПОЛНЕНИЕ

Оказалось, что это была проблема, вызванная тем, как я использовал PHP PDO для выполнения запросов MySQL. Я установил PDO::ATTR_AUTOCOMMIT = false при создании соединения с MYSQL (по некоторым причинам я не мог это изменить), поэтому мне пришлось специально выполнять $db_connection_handle->query('COMMIT;'), чтобы явно зафиксировать транзакцию. Поэтому казалось, что таблицы были заблокированы.

Проверьте свои журналы. Вы можете обнаружить, что файл ibdata заполнен и не может расширяться или не настроен на автоматическое расширение.
Проверьте show innodb status, ища активные транзакции. Пример:

 ---TRANSACTION 0 16662058, ACTIVE 20 sec, process no 22014, OS thread id 140277380307216
 2 lock struct(s), heap size 368, 30 row lock(s), undo log entries 30
 MySQL thread id 3662, query id 46913632 localhost user
 Trx read view will not see trx with id >= 0 16662059, sees < 0 16662059

Ответ или решение

Для решения вашей проблемы с зависшими таблицами InnoDB в MySQL на XAMPP в Windows 7, давайте рассмотрим несколько шагов и возможных решений.

Шаг 1: Проверка состояния транзакций

Как вы уже заметили, InnoDB работает с транзакциями, и если они не будут зафиксированы (committed), это может вызвать блокировки. Чтобы выяснить, что происходит, выполните команду:

SHOW ENGINE INNODB STATUS;

Эта команда даст вам информацию о текущих активных транзакциях и может выявить блокировки. Обратите внимание на активные транзакции и узлы блокировки.

Шаг 2: Завершение зависших транзакций

Если вы увидите активные транзакции, которые блокируют другие, их можно завершить с помощью команды KILL:

KILL <идентификатор_процесса>;

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

Шаг 3: Перезагрузка MySQL

Вы уже пытались перезапустить MySQL через XAMPP. Если это не помогло, можно попробовать другой подход. Попробуйте следующее:

Остановите сервер MySQL через XAMPP.
Убедитесь, что все процессы MySQL завершены. Это можно сделать через диспетчер задач Windows.
Удалите временные файлы InnoDB (убедитесь, что у вас есть резервная копия данных перед этим). Файлы ib_logfile* можно удалить из папки данных MySQL (обычно это C:\xampp\mysql\data\).
Запустите MySQL снова через XAMPP.

Шаг 4: Настройка PDO

Вы упомянули, что у вас установлено PDO::ATTR_AUTOCOMMIT = false, и что вы должны вручную выполнять COMMIT. Это действительно может быть причиной блокировок. Обязательно добавляйте COMMIT после выполнения нескольких операций вставки данных. Лучше всего исправить код, чтобы избавиться от постоянных блокировок.

Шаг 5: Проверка файловых систем и параметров конфигурации

Убедитесь, что ваши файлы InnoDB (такие как ibdata1, ib_logfile0, ib_logfile1) не переполнены. Если размер ibdata1 зафиксирован и не может расширяться, это также может вызвать зависания. В конфигурации MySQL в файле my.cnf или my.ini можно установить следующие параметры:

innodb_file_per_table=1
innodb_buffer_pool_size=256M  # или больше, в зависимости от вашей памяти

Шаг 6: Проверка логов MySQL

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

Заключение

Следуя этим шагам, вы сможете устранить блокировки в ваших таблицах InnoDB. Не забывайте делать резервные копии данных перед выполнением каких-либо операций, которые могут повлиять на данные. Если проблема не решается, возможно, стоит рассмотреть возможность переустановки XAMPP или обновления MySQL до более новой версии.