SQL-Ex blog

Что такое блокировки в SQL Server

Блокировки играют важную роль в обеспечении свойств транзакции ACID. Различные команды SELECT, DML и DDL генерируют блокировки на ресурсы. Например, в процессе обновления строки таблицы накладывается блокировка, гарантирующая, что те же самые данные не могут читаться или модифицироваться в то же время. Это обеспечивает чтение и модификацию только зафиксированных данных в базе данных. Последующее обновление может иметь место после первоначального, не они не могут конкурировать. Каждая транзакция должна полностью завершиться или откатиться, никаких полумер.

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

Типы блокировок

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

1. Если данные не модифицируются, конкурирующие пользователи могут читать одни и те же данные.
   
   
   
   1. Пока уровень изоляции является значением по умолчанию для SQL Server (Read Commited - чтение зафиксированных данных).
   
   
   2. Однако это поведение меняется при более высоком уровне изоляции, таком как сериализуемый.
   
   


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

Что такое блокирование

Блокирование - это реальное воздействие блокировок на ресурсы и другие запрошенные типы блокировок, несовместимые с существующей блокировкой. Вам необходимо иметь (запросить) блокировку для того, чтобы получить блокирование. В сценарии, когда обновляется строка, тип блокировки IX или X означает, что одновременные операторы чтения будут блокироваться до тех пор, пока блокировка модификации данных не будет снята. Подобным образом, чтение данных блокирует данные от выполнения модификации. Опять же имеются исключения, в зависимости от используемого уровня изоляции.

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

Проблемы возникают, когда блокирование затягивается на длительное время, т.к. это приводит к замедлению выполнения транзакций. Типичный тайм-аут соединения для веб-приложений составляет 30 секунд, поэтому превышение приводит к множеству исключений. Даже при 10 или 15 секундных задержках пользователи могут быть разочарованы. Очень длительное блокирование может остановить весь сервер на время, пока главные блокировщики не будут убраны.

Обнаружение блокирований

Я просто использую хранимую процедуру Адама Мачаника sp_whoisactive. Вы можете использовать sp_who2, если принципиально не используете сторонние скрипты, но, на всякий случай, это процедура написана на чистом T-SQL.

EXEC sp_whoisactive @find_block_leaders = 1

Убивать или не убивать

Иногда у вас нет других вариантов кроме как убить процесс, чтобы снять блокирование, но это нежелательно. Обычно я с меньшим недовольством убиваю запрос на выборку, если он вызывает блокирование, поскольку это не приведет к сбою транзакции DML. Это может просто означать сбой отчета или запроса пользователя.

Множественные идентичные блокировщики

Если имеются множественные блокировщики, и они все подобны или идентичны, это может означать, что конечный пользователь повторно выполняет что-то, что вызывает ожидание в слое приложения. Эти тайм-ауты приложения не коррелируют с тайм-аутами SQL, поэтому возможно, что пользователь просто нажимает F5, что, очевидно, только усугубляет проблему. Мне значительно проще убирать эти процессы, но важно сообщить по возможности конечному пользователю, чтобы он не повторял то же самое.

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

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

Тупик имеет место, когда два или более процесса ожидают на одних и тех же ресурсах, когда другой завершится, чтобы продолжить свою работу. В подобном сценарии что-то требуется предпринять, или они будут стоять в ожидании до скончания времен. SQL Server разрешает это выбором жертвы, обычно менее дорогой транзакции, и откатывает её. Это похоже на автоматическое завершение одного из ваших блокирующих запросов, чтобы все снова заработало. Это далеко от идеала, приводит к исключениям и может означать, что некоторые данные, предназначенные для вашей базы данных, никогда в неё не попадут.

Как проверить наличие тупика

Мне нравится использовать процедуру sp_blitzlock Брента Озара. В пожарном режиме я просто проверю предыдущий час. Вы также можете выбрать тупики из журнала ошибок SQL Server или же установить расширенные события для их захвата.

-- Тупики последнего часа

DECLARE @StartDateBlitz datetime = (SELECT DATEADD(HH,-1,GETDATE())),@EndDateBlitz DATETIME = (SELECT GETDATE())

EXEC sp_BlitzLock @EndDate = @EndDateBlitz, @StartDate = @StartDateBlitz

Моделирование блокирования

Если вы хотите смоделировать блокирование, то можете попробовать сделать это на базе данных Wide World Importers.

/*

   Выполните каждый из этих запросов по порядку в различных окнах SSMS.

*/

-- Запрос 1 (Этот беззаботный персонаж запустил, но не зафиксировал свое обновление)

BEGIN TRANSACTION

UPDATE [WorldWideImporters].[Sales].[Customers]

SET CustomerName = 'SpinTail Toys (Head Office)'

WHERE customerID  = 1

-- COMMIT

-- Выполните этот commit выше только после того, как будут выполнены все запросы и вы увидите блокирование. -- Запрос 2 сразу выполнится.

-- Запрос 2 (Я просто хочу получить результаты выборки, но незавершенная транзакция блокирует меня).

SELECT *

  FROM [WorldWideImporters].[Sales].[Customers]

WHERE customerID  = 1

-- Запрос 3 (Проверяем wait_info)

USE DBA

EXEC sp_whoisactive @find_block_leaders = 1

-- Вы должны увидеть тип ожидания LCK_M_S для вашего запроса select. Это означает, что поток ожидает получения разделяемой блокировки.

Изображение ниже показывает рядом вывод трех запросов. Запрос 1 завершается быстро, но отмечается, что он незафиксирован. Запрос 2 не завершается, пока запрос 1 не будет зафиксирован или сделан откат. Выполнение запроса 3 (sp_whoisactive) позволяет узнать, какие процессы вызывают блокирование, и какие заблокированы.



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