Блокировки
Блокировки – это механизм поддержки требования об изолированности транзакций
Блокировки классифицируются по типам, уровням изоляции и объектам, на которые накладываются блокировки.
В качестве объектов блокировок могут выступать следующие сущности:
- База данных (обозначается DB). При наложении блокировки на базу данных блокируются все входящие в нее таблицы.
- Таблица (обозначается TAB). При наложении блокировки на таблицу блокируются все экстенты данной таблицы, а также все ее индексы.
- Страница или Блок данных (обозначается PAG). При наложении блокировки на страницу (блок, в случае Oracle) блокируются все строки данной страницы.
Минимальная единица хранения данных в файле базы данных. Размер страницы в MS составляет 8 Кб, в Sybase и Oracle – 2, 8, 16 и т.д., обычно кратно размеру блока операционной системы
- Строка (обозначается RID или ROWID).
- Диапазон индекса (обозначается KEY). Блокируются данные, соответствующие диапазону индекса, на обновление, вставку и удаление.
При автоматическом определении объекта блокировки сервер должен выбрать наиболее подходящий с точки зрения производительности и параллельной работы пользователей.
Чем меньше детализация блокировки (строка – самая высокая степень детализации), тем ниже ее ресурсная стоимость, однако и ниже возможность параллельной работы пользователей.
Чем выше детализация блокировки, тем выше степень параллелизма транзакций, но тем больше блокировок требуется установить серверу, т.е. выделить больше ресурсов (время, память) для их создания, поддержки и удаления.
Обычно СУБД (в частности, MS SQL Server) поддерживает три основных типа блокировок:
- Разделяемая блокировка (Shared Lock)
Устанавливается при операции чтения данных. Гарантируется что данные, на которые она наложена, не будут изменены другой транзакцией. Однако чтение данных возможно.
В Oracle на сами данные при чтении не накладывается эта блоикровка, она устанавливается на таблицу (по избежание её удаления/изменения во время транзакции)
- Монопольная блокировка (Exclusive Lock)
Применяется при изменении данных. Если на ресурс установлена монопольная блокировка, гарантируется, что другие транзакции не могут не только изменять данные, но даже читать их.
- Блокировка обновления (Update Lock),
Является промежуточной между разделяемой и монопольной блокировкой. Устанавливается при намерении транзакции изменить данные, но при этом, само изменение пока не происходит (например не все строки-кандидаты считаны запросом). В этом случае другим транзакциям разрешается устанавливать разделяемые блокировки, но не позволяется устанавливать монопольные до тех пор, пока транзакция не завершится.
Блокировки намерений
Блокировки намерений всегда устанавливаются на таблицу или страницу и предназначены для повышения производительности работы менеджера блокировок.
Чтобы наложить монопольную блокировку на страницу данных, сервер должен убедиться, что ни на одну из записей, входящих в эту страницу, никакая блокировка не наложена. То есть необходимо перебрать все записи, входящие в страницу, и проверить их на наличие блокировок. То же самое, только в гораздо большем объеме, необходимо делать и для таблицы. Чтобы оптимизировать этот процесс, при наложении блокировки на запись (страницу) сервер ставит блокировку намерения на страницу (таблицу) и при проверки совместимости читает лишь блокировки намерения.
- Разделяемая блокировка намерения (Intent Shared Lock)
Устанавливается при операции разделяемого чтения данных или чтения данных для обновления.
Этот тип блокировки совместим со всеми остальными режимами, кроме монопольной блокировки
- Монопольная блокировка намерения (Intent Exclusive Lock)
Монопольная блокировка намерений запрещает доступ к данным какого-либо вида (даже на чтение) и позволяет повысить эффективность работы при множественном обновлении данных. В отличие от простой монопольной блокировки она совместима сама с собой. Т.е. другая транзакция также может наложить монопольную блокировку намерений на те же ресурсы, что и первая транзакция.
На данном слайде вы можете посмотреть матрицу совместимости блокировок, можно остановить видео и посмотреть ее более подробно
Блокировки индексов
Если в таблице присутствуют индексы и оптимизатор выбрал один из них для чтения или отбора записей для обновления (модификации), то применяются ещё два вида блокировок:
- Блокировка записи индекса
- Блокировка диапазона индекса
Блокировка записи индекса эквивалентна блокировке строки, т.е. менеджер блокировок будет блокировать строку, если в таблице отсутствует индекс, или же будет блокировать запись индекса, которая соответствует данной строке. Поскольку индекс может быть построен по неуникальному полю, блокируемая запись не может быть идентифицирована только записью индекса, поэтому для вычисления локатора ресурса применяется алгоритм хеширования идентификатора строки (RID) и записи индекса.
Если бы в таблице из предыдущего примера существовал индекс по полю i, то в предыдущей таблице блокировок в колонке Resource фигурировали бы хеш-значения записей индекса, а тип ресурса был – KEY.
Блокировка диапазона индекса позволяет наложить блокировку не на конкретные строки или записи индекса, а на диапазон строк, что позволяет блокировать записи по некоторому логическому условию. Применяется для поддержания уровня изоляции SERIALIZABLE (в таблицах без индексов при этом уровне изоляции блокируется ВСЯ таблица)
В идеальном случае результат выполнения транзакции не должен зависеть от остальных транзакций, но в этом случае накладываются сильные ограничения на параллельную обработку, практически выстраивая транзакции в очередь. В большинстве случаев такая строгость не нужна, и поэтому были введены так называемые «уровни изоляции» (Isolation Level), которые определяют степень параллелизма выполнения транзакций.
В стандарте ANSI SQL вводятся четыре уровня изоляции. Они привязаны к четырем «феноменам» – нарушениям изолированности транзакций: грязная запись, грязные чтения, неповторяющиеся чтения и фантомы. Повышение уровня изоляции последовательно устраняет эти аномалии одну за другой.
- Read Uncommitted – «грязное чтение» (Dirty Read), самый низкий уровень изоляции. Позволяет читать "грязные" данные незафиксированых транзакций. Т.е. если первая транзакция запишет какие-то данные, вторая их прочитает, а потом первая транзакция будет отменена, то получится, что вторая транзакция прочитала данные, которые никогда не существовали.
- Read Committed – грязное чтение невозможно, то есть второй транзакции не дадут прочитать данные первой до тех пор, пока первая транзакция не зафиксируется. Но при этом уровне изоляции все еще возможна аномалия неповторяющегося чтения. Если первая транзакция один раз прочитала данные, а потом вторая их изменила и зафиксировалась, то повторное чтение тех же данных первой транзакцией вернет уже измененные данные.
- Repeatable Read – этот уровень решает предыдущую проблему, но при этом возможно появление фантомов. Изменение однажды прочитанных первой транзакцией данных другими транзакциями (до фиксации первой) невозможно. Однако если первая транзакция сделала выборку по какому-то условию, а потом вторая транзакция добавила новые данные, этому условию удовлетворяющие, и зафиксировалась, то повторная выборка первой транзакцией по тому же условию вернет в том числе и добавленные данные – фантомы.
- Serializable – при этом уровне изоляции никакие фантомы невозможны в принципе, равно как и другие феномены, даже такие, которых еще не придумали. Этот уровень изоляции ни на какие феномены не опирается, просто требуется, чтобы результат параллельного выполнения транзакций был таким же, как если бы они выполнялись последовательно.
В таблице показано как уровни изоляции обеспечивают требования изолированности транзакций
В Oracle явно доступно всего два уровня изоляции: по умолчанию используется READ_COMMITTED, но при желании вы можете установить SERIALIZABLE
На уровне операторов (SELECT, UPDATE и т.д.) у вас по умолчанию уже есть REPEATABLE_READ, т.е. в рамках одного оператора вы всегда получаете согласованное чтение, что достигается за счет сегмента отката.
Даже если в середине вашего запроса другая транзакция переведет некоторую суммы с первого счета на последний, вы в итоге все равно получите данные актуальные на начало вашего запроса, так как дойдя до последний строчки ваш SELECT увидит, что строчка была изменена, пойдет в сегмент отката и прочитает данные, которые были в этой ячейке на момент начала выполнения запроса.
Однако в Oracle в данном случае есть опасность получить ORA-01555: snapshot too old
Взаимоблокировка (deadlock, «мертвая блокировка» или «тупиковая ситуация») – это ситуация, когда транзакции блокируют друг друга таким образом, что дальнейшее выполнение невозможно.
В силу протокола двухфазной блокировки ни одна из участвующих во взаимоблокировке транзакций не может отпустить уже захваченные ей ресурсы до того, как наложит блокировки на все, что ей необходимо. А получить все необходимые ресурсы мешают уже наложенные блокировки.
Рассмотрим суть взаимоблокировки на двух транзакциях (t1 и t2), обращающихся к двум ресурсам (A и B). Предположим, что транзакция t1 блокирует ресурс A, после чего транзакция t2 блокирует ресурс B. Если теперь транзакция t1 попытается получить доступ к ресурсу B, а транзакция t2 – к ресурсу А, возникнет взаимоблокировка.
В СУБД предусмотрен механизм автоматического определения взаимоблокировок и разрешения конфликтов. Специальный менеджер взаимоблокировок проверяет наличие двух и более ожидающих транзакции и вычисляет зависимости между ними. Если оказывается, что транзакции зависят друг от друга, т.е. для получения блокировки одной из них необходимо снять блокировку другой транзакции, фиксируется факт возникновения взаимоблокировки. Единственный способ устранения проблемы заключается в отмене одной из транзакций.
Для каждой транзакции вводится понятие цены или приоритета. Как правило в качестве «жертвы выбирается» транзакция, на которую было затрачено меньше всего ресурсов, т.е. цена отката которой – меньше.
- Первый оператор транзакции T1 сканирует таблицу накладывая блокировки обновления (U) и тут же снимает их, если запись не подходит под условия. Встретив 4-ю запись накладывает монопольную (X) блокировку и удерживает до конца(!) транзакции
- T2 делает тоже самое, встретив 2-ю запись накладывает X-блокировку и сканирует дальше. Встретив 4-ю запись останавливается и ждёт T1, поскольку U и X блокировки несовместимы
- Стартует второй оператор T1, опять начинает сканировать сверху и блокируется X-блокировкой, только что наложенной на 2-ю запись транзакцией T2.
Взаимоблокировка!
Причина: не использовались индексы!
Почему нужно устранять причины взаимоблокировок
- Лишняя нагрузка на журнал транзакций (тратятся ресурсы на откат, причём вероятно неоднократный, поскольку приложения обычно перехватывают ошибку взаимоблокировки и пытаются перезапустить транзакцию)
- Менеджер, отслеживающий замкнутые циклы в графе ожидания, не работает непрерывно, таким образом, взаимоблокировка не обнаруживается мгновенно. Это снижает производительность системы в целом из-за того, что ни в чем неповинные транзакции вынуждены ждать, пока менеджер не отменит одну из намертво заблокированных.
- Общая способность к масштабированию системы резко снижается, т.к. с увеличением нагрузки (объёма данных или количества одновременно работающих пользователей) число взаимоблокировок будет лишь возрастать, причём не линейно. А следовательно, будет расти и количество ожидающих транзакций, что в итоге может привести к полной неработоспособности системы.
Пути устранения взаимоблокировок
- Использовать одинаковый порядок доступа к одинаковым данным в различных процедурах
- Уменьшать сложность и продолжительность транзакций
- Использовать индексный поиск записей для чтения/модификации/удаления
- Избегать внутри транзакции повторных обращений к уже заблокированным ресурсам с более сильной блокировкой
- Использовать минимальный уровень изоляции транзакций (уменьшение блокировок – уменьшение ожиданий освобождения ресурсов – уменьшение вероятности взаимоблокировок). Однако следует помнить, что последствия рассогласованности данных гораздо серьёзнее, чем последствия взаимоблокировок.
- Как «последний рубеж» по защите нервов пользователя, в приложениях рекомендуется перехват ошибки сервера 1205 (взаимоблокировка) и пытаться перезапускать транзакцию, однако рекомендуется делать это (скрытно от пользователя) не более 3 раз подряд, т.к. этот шаг не устраняет первопричины явления, а лишь маскирует его.
Если статья была Вам полезна, ставьте пальцы вверх и подписывайтесь. Оставляйте свои пожелания и вопросы в комментариях, с удовольствием отвечу.
#it #sql #sql для новичков #блокировки