Механизм блокировок на уровне строк (Row-Level Locking) в InnoDB

Для обеспечения транзакционной изоляции движок InnoDB накладывает эксклюзивные блокировки (X-Locks) на изменяемые строки при выполнении операций UPDATE, DELETE или SELECT ... FOR UPDATE. Если транзакция Б пытается модифицировать или заблокировать строку, которая уже удерживается незавершенной транзакцией А, транзакция Б переходит в режим ожидания (Lock Wait). Время ожидания жестко лимитируется системной переменной innodb_lock_wait_timeout (по умолчанию 50 секунд). Если за это время первая транзакция не выполнила COMMIT или ROLLBACK, транзакция Б прерывается с системной ошибкой:

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

Бизнес-риски

Массовое зависание пользовательских запросов в очереди, сбои при обновлении остатков товаров на складе, каскадное накопление открытых соединений и падение по ошибке 1040.

Сравнение таймаута блокировки (1205) и дедлока (1213)

Регламент локализации блокирующей транзакции и оптимизации запросов

Шаг 1: Поиск блокирующего процесса через представление sys.innodb_lock_waits

Выполните запрос для определения точного ID потока и SQL-запроса, который держит блокировку:

-- 1. Запрос анализа блокировок (MySQL 5.7 / 8.0+):
SELECT 
    r.trx_id waiting_trx_id,
    r.trx_mysql_thread_id waiting_thread,
    r.trx_query waiting_query,
    b.trx_id blocking_trx_id,
    b.trx_mysql_thread_id blocking_thread,
    b.trx_query blocking_query
FROM performance_schema.data_lock_waits w
JOIN information_schema.innodb_trx b ON b.trx_id = w.blocking_engine_transaction_id
JOIN information_schema.innodb_trx r ON r.trx_id = w.requesting_engine_transaction_id;

-- 2. В схеме SYS (удобный человекочитаемый отчет):
SELECT 
    waiting_account, waiting_query, 
    blocking_account, blocking_query, 
    wait_age_secs, locked_table
FROM sys.innodb_lock_waits;

Шаг 2: Принудительное завершение блокирующего потока

-- Убейте поток, удерживающий транзакцию открытой (blocking_thread ID):
KILL 15420;

Шаг 3: Настройка параметров innodb_lock_wait_timeout и rollback

# 1. Уменьшение таймаута ожидания для веб-приложений (например, до 15 секунд вместо 50):
SET GLOBAL innodb_lock_wait_timeout = 15;

# 2. Включение полного отката всей транзакции при наступлении таймаута (защита целостности!):
# В my.cnf:
sudo nano /etc/mysql/mysql.conf.d/mysqld.cnf

[mysqld]
innodb_lock_wait_timeout = 15
innodb_rollback_on_timeout = 1

# 3. Перезапуск службы:
sudo systemctl restart mysql

Шаг 4: Оптимизация неиндексированных UPDATE запросов

Если запрос UPDATE выполняется по неиндексированному полю, InnoDB накладывает блокировку НА ВСЮ ТАБЛИЦУ ЦЕЛИКОМ (Table-level Lock), блокируя всех остальных пользователей:

-- ОШИБКА: поле status не имеет индекса -> блокируются ВСЕ строки таблицы orders!
UPDATE orders SET processed = 1 WHERE status = 'pending';

-- ИСПРАВЛЕНИЕ: создание индекса для точечной блокировки только изменяемых строк:
ALTER TABLE orders ADD INDEX idx_status (status);

Типовые ошибки администраторов

• Простое увеличение innodb_lock_wait_timeout до 500 секунд: Это не решает проблему, а лишь заставляет пользователей ждать по 8 минут перед получением ошибки, полностью парализуя работу пула потоков.
• Игнорирование незакрытых транзакций в бэкенд-коде: Открытие транзакции (BEGIN), выполнение тяжелых внешних HTTP-запросов к API и последующий COMMIT держит строки базы заблокированными на десятки секунд.