sys.dm_tran_locks (Transact-SQL)

Применимо к:SQL ServerБаза данных SQL AzureУправляемый экземпляр SQL AzureAzure Synapse AnalyticsСистема платформы аналитики (PDW)Хранилище в Microsoft FabricБаза данных SQL в Microsoft Fabric

Возвращает сведения о ресурсах диспетчера блокировки в настоящее время в SQL Server. Каждая строка представляет текущий активный запрос диспетчеру блокировок о блокировке, которая была получена или находится в ожидании получения.

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

Permissions

На SQL Server и Управляемый экземпляр SQL sys.dm_tran_locks требуется VIEW SERVER STATE разрешение.

В базе данных SQL базовые, S0и S1 целей службы, а также для баз данных в эластичных пулах, требуется учетная запись администратора сервера сервера, учетная запись администратора Microsoft Entra или членство в роли сервера ##MS_ServerStateReader##. Для всех остальных целей службы базы данных SQL требуется разрешение VIEW DATABASE STATE на базу данных или членство в роли сервера ##MS_ServerStateReader##.

В SQL analytics endpoint и Warehouse in Microsoft Fabric членство в роли Viewer workspace — это минимальное разрешение, необходимое для просмотра sys.dm_tran_locks.

Разрешения для SQL Server 2022 и более поздних версий

В SQL Server 2022 и более поздних версиях sys.dm_tran_locks требуется VIEW SERVER PERFORMANCE STATE разрешение на сервере.

Замечания

Состояние предоставленного запроса показывает, что блокировка ресурса была предоставлена запрашивающему объекту. Ожидающий запрос обозначает, что запрос еще не был предоставлен. Следующие типы запросов ожидания возвращаются столбцом request_status :

• Состояние преобразованного запроса означает, что запрашивающий объект получил запрос ресурса и в настоящий момент ожидает, пока будет предоставлено обновление исходного запроса.

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

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

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

Управлять блокировкой операций считывания можно с помощью следующих средств.

• SET TRANSACTION ISOLATION LEVEL для указания уровня блокировки для сессии. Дополнительные сведения см. в разделе SET TRANSACTION ISOLATION LEVEL (Transact-SQL).

• Таблица блокировки подсказывает для указания уровня блокировки для отдельной ссылки на таблицу в предложении FROM . Сведения о синтаксисе и ограничениях см. в разделе "Подсказки таблиц" (Transact-SQL).

Ресурс, работающий под session_id одним из них, может иметь несколько предоставленных блокировок. Различные сущности, выполняемые в рамках одного сеанса, могут владеть блокировкой одного ресурса, а сведения отображаются в request_owner_type столбцах, request_owner_id возвращаемых sys.dm_tran_locks. Если существует несколько экземпляров одного и того же request_owner_type , request_owner_id столбец используется для различения каждого экземпляра. Для распределенных транзакций столбцы request_owner_typerequest_owner_guid отображают разные сведения об сущности.

Например, сеанс S1 владеет общей блокировкой; и транзакцией T1, которая выполняется в сеансе S1, также владеет общей блокировкойTable1Table1. В этом случае столбец, resource_description возвращаемый sys.dm_tran_locks двумя экземплярами одного ресурса. В столбце request_owner_type показан один экземпляр в виде сеанса и другой в виде транзакции. Кроме того, столбец resource_owner_id имеет разные значения.

Несколько курсоров, существующих в одном сеансе, неразличимы и обрабатываются как одна сущность.

Распределенные транзакции, которые не связаны со session_id значением, являются потерянными транзакциями и присваиваются значению session_id-2. Дополнительные сведения см. в разделе KILL (Transact-SQL).

Locks

Блокировки хранятся на ресурсах SQL Server, таких как строки, считываемые или измененные во время транзакции, чтобы предотвратить параллельное использование ресурсов различными транзакциями. Например, если исключительная (X) блокировка получена транзакцией на строку в таблице, никакая другая транзакция не сможет изменить эту строку, пока блокировка не будет освобождена. Минимизация использования блокировок повышает параллелизм, что может улучшить общую производительность.

Сведения о ресурсах

В следующей таблице перечислены ресурсы, представленные в столбце resource_associated_entity_id .

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

В следующей таблице представлен формат столбца resource_description для каждого типа ресурса.

Examples

А. Использование sys.dm_tran_locks с другими инструментами

В следующем примере выполняется работа со сценарием, в котором операция обновления блокируется другой транзакцией. С помощью sys.dm_tran_locks и других средств предоставляется информация о блокировке ресурсов.

USE tempdb;
GO

-- Create test table and index.
CREATE TABLE t_lock
    (
    c1 int, c2 int
    );
GO

CREATE INDEX t_lock_ci on t_lock(c1);
GO

-- Insert values into test table
INSERT INTO t_lock VALUES (1, 1);
INSERT INTO t_lock VALUES (2, 2);
INSERT INTO t_lock VALUES (3, 3);
INSERT INTO t_lock VALUES (4, 4);
INSERT INTO t_lock VALUES (5, 5);
INSERT INTO t_lock VALUES (6, 6);
GO

-- Session 1
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;

BEGIN TRAN
    SELECT c1
        FROM t_lock
        WITH(holdlock, rowlock);

-- Session 2
BEGIN TRAN
    UPDATE t_lock SET c1 = 10;

В следующем запросе отображаются сведения о блокировке. Значение для <dbid> этого параметра должно быть заменено значением database_id from sys.databases.

SELECT resource_type, resource_associated_entity_id,
    request_status, request_mode,request_session_id,
    resource_description
    FROM sys.dm_tran_locks
    WHERE resource_database_id = <dbid>;

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

SELECT object_name(object_id), *
    FROM sys.partitions
    WHERE hobt_id=<resource_associated_entity_id> ;

В следующем запросе показаны блокирующие сведения.

SELECT
    t1.resource_type,
    t1.resource_database_id,
    t1.resource_associated_entity_id,
    t1.request_mode,
    t1.request_session_id,
    t2.blocking_session_id
FROM sys.dm_tran_locks as t1
INNER JOIN sys.dm_os_waiting_tasks as t2
    ON t1.lock_owner_address = t2.resource_address;

Освобождение ресурсов с помощью отката транзакций.

-- Session 1
ROLLBACK;
GO

-- Session 2
ROLLBACK;
GO

B. Связывание сведений о сеансе с потоками операционной системы

В следующем примере возвращаются сведения, которые связываются session_id с идентификатором потока Windows. За производительностью потока можно наблюдать в системном мониторе Windows. Этот запрос не возвращает session_id спящий в данный момент объект.

SELECT STasks.session_id, SThreads.os_thread_id
FROM sys.dm_os_tasks AS STasks
INNER JOIN sys.dm_os_threads AS SThreads
    ON STasks.worker_address = SThreads.worker_address
WHERE STasks.session_id IS NOT NULL
ORDER BY STasks.session_id;
GO

Связанные материалы

• sys.dm_tran_database_transactions (Transact-SQL)
• Руководство по блокировке и управлению версиями строк транзакций
• Системные динамические административные представления
• Динамические административные представления и функции, связанные с транзакциями (Transact-SQL)
• SQL Server, объект Locks