Перейти к содержимому 
Строка базы данных Блокировка в MySQL контролирует, какой именно транзакции разрешено читать или записывать строки и когда, защищая тем самым от потерянных обновлений и грязных чтений. Я покажу вам шаг за шагом, как установить блокировку, MVCC взаимодействуют уровни изоляции, где возникают проблемы с параллелизмом и как я разрабатываю запросы, индексы и транзакции таким образом, чтобы блокировка не возникала.
Центральные пункты
Чтобы помочь вам быстро понять, на чем я сосредоточился в этой статье, я обобщу и кратко сравню наиболее важные рекомендации. Это даст вам компактную структуру для следующих, более подробных статей Объяснения.
- Замки для рядов ограничивать конфликты отдельными строками, а не целыми таблицами.
- MVCC обеспечивает быстрое чтение без постоянных общих блокировок.
- Изоляция определяет, какие аномалии могут возникнуть.
- Gap/Next-Key блокировать индексные пробелы против фантомов.
- Лучшие практики заметно сократить количество блокировок и тупиковых ситуаций.
Далее я переведу эти пункты в конкретные меры, которые я использую для обеспечения безопасности и быстродействия производительных экземпляров MySQL. Каждая рекомендация направлена на то, чтобы меньше Блокировка, последовательные данные и четкие пути диагностики.
Почему необходим контроль параллелизма
Одновременные доступы сталкиваются, как только несколько сессий хотят прочитать или записать одни и те же строки, вот почему я полагаюсь на четкое Лимиты на транзакции обратите внимание. Без правил существует риск потери обновлений, грязных чтений, неповторяющихся чтений и фантомов, которые в конечном итоге вызывают неправильные решения в коде приложения. Я предотвращаю это, обеспечивая согласованность чтения и делая конфликты записи видимыми на ранней стадии вместо того, чтобы молча перезаписывать их. Чем больше параллельных пользователей активно работают, тем важнее небольшие объекты блокировки и короткие Время выдержки. Если вы проигнорируете это, то приобретете ошибки данных с длинными цепочками ожидания и таймаутами.
Основы блокировки строк в MySQL
Блокировка строк устанавливает блокировку на отдельные строки, чтобы другие строки оставались свободными и можно было заблокировать больше строк. Параллелизм создается. Эксклюзивная блокировка защищает операции записи вплоть до фиксации, а для доступа к чтению используются общие блокировки или снимки MVCC, в зависимости от уровня изоляции. Блокировки намерений служат сигналами на более высоком уровне, чтобы движок мог быстрее проверять совместимость блокировок. Я всегда обращаю внимание на то, что даже небольшие обновления могут затронуть множество строк, если условия WHERE неточны и нет Индекс приводит. Точность фильтра позволяет избежать широких диапазонов блокировки и сохранить параллельность.
Взаимодействие с индексами также важно, поскольку InnoDB блокирует по индексным путям; отсутствующие или неподходящие ключи значительно увеличивают количество затронутых строк. Если к оператору обращаются через полное сканирование, поле блокировки растет, что увеличивает время ожидания и способствует возникновению тупиковых ситуаций. Поэтому я с самого начала планирую подходящие ключи для частых путей и делаю формулы WHERE как можно более конкретными. Таким образом, мои блокировки остаются узкими, а другие транзакции обрабатываются быстрее. Доступ. Это самый простой регулировочный винт для плавной фиксации.
Пессимистическая и оптимистическая блокировка
Пессимистическая блокировка предполагает конфликты и блокирует раньше, что повышает целостность, но требует времени, в то время как оптимистичный системы только в конце проверяют, изменились ли данные. В практических установках MySQL я комбинирую оба варианта: для критических учетных записей я использую FOR UPDATE, для редко сталкивающихся сущностей - версии. Колонка версии или временная метка позволяют мне определить, был ли кто-то быстрее во время обновления, без постоянного блокирования строки. Если возникает конфликт, я целенаправленно повторяю транзакцию или выполняю настроенную бизнес-логику. Это позволяет мне более рационально распределить нагрузку, сократить время ожидания и сохранить Корректность высокий.
Я выбираю стратегию в зависимости от конкретного случая использования: много одновременных доступов на чтение выигрывают от оптимистичных подходов, в то время как высококритичные денежные или фондовые записи процветают при использовании коротких, но четких эксклюзивных блоков. Цель всегда состоит в том, чтобы блокировать только столько, сколько необходимо, и распознавать конфликты на ранней стадии. При таком подходе я избегаю длинных цепочек ожидающих сессий. Это повышает пропускную способность и Надежность в повседневной жизни.
Понимание уровней изоляции и MVCC
Уровень изоляции определяет, сколько аномалий я допускаю и как сильно MySQL блокирует, поэтому я намеренно выбираю уровень в зависимости от случая использования. READ COMMITTED предотвращает грязные доступы к чтению, REPEATABLE READ сохраняет последовательность значений транзакций, а SERIALIZABLE устанавливает самые строгие последовательности. InnoDB использует MVCC, так что читатели почти всегда могут обойтись без общих блокировок и при этом видеть последовательные снимки. Любой, кто работает с этим, должен понимать, когда вступают в силу блокировки gap и next-key, чтобы предотвратить появление фантомов. Для получения более подробной справочной информации стоит взглянуть на Подробная информация об уровнях изоляции, чтобы вы могли правильно оценить эффекты для каждого уровня.
В следующей таблице общие уровни классифицируются по типичным аномалиям и влиянию на замки, чтобы я мог сделать правильный выбор и избежать ненужных Блокировка избегать.
| Уровень изоляции | Разрешенные аномалии | Поведение замка (упрощенное) | Типичное использование |
|---|---|---|---|
| ЧИТАТЬ БЕЗ ФИКСАЦИИ | Грязное чтиво, Невозвращенец, Фантомы | Почти нет замков, высокая Риски | Редко бывает полезным |
| READ COMMITTED | Неповторяемые, фантомы | Читатели используют MVCC, писатели X-Locks | Отчеты, API с множеством вариантов чтения |
| ПОВТОРЯЕМОЕ ЧТЕНИЕ | Фантомы уменьшены Next-Key | Сильная последовательность чтения, целенаправленное Gap-Замки | Стандарт в InnoDB |
| СЕРИАЛИЗИРУЕМЫЕ | Нет аномалий | Более широкие барьеры, более низкие Параллелизм | Высококритичные процессы |
Обычно я начинаю с REPEATABLE READ и вношу целевые коррективы, если запросы слишком сильно блокируются из-за блокировок следующего ключа. И наоборот, я использую SERIALIZABLE только в тех случаях, когда это технически неизбежно, поскольку в противном случае время ожидания будет накапливаться. Имея четкий выбор для каждой рабочей нагрузки, я поддерживаю постоянство данных и в то же время защищаю Производительность. Такой подход экономит время службы поддержки, поскольку неожиданные пики блокировки происходят реже. Это означает, что система остается предсказуемой, даже если количество пользователей растет.
параллелизм в mysql на практике
Правильный параллелизм начинается с чисто сформулированных запросов, которые выполняют только те строки, которые действительно необходимы, так что InnoDB может обрабатывать небольшие Ряд-Можно установить блокировки. Я слежу за тем, чтобы условия фильтрации были sargable, т. е. выполнялись через индексы и не заставляли вызывать функции на столбцах. Я слежу за тем, чтобы обновления были целенаправленными: Чистое предложение WHERE, соответствующий индекс, никаких лишних объединений в одном операторе. Для случаев резервирования я использую FOR UPDATE редко и только для тех записей данных, которые действительно затрагиваются. Я также избегаю длительного взаимодействия с пользователем между BEGIN и COMMIT, поскольку каждая секунда увеличивает время ожидания другие занятия.
При вставках в плотные индексные пространства я учитываю, что могут вступить в силу блокировки по следующему ключу, и поэтому больше транзакций ожидают. Я распределяю горячие точки, рассредоточивая ключевые пространства или освобождая путь записи в небольшой независимой очереди. Это уменьшает количество коллизий на самой горячей таблице. Такая тонкая настройка дает больший эффект, чем увеличение таймаутов, потому что меньше Конфликты возникнуть вообще. Именно поэтому стоит измерить доступ к данным до начала эксплуатации.
Типичные проблемы параллелизма: Блокировка, тупики, область блокировки
Блокировка происходит, когда транзакция ожидает строку, которая уже заблокирована, вот почему я делаю транзакции короткими и блокирую затронутую строку. Количество ограничение. Тупики возникают, когда две транзакции блокируют друг друга, что MySQL распознает и прерывает одну из них. Я реагирую на это целенаправленными повторными попытками и последовательностью доступа во всех путях кода. В InnoDB эскалация блокировок встречается реже, но внутренние ограничения ограничивают усилия по администрированию; большие сканирования приближают движок к таким ограничениям. Если вы видите повторяющиеся тупиковые ситуации, вам следует воспользоваться функцией Обнаружение и обработка тупиковых ситуаций систематически устранять источники конфликтов, а не просто увеличивать тайм-ауты.
По моему опыту, три паттерна вызывают особенно длительное время ожидания: неиндексированные фильтры на горячих таблицах, FOR UPDATE без точного предложения WHERE и длинная бизнес-логика между шагами чтения и записи. Я устраняю их, измеряя каждый путь по отдельности, уменьшая длительность блокировки и сокращая SQL-операторы до индексированных путей. Небольшие изменения в фильтре или последовательности обновлений часто решают проблему целых узлов. Такие исправления более благоприятны, чем более Оборудование, потому что они имеют длительный эффект. Только после этого стоит задуматься о вертикальном или горизонтальном масштабировании.
Лучшие методы борьбы с блокировками и тупиками
Я быстро завершаю транзакции и не оставляю открытыми экраны ввода, пока удерживаются замки, потому что каждая секунда уходит впустую. Цепи ожидания спровоцировано. Я всегда обращаюсь к таблицам и строкам в одном и том же порядке, чтобы избежать циклических зависимостей. READ COMMITTED часто достаточно для строк, предназначенных только для чтения, а для критических обновлений я использую REPEATABLE READ или FOR UPDATE по первому требованию. Проектирование индексов остается обязательным: без подходящего ключа в операторе быстро блокируется слишком много строк. Обработка ошибок также является частью этой работы: Я отлавливаю тупиковые ошибки, записываю все подробности и стараюсь создать короткий, чистый Повторная попытка.
Завершает пакет мониторинг: Я наблюдаю за временем ожидания, количеством тупиков и планами запросов и в первую очередь оптимизирую заметные пики. Небольшие улучшения в горячих путях приносят огромные плоды, потому что они влияют на каждый запрос. Таким образом, я добиваюсь меньшего количества блокировок, большей пропускной способности и надежного времени отклика. Такой подход гораздо более убедителен в повседневной работе, чем масштабные реорганизации. Чистые рутины побеждают обобщенные Действия почти всегда.
Советы по повышению параллелизма в MySQL
Я использую autocommit осознанно: отдельные заявления получают от него пользу, а последовательные изменения обобщаются в коротком и ясном Транзакция земля. Я использую SELECT ... FOR UPDATE редко и только для записей данных, которые мне действительно нужно зарезервировать. Я переношу длинные отчеты в реплики или аналитические системы, чтобы рабочие нагрузки OLTP не ждали. Я также регулярно проверяю, какие операторы удерживают необычно большое количество блокировок и почему. Если вы хотите углубиться в эту тему, вам следует прочитать Механизм хранения данных InnoDB и макеты индексов в контексте вашей собственной схемы до выхода следующего релиза.
Я минимизирую горячие точки, выбирая первичные ключи таким образом, чтобы нагрузка при записи не сходилась постоянно в конце монотонно растущего индекса. Я также разбиваю пакетные операции на небольшие фрагменты, чтобы избежать создания длинных эксклюзивных блокировок. С помощью этого инструмента блокировки длятся дольше, а количество конфликтов ощутимо снижается. Это снижает количество ошибок, и приложение реагирует более плавно. Таким образом, я открываю резервы без немедленного создания новых. Сервер наращивать.
Мониторинг и анализ: что я измеряю
Я начинаю с показателей времени ожидания блокировки, количества тупиковых ситуаций, длительных транзакций и наибольшего количества операторов по времени выполнения, чтобы выявить самые крупные. Рычаг узнайте. Схема производительности, SHOW ENGINE INNODB STATUS и журналы медленных запросов дают мне конкретные подсказки. Затем я просматриваю планы худших запросов и проверяю, отсутствуют ли индексы или фильтры. Как только я устраняю узкие места, я наблюдаю за эффектом в течение нескольких фаз нагрузки. Этот цикл измерений, изменений и проверок позволяет качество параллельность заметно возрастет.
Для получения достоверных данных мне нужны реалистичные тестовые данные и реальные шаблоны доступа, а не просто синтетические тесты с одиночными выстрелами. Профили нагрузки с одновременными сеансами показывают, как на самом деле работают блокировки. Такие тесты позволяют выявить скрытые "горячие точки", которые иначе становятся очевидными только на поздних этапах повседневной эксплуатации. Проверка релизов таким образом позволяет избежать неожиданностей в процессе эксплуатации. Это экономит затраты, время и нервы в долгосрочной перспективе. Посмотреть.
Среда хостинга и производительность базы данных
Хороший параллелизм зависит от быстрого оборудования, потому что каждая задержка ввода-вывода увеличивает Продолжительность приманки. Я обращаю внимание на быстрые SSD, достаточный объем оперативной памяти для буферных пулов и короткие пути между приложением и базой данных. Резервы процессора помогают выполнять параллельные запросы без перегрузок. Я последовательно снижаю сетевые задержки, чтобы обходные пути не увеличивали эффективное время блокировки. Если вы будете соблюдать эти условия, то получите быстрое реагирование Услуги и меньше отмен.
Также важны разумные пути масштабирования: Реплики чтения для отчетов, шардинг для очень больших наборов данных и отдельные системы для аналитических нагрузок. Я выбираю подходящий вариант только после измерений и не тороплю события. Архитектура и дисциплина SQL дополняют друг друга; без согласованных запросов аппаратное обеспечение компенсирует их только в краткосрочной перспективе. Благодаря гармоничному сочетанию я значительно сокращаю количество конфликтов блокировок. В результате мы получаем надежную работу пользователей, не бросающуюся в глаза Время ожидания.
Типы блокировок в InnoDB в деталях
Для принятия правильных решений о путях запросов я точно знаю, какие типы блокировок наиболее важны: блокировки записей блокируют отдельные записи индекса, блокировки пробелов блокируют промежуток между двумя записями индекса, а блокировки следующего ключа представляют собой комбинацию обоих типов. Последние предотвращают появление фантомов во время сканирования диапазона. Блокировки Insert-intention сигнализируют о намерениях вставки и позволяют выполнять параллельные вставки в разные промежутки без излишних помех друг другу. Для уникального поиска по уникальному индексу InnoDB сводит блокировку к блокировке записи, что минимизирует блокировку. Как только в игру вступает предикат диапазона (BETWEEN, >, LIKE с префиксом), часто вступает в силу блокировка по следующему ключу и, следовательно, более широкий диапазон блокировки.
Поэтому я планирую запросы так, чтобы они по возможности попадали на уникальные или высокоселективные индексы. WHERE - не единственный решающий фактор: Последовательности ORDER BY, LIMIT и JOIN также влияют на выбранный путь к индексу - и, следовательно, на область блокировки. Целенаправленная переработка, использующая ORDER BY с подходящим индексом, позволяет избежать блокировок по следующему ключу и значительно сократить время ожидания.
Целенаправленное использование блокировки чтения
Блокировка чтения полезна, когда мне нужно зарезервировать строки или скоординировать конкурирующие обновления. В MySQL я использую:
- SELECT ... FOR UPDATE: эксклюзивная блокировка на чтение строк, подходит для резервирования перед обновлением.
- SELECT ... FOR SHARE (или LOCK IN SHARE MODE в старых версиях): общая блокировка для получения последовательных чтений с защитой от записи.
- NOWAIT и SKIP LOCKED: избегайте длительного ожидания - NOWAIT отменяет немедленно, SKIP LOCKED пропускает заблокированные линии.
Общий шаблон для очередей заданий:
НАЧАТЬ ТРАНЗАКЦИЮ;
SELECT id, payload
от заданий
WHERE status = 'ready'
ORDER BY priority, id
LIMIT 50
ДЛЯ ОБНОВЛЕНИЯ ПРОПУСТИТЬ БЛОКИРОВКУ;
-- пометить как 'обработка' и зафиксировать
UPDATE jobs SET status = 'processing' WHERE id IN (...);
COMMIT;Это позволяет мне обрабатывать грузы параллельно, не блокируя друг друга. Что еще важно: точные формулы WHERE, подходящий индекс (статус, приоритет, id) и короткие транзакции.
Понимание блокировок метаданных (MDL)
В дополнение к блокировкам строк существуют блокировки метаданных, которые координируют работу DDL и DML. Каждый выполняющийся запрос имеет MDL-блокировку на чтение соответствующих таблиц; DDL требует эксклюзивных MDL-блокировок. Поэтому неосторожно запущенный ALTER TABLE может ждать окончания длительных транзакций или отчетов - и наоборот, DDL, в свою очередь, блокирует новые обращения к DML. Поэтому я планирую изменения схемы вне основной нагрузки, сокращаю длительность транзакций и проверяю, не держат ли сессии таблицы открытыми в течение нескольких минут, перед развертыванием. Онлайн-варианты DDL облегчают многие проблемы, но не заменяют дисциплину, когда речь идет о времени транзакций. При мониторинге я специально слежу за ожиданием MDL, поскольку оно сигнализирует о перегрузке, которой можно избежать.
Внешние ключи, каскады и обязательность индексов
Внешние ключи повышают качество данных, но увеличивают площадь блокировок. InnoDB проверяет согласованность с помощью индексов - если они отсутствуют на столбцах с внешними ключами, есть риск широкого сканирования и длительных блокировок. Поэтому я слежу за тем, чтобы индексы имелись на каждом ссылающемся столбце. Каскадные обновления/удаления могут заблокировать несколько таблиц в транзакции и тем самым способствовать возникновению тупиковых ситуаций. Я определяю фиксированную последовательность доступа для всех затронутых таблиц и сохраняю изменения небольшими. Если каскады технически редки, я проверяю альтернативы: явные, короткие шаги с четкими условиями WHERE, чтобы длительность блокировки была предсказуемой.
Автоинкремент, горячие точки и объемные вставки
Монотонно растущие первичные ключи создают "горячую точку" в конце кластеризованного индекса. Там встречается множество параллельных вставок, что увеличивает время ожидания. Я разбрасываю ключи (например, использую ключи разделов или префиксные идентификаторы сущностей) или использую короткие и чистые по размеру партии. Я контролирую поведение автоинкремента с помощью режима блокировки: для OLTP я предпочитаю настройки, которые позволяют параллельные вставки и блокируют только на короткое время. При работе с большими партиями я проверяю, насколько быстрее работает путь, похожий на COPY, или небольшие повторяющиеся подмножества. Это по-прежнему важно: Создавайте индексы только после больших процессов загрузки или разгружайте вторичные индексы, чтобы уменьшить количество точек вставки.
Репликация и последовательное чтение
Я учитываю задержки при чтении из реплик: иначе отчет может увидеть более старые статусы. Для получения последовательных снимков я намеренно начинаю транзакции с WITH CONSISTENT SNAPSHOT и устанавливаю READ ONLY, когда происходит только чтение. Таким образом, я поддерживаю стабильное представление в нескольких операциях - без лишних блокировок. В то же время я забочусь о том, чтобы у приложения были терпимые пути на случай задержек репликации или, при необходимости, переключения на основной сервер, если важна абсолютная свежесть. Это сводит к минимуму неожиданности и объясняет кажущиеся „аномалии“, которые на самом деле являются просто задержками репликации.
Конфигурация и стратегии повторных попыток
Я разумно настраиваю время ожидания блокировки и обнаружение: Умеренное время ожидания innodb_lock_wait_timeout не позволяет сессиям блокироваться по несколько минут подряд. Я обнаруживаю тупики проактивно и делаю четкое различие: я кратко извлекаю ошибку 1213 (тупик) с помощью бэкоффа и джиттера; я оцениваю ошибку 1205 (таймаут ожидания блокировки) как сигнал к оптимизации пути запроса. innodb_deadlock_detect помогает при многих коротких транзакциях; при чрезвычайно высоком параллелизме расчет затрат и выгод может опрокинуться - тогда выравнивание горячих точек почти всегда лучший ответ, чем чистое вращение параметров.
Повторные попытки безопасны только в том случае, если операции идемпотентны. Я разрабатываю пути обновления таким образом, чтобы повторные попытки достигали одного и того же целевого состояния (например, с помощью столбцов версий, детерминированных наборов вместо инкрементов или чистых бизнес-событий). Таким образом, я предотвращаю двойные постинги и сохраняю устойчивость кода к неизбежным конфликтам.
Примеры: Партии без широких замков
Я разбиваю большие изменения по первичному ключу на небольшие куски, основанные на индексе:
-- Пример: удаление партиями
SET @last_id = 0;
WHILE 1 DO
DELETE FROM events
WHERE id > @last_id
ORDER BY id
LIMIT 1000;
SET @rows = ROW_COUNT();
IF @rows = 0 THEN LEAVE; END IF;
SET @last_id = (SELECT MAX(id) FROM events WHERE id <= @last_id + 1000);
END WHILE;Такая схема позволяет сократить время выполнения транзакций, уменьшить время удержания блокировки и дать возможность другим рабочим нагрузкам передохнуть. Я использую аналогичный подход для массовых обновлений: Сначала я выбираю целевые идентификаторы во временном наборе (или через окно LIMIT), затем пишу сфокусированную запись и быстро фиксирую.
Руководство по быстрой диагностике
Когда время ожидания увеличивается, я работаю в фиксированном порядке:
- Сузьте круг симптомов: Какие таблицы, какие утверждения, какое время?
- Сделайте видимыми цепочки ожидания: Определите data_locks/data_lock_waits и блокирующие PID в схеме производительности; также проверьте текущий статус InnoDB.
- Проверьте план запроса: Использует ли запрос ожидаемый индекс? Являются ли предикаты разрешимыми?
- Уменьшите область действия блокировки: Уточните WHERE, добавьте индексы, избегайте сканирования диапазона, ужесточите блокировку чтения.
- Сократите продолжительность транзакций: Уберите из транзакции взаимодействия и внешние вызовы, сократите наборы результатов.
- Повторите и измерьте: После изменений снова наблюдайте и сравнивайте пиковое время.
Этот процесс предотвращает слепые полеты. Вместо того чтобы увеличивать тайм-ауты, я устраняю их причины - более устойчиво и, как правило, быстрее.
Избегайте "подводных камней" в работе
Во время работы я обращаю особое внимание на три вещи: во-первых, я не останавливаю автокоммит по ошибке - это продлевает блокировки незаметно. Во-вторых, я не позволяю пулам соединений передавать транзакции, которые уже имеют открытые блокировки. В-третьих, я использую точки сохранения специально для частичных снятий, но не ожидаю, что они сократят время удержания блокировок: блокировки остаются заблокированными до тех пор, пока они не будут зафиксированы или откачены. Четкая дисциплина на прикладном уровне приносит прямые плоды в виде сокращения времени ожидания.
Коротко и ясно: Основные выводы
Блокировка строк обеспечивает согласованность данных, но только вместе с MVCC, Сила FOR UPDATE раскрывается при правильной изоляции и чистом дизайне индексов. Я делаю транзакции короткими, фильтрую конкретно и использую FOR UPDATE только там, где резервирование технически необходимо. Я уменьшаю конфликты с помощью последовательности доступа и четких повторных попыток в случае тупиковых ситуаций. Я выбираю уровни изоляции для каждого случая использования и наблюдаю за эффектами блокировок gap и next-key. Если вы будете придерживаться взвешенного подхода и регулярно подтягиваться, вы достигнете высоких показателей Concurrency без сюрпризов.
В конечном итоге важны три вещи: небольшие объекты блокировки, короткое время удержания и прослеживаемые пути запросов. Благодаря этим принципам рабочие нагрузки MySQL работают надежно даже при одновременной активности многих пользователей. Я полагаюсь на повторяющиеся тесты, значимые метрики и целенаправленную оптимизацию вместо масштабных перестроек. Это позволяет поддерживать корректность данных, низкое время отклика и редкие тупики. Это именно то, что каждая команда ожидает от отзывчивого База данных.
