Как посмотреть, какие данные заблокированы в СУБД MS SQL Server

Статья актуальна для MS SQL Server 2012.

Смотрим блокировки

Иногда требуется посмотреть, какие объекты и данные заблокированы и какие блокировки на этих объектах стоят (речь идет только о транзакционных блокировках). В SQL Server для этих целей существует динамическое представление sys.dm_tran_locks. Оно возвращает сведения об активных в данный момент в SQL Server ресурсах диспетчера блокировок.

Следующий запрос

SELECT * FROM sys.dm_tran_locks

выведет таблицу:

Нас интересуют следующие столбцы:

• resource_type - тип ресурса. Значение может быть одним из следующих: DATABASE, FILE, OBJECT, PAGE, KEY, EXTENT, RID, APPLICATION, METADATA, HOBT или ALLOCATION_UNIT. Основные из них это - DATABASE - сама база данных; OBJECT - таблица или любой другой объект; PAGE - страница файла БД; KEY - указатель на ключ индекса; EXTENT - экстент файла БД; RID - физическая строка в куче.
• resource_database_id - идентификатор базы данных.
• resource_description - описание ресурса. Для ресурса типа PAGE в этом поле хранится адрес страницы в формате <file_id>:<page_in_file>; для KEY будет указан хэш ключевых столбцов; для RID адрес строки вида <file_id>:<page_in_file>:<row_on_page>.
• resource_associated_entity_id - идентификатор сущности, с которой связан ресурс. Для OBJECT - это ИД объекта; для KEY, PAGE и RID - идентификатор HoBt.
• request_mode - тип блокировки. Как раз в этой колонке указано, какая блокировка наложена на ресурс - S, U, X и пр.

Смотрим какие объекты заблокированы

Представление sys.dm_tran_locks выдает результат в неудобном для чтения виде. Одни идентификаторы и ничего более. Чтобы получить более наглядное отображение, необходимо воспользоваться еще несколькими инструментами SQL Server.

Название базы данных из ее идентификатора можно получить через функцию DB_NAME(database_id). Для обратного преобразования - DB_ID('database_name'). Для объектов существуют аналогичные функции OBJECT_NAME(object_id) и OBJECT_ID( 'database_name.object_name').

Чтобы посмотреть, какие данные хранятся на странице, достаточно ИД файла и ИД страницы. При чтении будет указан и объект, к которому эта страница относится, и сами данные. Но для индексов необходимо узнать еще имя самого индекса, чтобы можно было выполнить запрос.

Узнать, к какому объекту относится элемент блокировки, у которого указан только HoBt, можно по представлению sys.partitions. Оно содержит нужные нам колонки:

• object_id - идентификатор объекта, к которому относится сущность.
• index_id - идентификатор индекса объекта. Нужен для получения названия индекса для типа ресурса KEY. 0 - означает кучу, 1 - кластерный индекс, все остальные значения относятся к некластерным индексам.
• hobt_id - идентификатор сущности.

По index_id и object_id уже можно узнать имя индекса, по ключу которого установлена блокировка. Для этого воспользуемся еще одним представлением - sys.indexes. От него нам нужны следующие колонки:

• object_id - идентификатор объекта, к которому относится индекс.
• index_id - идентификатор индекса. Его мы знаем из таблицы sys.partitions.
• name - имя индекса. Если тип индекса 0 (куча), то имя будет Null.

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

SELECT 
    CASE locks.resource_type
		WHEN N'OBJECT' THEN OBJECT_NAME(locks.resource_associated_entity_id)
		WHEN N'KEY'THEN (SELECT OBJECT_NAME(object_id) FROM sys.partitions WHERE hobt_id = locks.resource_associated_entity_id)
		WHEN N'PAGE' THEN (SELECT OBJECT_NAME(object_id) FROM sys.partitions WHERE hobt_id = locks.resource_associated_entity_id)
		WHEN N'HOBT' THEN (SELECT OBJECT_NAME(object_id) FROM sys.partitions WHERE hobt_id = locks.resource_associated_entity_id)
		WHEN N'RID' THEN (SELECT OBJECT_NAME(object_id) FROM sys.partitions WHERE hobt_id = locks.resource_associated_entity_id)
		ELSE N'Unknown'
    END AS objectName,
    CASE locks.resource_type
		WHEN N'KEY' THEN (SELECT indexes.name 
							FROM sys.partitions JOIN sys.indexes 
								ON partitions.object_id = indexes.object_id AND partitions.index_id = indexes.index_id
							WHERE partitions.hobt_id = locks.resource_associated_entity_id)
		ELSE N'Unknown'
    END AS IndexName,
    locks.resource_type,
	DB_NAME(locks.resource_database_id) AS database_name,
	locks.resource_description,
	locks.resource_associated_entity_id,
	locks.request_mode
