SQL-Ex blog
Блокировки, блокирование и тупики в SQL Server
Терминология имеет значение: Locks, blocks и deadlocks
Что такое блокировки в SQL Server
Блокировки играют важную роль в обеспечении свойств транзакции ACID. Различные команды SELECT, DML и DDL генерируют блокировки на ресурсы. Например, в процессе обновления строки таблицы накладывается блокировка, гарантирующая, что те же самые данные не могут читаться или модифицироваться в то же время. Это обеспечивает чтение и модификацию только зафиксированных данных в базе данных. Последующее обновление может иметь место после первоначального, не они не могут конкурировать. Каждая транзакция должна полностью завершиться или откатиться, никаких полумер.
Следует отметить, что уровни изоляции могут оказывать влияние на поведение чтения и записи, но так описанное выше обычно работает, когда используется уровень изоляции по умолчанию.
Типы блокировок
- Если данные не модифицируются, конкурирующие пользователи могут читать одни и те же данные.
- Пока уровень изоляции является значением по умолчанию для SQL Server (Read Commited — чтение зафиксированных данных).
- Однако это поведение меняется при более высоком уровне изоляции, таком как сериализуемый.
Что такое блокирование
Блокирование — это реальное воздействие блокировок на ресурсы и другие запрошенные типы блокировок, несовместимые с существующей блокировкой. Вам необходимо иметь (запросить) блокировку для того, чтобы получить блокирование. В сценарии, когда обновляется строка, тип блокировки IX или X означает, что одновременные операторы чтения будут блокироваться до тех пор, пока блокировка модификации данных не будет снята. Подобным образом, чтение данных блокирует данные от выполнения модификации. Опять же имеются исключения, в зависимости от используемого уровня изоляции.
Таким образом, блокировка — это вполне естественное явление в SQL Server. Фактически, это жизненно важно для обеспечения ACID-транзакций. На хорошо оптимизированных системах это трудно заметить и не вызывает проблем.
Проблемы возникают, когда блокирование затягивается на длительное время, т.к. это приводит к замедлению выполнения транзакций. Типичный тайм-аут соединения для веб-приложений составляет 30 секунд, поэтому превышение приводит к множеству исключений. Даже при 10 или 15 секундных задержках пользователи могут быть разочарованы. Очень длительное блокирование может остановить весь сервер на время, пока главные блокировщики не будут убраны.
Обнаружение блокирований
Я просто использую хранимую процедуру Адама Мачаника sp_whoisactive. Вы можете использовать sp_who2, если принципиально не используете сторонние скрипты, но, на всякий случай, это процедура написана на чистом T-SQL.
Убивать или не убивать
Иногда у вас нет других вариантов кроме как убить процесс, чтобы снять блокирование, но это нежелательно. Обычно я с меньшим недовольством убиваю запрос на выборку, если он вызывает блокирование, поскольку это не приведет к сбою транзакции DML. Это может просто означать сбой отчета или запроса пользователя.
Множественные идентичные блокировщики
Если имеются множественные блокировщики, и они все подобны или идентичны, это может означать, что конечный пользователь повторно выполняет что-то, что вызывает ожидание в слое приложения. Эти тайм-ауты приложения не коррелируют с тайм-аутами SQL, поэтому возможно, что пользователь просто нажимает F5, что, очевидно, только усугубляет проблему. Мне значительно проще убирать эти процессы, но важно сообщить по возможности конечному пользователю, чтобы он не повторял то же самое.
Также может случиться, что фрагмент кода, который регулярно вызывается, начинает тормозить и не отрабатывает быстро. Вам нужно исправить это, или проблемы с блокированием никуда не исчезнут.
Что такое тупики?
Тупик имеет место, когда два или более процесса ожидают на одних и тех же ресурсах, когда другой завершится, чтобы продолжить свою работу. В подобном сценарии что-то требуется предпринять, или они будут стоять в ожидании до скончания времен. SQL Server разрешает это выбором жертвы, обычно менее дорогой транзакции, и откатывает её. Это похоже на автоматическое завершение одного из ваших блокирующих запросов, чтобы все снова заработало. Это далеко от идеала, приводит к исключениям и может означать, что некоторые данные, предназначенные для вашей базы данных, никогда в неё не попадут.
Как проверить наличие тупика
Мне нравится использовать процедуру sp_blitzlock Брента Озара. В пожарном режиме я просто проверю предыдущий час. Вы также можете выбрать тупики из журнала ошибок SQL Server или же установить расширенные события для их захвата.
Моделирование блокирования
Если вы хотите смоделировать блокирование, то можете попробовать сделать это на базе данных Wide World Importers.
Изображение ниже показывает рядом вывод трех запросов. Запрос 1 завершается быстро, но отмечается, что он незафиксирован. Запрос 2 не завершается, пока запрос 1 не будет зафиксирован или сделан откат. Выполнение запроса 3 (sp_whoisactive) позволяет узнать, какие процессы вызывают блокирование, и какие заблокированы.
Я попытался здесь объяснить разницу между блокировками, блокированием и тупиками. Я не вникал в технические детали уровней изоляции, типов ожидания или совместимости блокировок. Этот пост предназначен для новичков в администрировании баз данных и разработчиков с целью помочь им познакомиться с технологией и понять различное назначение блокировок, блокирования и тупиков.
Блокировки
Одновременный конкурентный доступ может вызывать разные отрицательные эффекты, например чтение несуществующих данных или потерю модифицированных данных. Рассмотрим следующий практический пример, иллюстрирующий один из этих отрицательных эффектов, называемый грязным чтением. Пользователь U1 из отдела кадров получает извещение, что сотрудник «Василий Фролов» поменял место жительства. Он вносит соответствующее изменение в базу данных для данного сотрудника, но при просмотре другой информации об этом сотруднике он понимает, что изменил адрес не того человека. (В компании работают два сотрудника по имени Василий Фролов.) К счастью, приложение позволяет отменить это изменение одним нажатием кнопки. Он нажимает эту кнопку, уверенный в том, что данные после отмены операции изменения адреса уже не содержат никакой ошибки.
В то же самое время пользователь U2 в отделе проектирования обращается к данным второго сотрудника с именем Василий Фролов, чтобы отправить ему домой последнюю техническую документацию, поскольку этот служащий редко бывает в офисе. Однако пользователь U2 обратился к базе данных после того, как адрес этого второго сотрудника с именем Василий Фролов был ошибочно изменен, но до того, как он был исправлен. В результате письмо отправляется не тому адресату.
Чтобы предотвратить подобные проблемы в модели пессимистического одновременного конкурентного доступа, которую мы кратко описали в предыдущей статье, каждая система управления базами данных должна обладать механизмом для управления одновременным доступом к данным всеми пользователями. Для обеспечения согласованности данных в случае одновременного обращения к данным несколькими пользователями компонент Database Engine, подобно всем СУБД, применяет блокировки. Каждая прикладная программа блокирует требуемые ей данные, что гарантирует, что никакая другая программа не сможет модифицировать эти данные. Когда другая прикладная программа пытается получить доступ к заблокированным данным для их модификации, то система или завершает эту попытку ошибкой, или заставляет программу ожидать снятия блокировки.
Блокировка имеет несколько разных свойств: длительность блокировки, режим блокировки и гранулярность блокировки. Длительность блокировки — это период времени, в течение которого ресурс удерживает определенную блокировку. Длительность блокировки зависит, среди прочего, от режима блокировки и выбора уровня изоляции.
Режимы блокировки и уровень гранулярности блокировки рассматриваются в следующих двух разделах. Последующее обсуждение относится к модели пессимистического одновременного конкурентного доступа. Модель оптимистического одновременного конкурентного доступа основана на управлении версиями строк и рассматривается в последующих статьях.
Режимы блокировки
Режимы блокировки определяют разные типы блокировок. Выбор определенного режима блокировки зависит от типа ресурса, который требуется заблокировать. Для блокировок ресурсов уровня строки и страницы применяются следующие три типа блокировок:
Разделяемая блокировка (shared lock)
Резервирует ресурс (страницу или строку) только для чтения. Другие процессы не могут изменять заблокированный таким образом ресурс, но, с другой стороны, несколько процессов могут одновременно накладывать разделяемую блокировку на один и тот же ресурс. Иными словами, чтение ресурса с разделяемой блокировкой могут одновременно выполнять несколько процессов.
Монопольная блокировка (exclusive lock)
Резервирует страницу или строку для монопольного использования одной транзакции. Блокировка этого типа применяется инструкциями DML (INSERT, UPDATE и DELETE), которые модифицируют ресурс. Монопольную блокировку нельзя установить, если на ресурс уже установлена разделяемая или монопольная блокировка другим процессом, т.е. на ресурс может быть установлена только одна монопольная блокировка. На ресурс (страницу или строку) с установленной монопольной блокировкой нельзя установить никакую другую блокировку.
Блокировка обновления (update lock)
Может быть установлена на ресурс только при отсутствии на нем другой блокировки обновления или монопольной блокировки. С другой стороны, этот тип блокировки можно устанавливать на объекты с установленной разделяемой блокировкой. В таком случае блокировка обновления накладывает на объект другую разделяемую блокировку. Если транзакция, которая модифицирует объект, подтверждается, и у объекта нет никаких других блокировок, блокировка обновления преобразовывается в монопольную блокировку. У объекта может быть только одна блокировка обновления.
Система баз данных автоматически выбирает соответствующий режим блокировки, в зависимости от типа операции (чтение или запись). Блокировка обновления применяется для предотвращения определенных распространенных типов взаимоблокировок.
Возможность совмещения разных типов блокировок приводится в таблице ниже:
| Разделяемая | Обновления | Монопольная | |
| Разделяемая | Да | Да | Нет |
| Обновления | Да | Нет | Нет |
| Монопольная | Нет | Нет | Нет |
Эта таблица интерпретируется следующим образом: предположим транзакция T1 имеет блокировку, указанную в заголовке соответствующей строки таблицы, а транзакция T2 запрашивает блокировку, указанную в соответствующем заголовке столбца таблицы. Значение «Да» в ячейке на пресечении строки и столбца означает, что транзакция T2 может иметь запрашиваемый тип блокировки, а значение «Нет», что не может.
Компонент Database Engine также поддерживает и другие типы блокировок, такие как кратковременные блокировки (latch lock) и взаимоблокировки (spin lock).
На уровне таблицы существует пять разных типов блокировок:
разделяемая (shared, S);
монопольная (exclusive, X);
разделяемая с намерением (intent shared, IS);
монопольная с намерением (intent exclusive, IX);
разделяемая с монопольным намерением (shared with intent exclusive, SIX).
Разделяемые и монопольные типы блокировок для таблицы соответствуют одноименным блокировкам для строк и страниц. Обычно блокировка с намерением (intent lock) означает, что транзакция намеревается блокировать следующий нижележащий в иерархии объектов базы данных ресурс. Таким образом, блокировка с намерением помещается на уровне иерархии объектов, который выше того объекта, который этот процесс намеревается заблокировать. Это является действенным способом узнать, возможна ли подобная блокировка, а также устанавливается запрет другим процессам блокировать более высокий уровень, прежде чем процесс может установить требуемую ему блокировку.
Возможность совмещения разных типов блокировок на уровне таблиц базы данных приведена в таблице ниже. Эта таблица интерпретируется точно таким же образом, как и предыдущая таблица.
| S | X | IS | SIX | IX | |
| S | Да | Нет | Да | Нет | Нет |
| X | Нет | Нет | Нет | Нет | Нет |
| IS | Да | Нет | Да | Да | Да |
| SIX | Нет | Нет | Да | Нет | Нет |
| IX | Нет | Нет | Да | Нет | Да |
Гранулярность блокировки
Гранулярность блокировки определяет, какой ресурс блокируется в одной попытке блокировки. Компонент Database Engine может блокировать следующие ресурсы: строки, страницы, индексный ключ или диапазон индексных ключей, таблицы, экстент, саму базу данных. Система выбирает требуемую гранулярность блокировки автоматически.
Строка является наименьшим ресурсом, который можно заблокировать. Блокировка уровня строки также включает как строки данных, так и элементы индексов. Блокировка на уровне строки означает, что блокируется только строка, к которой обращается приложение. Поэтому все другие строки данной таблицы остаются свободными и их могут использовать другие приложения. Компонент Database Engine также может заблокировать страницу, на которой находится подлежащая блокировке строка.
В кластеризованных таблицах страницы данных хранятся на уровне узлов (кластеризованной) индексной структуры, и поэтому для них вместо блокировки строк применяется блокировка с индексными ключами.
Блокироваться также могут единицы дискового пространства, которые называются экстентами и имеют размер 64 Кбайт. Экстенты блокируются автоматически, и когда растет таблица или индекс, то для них требуется выделять дополнительное дисковое пространство.
Гранулярность блокировки оказывает влияние на одновременный конкурентный доступ. В общем, чем выше уровень гранулярности, тем больше сокращается возможность совместного доступа к данным. Это означает, что блокировка уровня строк максимизирует одновременный конкурентный доступ, т.к. она блокирует всего лишь одну строку страницы, оставляя все другие строки доступными для других процессов. C другой стороны, низкий уровень блокировки увеличивает системные накладные расходы, поскольку для каждой отдельной строки требуется отдельная блокировка. Блокировка на уровне страниц и таблиц ограничивает уровень доступности данных, но также уменьшает системные накладные расходы.
Укрупнение блокировок
Если в процессе транзакции имеется большое количество блокировок одного уровня, то компонент Database Engine автоматически объединяет эти блокировки в одну уровня таблицы. Этот процесс преобразования большого числа блокировок уровня строки, страницы или индекса в одну блокировку уровня таблицы называется укрупнением блокировок (lock escalation). Порогом укрупнения называется граница, на которой система баз данных применяет укрупнение блокировок. Пороги укрупнения устанавливаются динамически системой и не требуют настройки. (В настоящее время пороговым значением укрупнения блокировок является 5000 блокировок.)
Основной проблемой, касающейся укрупнения блокировок, является то обстоятельство, что решение, когда осуществлять укрупнение, принимает сервер баз данных, и это решение может не быть оптимальным для приложений, имеющих различные требования. Механизм укрупнения блокировок можно модифицировать с помощью инструкции ALTER TABLE. Эта инструкция поддерживает параметр TABLE и имеет следующий синтаксис:
Параметр table является значением по умолчанию и задает укрупнение блокировок на уровне таблиц. Параметр auto позволяет компоненту Database Engine самому выбирать уровень гранулярности, который соответствует схеме таблицы. Наконец, параметр disable отключает укрупнение блокировок в большинстве случаев. (В некоторых случаях компоненту Database Engine требуется наложить блокировку на уровне таблиц, чтобы предохранить целостность данных.)
В примере ниже показана отмена укрупнения блокировок для таблицы Employee:
Настройка блокировок
Настройку блокировок можно осуществлять, используя подсказки блокировок (locking hints) или параметр LOCK_TIMEOUT инструкции SET. Эти возможности описываются в следующих разделах.
Подсказки блокировок (locking hints)
Подсказки блокировок задают тип блокировки, используемой компонентом Database Engine для блокировки табличных данных. Подсказки блокировки уровня таблиц применяются, когда требуется более точное управление типами блокировок, накладываемых на ресурс. (Подсказки блокировок перекрывают текущий уровень изоляции для сеанса.)
Все подсказки блокировок указываются в предложении FROM инструкции SELECT. Далее приводится список и краткое описание доступных подсказок блокировок:
UPDLOCK
Устанавливается блокировка обновления для каждой строки таблицы при операции чтения. Все блокировки обновления удерживаются до окончания транзакции.
TABLOCK
Устанавливается разделяемая (или монопольная) блокировка для таблицы. Все блокировки удерживаются до окончания транзакции.
ROWLOCK
Существующая разделяемая блокировка таблицы заменяется разделяемой блокировкой строк для каждой отвечающей требованиям строки таблицы.
PAGLOCK
Разделяемая блокировка таблицы заменяется разделяемой блокировкой страницы для каждой страницы, содержащей указанные строки.
NOLOCK
Синоним для READUNCOMMITTED, который мы рассмотрим при обсуждении уровней изоляции.
HOLDLOCK
Синоним для REPEATABLEREAD.
XLOCK
Устанавливается монопольная блокировка, удерживаемая до завершения транзакции. Если подсказка xlock указывается с подсказкой rowlock, paglock или tablock, монопольные блокировки устанавливаются на соответствующем уровне гранулярности.
READPAST
Указывает, что компонент Database Engine не должен считывать строки, заблокированные другими транзакциями.
Все эти параметры можно объединять вместе в любом имеющем смысл порядке. Например, комбинация подсказок TABLOCK с PAGLOCK не имеет смысла, поскольку каждая из них применяется для разных ресурсов.
Параметр LOCK_TIMEOUT
Чтобы процесс не ожидал освобождения блокируемого объекта до бесконечности, можно в инструкции SET использовать параметр LOCK_TIMEOUT. Этот параметр задает период в миллисекундах, в течение которого транзакция будет ожидать снятия блокировки с объекта. Например, если вы хотите чтобы период ожидания был равен восемь секунд, то это следует указать следующим образом:
Если данный ресурс не может быть предоставлен процессу в течение этого периода времени, инструкция завершается аварийно и выдается соответствующее сообщение об ошибке. Значение LOCK_TIMEOUT равное -1 (значение по умолчанию) указывает отсутствие периода ожидания, т.е. транзакция не будет ожидать освобождения ресурса совсем. (Подсказка блокировки READPAST предоставляет альтернативу параметру LOCK_TIMEOUT.)
Отображение информации о блокировках
Наиболее важным средством для отображения информации о блокировках является динамическое административное представление sys.dm_tran_locks. Это представление возвращает информацию о текущих активных ресурсах диспетчера блокировок. Каждая строка представления отображает активный в настоящий момент запрос на блокировку, которая была предоставлена или предоставление которой ожидается. Столбцы представления соответствуют двум группам: ресурсам и запросам. Группа ресурсов описывает ресурсы, на блокировку которых делается запрос, а группа запросов описывает запрос блокировки. Наиболее важными столбцами этого представления являются следующие:
resource_type — указывает тип ресурса;
resource_database_id — задает идентификатор базы данных, к которой принадлежит данный ресурс;
request_mode — задает режим запроса;
request_status — задает текущее состояние запроса.
В примере ниже показан запрос, использующий представление sys.dm_tran_locks для отображения блокировок в состоянии ожидания:
Взаимоблокировки
— это особая проблема одновременного конкурентного доступа, в которой две транзакции блокируют друг друга. В частности, первая транзакция блокирует объект базы данных, доступ к которому хочет получить другая транзакция, и наоборот. (В общем, взаимоблокировка может быть вызвана несколькими транзакциями, которые создают цикл зависимостей.) В примере ниже показана взаимоблокировка двумя транзакциями. (При использовании базы данных небольшого размера, одновременный конкурентный доступ процессов нельзя получить естественным образом, вследствие очень быстрого выполнения каждой транзакции. Поэтому в примере ниже используется инструкция WAITFOR, чтобы приостановить обе транзакции на десять секунд, чтобы эмулировать взаимоблокировку.)
Если обе транзакции в примере выше будут выполняться в одно и то же время, то возникнет взаимоблокировка и система возвратит следующее сообщение об ошибке:
Как можно видеть по результатам выполнения примера, система баз данных обрабатывает взаимоблокировку, выбирая одну из транзакций (на самом деле, транзакцию, которая замыкает цикл в запросах блокировки) в качестве «жертвы» и выполняя ее откат. После этого выполняется другая транзакция. На уровне прикладной программы взаимоблокировку можно обрабатывать посредством реализации условной инструкции, которая выполняет проверку на возврат номера ошибки (1205), а затем снова выполняет инструкцию, для которой был выполнен откат.
Вы можете повлиять на то, какая транзакция будет выбрана системой в качестве «жертвы» взаимоблокировки, присвоив в инструкции SET параметру DEADLOCK_PRIORITY один из 21 (от -10 до 10) разных уровней приоритета взаимоблокировки. Константа LOW соответствует значению -5, NORMAL (значение по умолчанию) — значению 0, а константа HIGH — значению 5. Сеанс «жертва» выбирается в соответствии с приоритетом взаимоблокировки сеанса.