Что такое тупик?

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

Причины тупика MySQL

1. Конкуренция за одни и те же ресурсы

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

2. Обновление блокировки

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

3. Неправильный порядок сделок

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

4. Длинные транзакции и высокий уровень изоляции

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

Как обнаружить тупик MySQL?

1. Проверьте журнал ошибок.

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

2. использоватьSHOW ENGINE INNODB STATUSЗаказ

Эта команда предоставляет подробную информацию о механизме хранения InnoDB, включая обнаружение взаимоблокировок. В выводе этой команды вы можете найти подробную информацию, связанную с взаимоблокировкой, например список взаимоблокирующих транзакций, ожидающие блокировки и т. д.

3. Инструменты мониторинга производительности

Используйте инструменты мониторинга производительности (такие как Percona Toolkit, MySQL Enterprise Monitor и т. д.) для мониторинга показателей производительности базы данных в режиме реального времени, включая частоту и продолжительность взаимоблокировок. Эти инструменты обычно предоставляют визуальные интерфейсы и функции оповещения, помогающие администраторам своевременно обнаруживать и решать проблемы тупиковой ситуации.

Анализ случаев тупиковой ситуации MySQL

Случай 1: Конкуренция за один и тот же ресурс

Описание сцены

Две транзакции пытаются обновить одну и ту же строку данных.

Порядок выполнения транзакции

1. Транзакция АОбновить таблицуusersсерединаid=1Цель,Но оно не было представлено.
2. Транзакция Бтоже пробовал Обновить таблицуusersсерединаid=1Цель,но заблокирован,потому что Транзакция Ауже Замок Забронировал поездку。
3. в то же время,Транзакция Атоже пробовал Обновить таблицуordersсередина Принадлежит пользователю1заказ,Но строку заменили на Транзакцию БЗамок Конечно（гипотеза Транзакция БДоуже Замок Конечнострока заказа）。
4. в это время,Транзакция Аи Транзакция Подождите, пока друг друга высвободят ресурсы.,Формирование замка смерти.

пример SQL

-- Транзакция А
START TRANSACTION;
UPDATE users SET balance = balance - 100 WHERE id = 1; -- Замок Определить строку пользователя 1
-- Попробуйте обновить таблицу заказов позже.

-- Транзакция Б
START TRANSACTION;
UPDATE orders SET status = 'shipped' WHERE user_id = 1; -- Замок Определить строку заказа для пользователя 1
-- Попробуйте обновить позжеusersповерхность

Случай 2: Обновление блокировки

Описание сцены

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

Порядок выполнения транзакции

1. Транзакция Ачитатьповерхностьproductsсерединаid=1информация о продукте（Используйте общий доступ Замок）。
2. Транзакция Б也читать相同информация о продукте（общий Замок Не взаимоисключающие）。
3. Транзакция АТеперь хочу обновить информацию об этом продукте,Необходимо перейти на эксклюзивный Замок.,но был Транзакция Бизобщий Замокблокировать。
4. в то же время,Транзакция БТакже хочу обновить информацию об этом продукте,такой же Необходимо перейти на эксклюзивный Замок.,одеяло Транзакция Аизобщий Замок（Запросите повышение до эксклюзивного уровня прямо сейчас Замок）блокировать。
5. Образовался Мертвый Замок.

пример SQL

-- Транзакция А
START TRANSACTION;
SELECT * FROM products WHERE id = 1 LOCK IN SHARE MODE; -- Получить Поделиться Замок
-- Попробуйте обновить позже

-- Транзакция Б
START TRANSACTION;
SELECT * FROM products WHERE id = 1 LOCK IN SHARE MODE; -- Получить Поделиться Замок
-- Попробуйте обновить позже

Случай 3: Неправильный порядок транзакций

Описание сцены

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

Порядок выполнения транзакции

1. Транзакция АЗамок Конечноповерхностьaccountsсерединаaccount_no=1001Цель。
2. Транзакция БЗамок Конечноповерхностьaccountsсерединаaccount_no=1002Цель。
3. Транзакция Апытаюсь получить доступaccount_no=1002Цель,но был Транзакция БЗамок Конечно。
4. Транзакция Бпытаюсь получить доступaccount_no=1001Цель,но был Транзакция АЗамок Конечно。
5. Образовался Мертвый Замок.

пример SQL

-- Транзакция А
START TRANSACTION;
UPDATE accounts SET balance = balance + 50 WHERE account_no = 1001; -- Замок Определить 1001 аккаунт
-- Попробуйте получить доступ к учетной записи 1002 позже.

-- Транзакция Б
START TRANSACTION;
UPDATE accounts SET balance = balance - 50 WHERE account_no = 1002; -- Замок Определить 1002 аккаунт
-- Попробуйте войти в учетную запись 1001 позже.

Случай 4: Длинные транзакции и высокий уровень изоляции

Описание сцены

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

Порядок выполнения транзакции

1. Транзакция Начать длинную транзакцию,и Замок Конечно Понятноповерхностьinventoryсерединаиз某些行。
2. потому что Транзакция Срок исполнения долгий,Транзакция Б ждет Транзакцию Авыпускать Замокиз过程серединатоже началосьипытаться Замок Конечноповерхностьinventoryсерединаиз其他行。
3. Транзакция БОжидание процессасерединаодеялоблокировать,потому чтозанимает Цельодеяло Транзакция АЗамок Конечно。
4. в то же время,Транзакция Ав последующих операцияхсерединапытаться Замок Конечно Транзакция Буже Замок Конечно Цель,Вызывает смерть Замок.

пример SQL

Операторы SQL в этом случае аналогичны другим случаям, но дело в том, что время выполнения транзакции А очень велико, что может быть связано со сложной бизнес-логикой, внешними системными вызовами или искусственными паузами. При высоком уровне изоляции (например, повторяемом чтении) транзакция B с большей вероятностью будет затронута транзакцией A и приведет к взаимоблокировке.

Решение тупика MySQL

1. Повторите неудачные транзакции.

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

2. Оптимизация дизайна транзакции

• Уменьшить размер транзакции：Попробуйте разделить большие транзакции на несколько более мелких транзакций.,Сократите продолжительность транзакции.
• Фиксированный порядок доступа к ресурсам：Если все транзакции обращаются к ресурсам в одном и том же порядке,Тогда вероятность смерти значительно уменьшится.
• Избегайте длинных транзакций：Минимизируйте время выполнения транзакции,Не занимайте Замок на длительное время.

3. Установите тайм-аут блокировки.

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

4. Отрегулируйте уровень изоляции.

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

5. Используйте стратегии предотвращения тупиковых ситуаций

• Используйте транзакции с низким приоритетом：Установите более низкий приоритет для неважных задач.,Сделайте его предпочтительным откатом при возникновении смерти.
• Избегайте ожидания цикла：通过合理из资源分配ибизнес-дизайн,Избегайте условий, которые создают циклическое ожидание.

6. Мониторинг и оповещение

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

Подвести итог

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

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