FROM sys.dm_tran_locks AS locks
	WHERE locks.resource_database_id = DB_ID(N'database_name')

Смотрим, какие данные заблокированы. Тип ресурса KEY, RID

Для типа ресурса KEY в поле resource_description в представлении sys.dm_tran_locks хранится хэш ключевых столбцов индекса. Для каждой записи индекса SQL Server вычисляет хэш. Он не повторяется, даже если все значения в колонках одинаковы, т.к. для каждой строки добавляется уникальный идентификатор, который участвует в формировании хэша. Посмотреть хэши строк таблицы (если есть кластерный индекс) или записей индекса можно с помощью виртуальной колонки %%lockres%%. Если кластерного индекса нет, то вместо хэша выведется адрес строки вида <file_id>:<page_in_file>:<row_on_page> (который нам нужен для типа ресурса RID). Выводится в запросе как и обычная колонка:

SELECT %%lockres%% AS lockres, * FROM dbo.table_name (NOLOCK)

Результат вывода для таблицы без кластерного индекса:

Для таблицы с кластерным индексом:

Чтобы посмотреть хэши по записям индекса, необходимо выполнить запрос:

SELECT 
	%%lockres%% AS lockres,
	*
FROM
	dbo.table_name WITH (INDEX (index_name) NOLOCK)

Теперь, чтобы получить ключ индекса, достаточно выполнить запрос с условием по виртуальной колонке.

Например, для типа ресурса KEY:

SELECT 
	%%lockres%% AS lockres,
	*
FROM
	dbo._AccumRg7528 WITH (INDEX (_AccumR7528_ByProperty7546_RTRN) NOLOCK)
WHERE
	%%lockres%% = '(143f95858242)'

Для RID такой:

SELECT 
	%%lockres%% AS lockres,
	*
FROM
	dbo._InfoRg6407 (NOLOCK)
WHERE
	%%lockres%% = '1:115919:16'

Смотрим, какие данные заблокированы. Тип ресурса PAGE

Чтобы посмотреть, что хранится в странице файла базы данных, воспользуемся функцией DBCC PAGE (). Принимает параметры: DBCC PAGE(db_name|db_id, file_id, page_id, print_option). Последним параметром указывается формат вывода из следующих доступных значений:

• 0 — только заголовок;
• 1 — заголовок, дампы и индекс слотов;
• 2 — заголовок и полный дамп;
• 3 — заголовок и максимальная детализация для каждого слота.

Нам интересен формат вывода 3. Перед использованием функции необходимо включить флаг трассировки: DBCC TRACEON(3604), иначе вместо результата увидите только надпись, что команда выполнилась, и ничего более.

Если выполнить команду:

DBCC PAGE('database_name', 1, 423, 3)

то мы скорее всего увидим результат в виде текста:

Это не очень удобно, поэтому команду DBCC PAGE удобнее всего выполнять с опцией WITH TABLE RESULT:

DBCC PAGE('database_name', 1, 423, 3) WITH TABLERESULTS

Теперь та же информация представлена в виде таблицы:

Каждая страница (в общем виде) состоит из заголовка, раздела с данными и таблицы смещений. В заголовке стоит обратить внимание на поле m_type - тип страницы. 1 - означает страница с данными или кластерный индекс; 2 - страница с записями некластерного индекса; 10 - карта распределения индекса. После заголовка идут данные, распределенные по "слотам" (результат для страницы m_type = 1):

И вот здесь мы уже видим наши данные, которые хранятся в самой таблице. В моем случае это регистр сведений, у которого два измерения с типом Строка (поля _Fld11, _Fld12) и ресурс с типом Число (_Fld13).

Если на странице расположены записи индекса (m_type = 2), то в таблице ключей записи мы не увидим. Они выведутся в другой таблице уже без дополнительной информации (заголовков и пр.):

Получение структуры хранения базы данных

Чтобы получить размещение базы данных по страницам, можно воспользоваться функцией-представлением sys.dm_db_database_page_allocations. Принимает следующие аргументы: sys.dm_db_database_page_allocations(db_id , table_id , index_id , partidion_id , mode_option). В последнем аргументе передается формат вывода: ‘DETAILED’ или ‘LIMITED’.

Использовать в обычном SELECT-запросе:

SELECT
	OBJECT_NAME(pages.object_id) AS object,
	*
FROM
	sys.dm_db_database_page_allocations(DB_ID('DBCC_PAGE'), NULL ,NULL,NULL,'DETAILED') AS pages

Результат:

В колонке allocated_page_file_id  будет указан адрес страницы.