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

Администрирование - Системное

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

См. также

Комментарии
Сортировка: Древо
1. Serg O. 131 28.11.17 11:34 Сейчас в теме
для программистов 1С сложновато... на первый взгляд
но на крайний случай конечно приходится лезть напрямую в SQL


Вот ещё хорошие статьи про блокировки от Бурмистрова Андрея (из команды gilev.ru)

https://infostart.ru/public/629017/

Обработка для просмотра блокировок SQL и управляемых в 1С
https://infostart.ru/public/557477/

другие статьи этого же автора - так же очень интересно и полезно почитать
2. PerlAmutor 27 28.11.17 19:24 Сейчас в теме
Что делать, когда SQL сервер запрос выполнил за 3 секунды, но сервер 1С этого не "прочухал" и тупо висит ничего не делая?
3. Darklight 6 29.11.17 17:23 Сейчас в теме
(2) Вероятно (если Вы уже проверяли этот запрос к консоле запросов SQL сервера и он там возвращает данные за те же 3 секунды), есть какая-то проблема с взаимодействием sql-сервера и сервера 1С. Может что-то с каналом связи, может что-то с драйверами. Может что-то вам подскажет технологический журнал 1С.
7. PerlAmutor 27 29.11.17 19:45 Сейчас в теме
(3) Я не внимательно изучил данные и не заметил, что 1С разбивает один единый запрос, где есть инструкция "ОБЪЕДИНИТЬ ВСЕ" и делает отдельные вызовы этих частей через хранимую процедуру "sp_executesql" помещая данные во временную таблицу. Но сегодня я дождался завершения запроса, он выполнялся 4 часа против 10 секунд на настольном компьютере. Сравнил количество данных во всех таблицах - практически идентичные цифры. Почему это происходит при очищенном кэше плана запросов непонятно, т.к. в базах кроме меня никого нет.
8. Darklight 6 30.11.17 10:25 Сейчас в теме
(7)У вас какая-то специфическая проблема - задайте свой вопрос на форуме инфостарта, изложив детали более подробно - Вам помогут.
4. Darklight 6 29.11.17 17:27 Сейчас в теме
Хорошая статья. Наконец-то выложили информацию как детализироваться от абстрактных дескрипторов ресурсов SQL сервера, до самих данных. Написано, конечно, немного сложновато, особенно под конец стать, и обрывается она как-то резко, с какой-то недосказанностью. Не хватает подведения итога, финального аккорда - поясняющего как это всё применять на практике. Но, автору, всё равно - СПАСИБО! Будет что покапать в дальнейшем...
5. Irwin 285 29.11.17 17:38 Сейчас в теме
(4) Статья писалась два года назад для личного блока в процессе подготовки к эксперту. Поэтому в ней ровно та информация, которая меня интересовала на тот момент. И ни о каком подведении итога тогда и не думал :)
6. Darklight 6 29.11.17 17:43 Сейчас в теме
(5)Ну, как пожелание, всё-таки немного причесать статью для аудитории инфостарта ;-)
9. kolya_tlt 11 30.11.17 11:13 Сейчас в теме
блин, нужен переводчик для статьи...
10. vasilev2015 647 24.01.18 09:47 Сейчас в теме
Иван, спасибо Вам за просветительскую деятельность. На прошлой неделе благодаря Вам сдал профа. (Кто знает-догадается.) Пусть мой голос станет юбилейным, пятидесятым для этой статьи ))
Оставьте свое сообщение