Locks on SQL Server

tags: #SQLServer
categories: SQLServer

TODO

You may find this query useful:

SELECT * 
  FROM sys.dm_exec_requests
  WHERE DB_NAME(database_id) = 'YourDBName' 
    AND blocking_session_id <> 0

To get the query itself use this one:

SELECT text,* 
  FROM sys.dm_exec_requests
  CROSS APPLY sys.dm_exec_sql_text(sql_handle)
  WHERE DB_NAME(database_id) = 'YourDBName' 
    AND blocking_session_id <> 0

;WITH cteHead ( session_id,request_id,wait_type,wait_resource,last_wait_type,is_user_process,request_cpu_time
,request_logical_reads,request_reads,request_writes,wait_time,blocking_session_id,memory_usage
,session_cpu_time,session_reads,session_writes,session_logical_reads
,percent_complete,est_completion_time,request_start_time,request_status,command
,plan_handle,sql_handle,statement_start_offset,statement_end_offset,most_recent_sql_handle
,session_status,group_id,query_hash,query_plan_hash) 
AS ( SELECT sess.session_id, req.request_id, LEFT (ISNULL (req.wait_type, ''), 50) AS 'wait_type'
    , LEFT (ISNULL (req.wait_resource, ''), 40) AS 'wait_resource', LEFT (req.last_wait_type, 50) AS 'last_wait_type'
    , sess.is_user_process, req.cpu_time AS 'request_cpu_time', req.logical_reads AS 'request_logical_reads'
    , req.reads AS 'request_reads', req.writes AS 'request_writes', req.wait_time, req.blocking_session_id,sess.memory_usage
    , sess.cpu_time AS 'session_cpu_time', sess.reads AS 'session_reads', sess.writes AS 'session_writes', sess.logical_reads AS 'session_logical_reads'
    , CONVERT (decimal(5,2), req.percent_complete) AS 'percent_complete', req.estimated_completion_time AS 'est_completion_time'
    , req.start_time AS 'request_start_time', LEFT (req.status, 15) AS 'request_status', req.command
    , req.plan_handle, req.[sql_handle], req.statement_start_offset, req.statement_end_offset, conn.most_recent_sql_handle
    , LEFT (sess.status, 15) AS 'session_status', sess.group_id, req.query_hash, req.query_plan_hash
    FROM sys.dm_exec_sessions AS sess
    LEFT OUTER JOIN sys.dm_exec_requests AS req ON sess.session_id = req.session_id
    LEFT OUTER JOIN sys.dm_exec_connections AS conn on conn.session_id = sess.session_id )
, cteBlockingHierarchy (head_blocker_session_id, session_id, blocking_session_id, wait_type, wait_duration_ms,
wait_resource, statement_start_offset, statement_end_offset, plan_handle, sql_handle, most_recent_sql_handle, [Level])
AS ( SELECT head.session_id AS head_blocker_session_id, head.session_id AS session_id, head.blocking_session_id
    , head.wait_type, head.wait_time, head.wait_resource, head.statement_start_offset, head.statement_end_offset
    , head.plan_handle, head.sql_handle, head.most_recent_sql_handle, 0 AS [Level]
    FROM cteHead AS head
    WHERE (head.blocking_session_id IS NULL OR head.blocking_session_id = 0)
    AND head.session_id IN (SELECT DISTINCT blocking_session_id FROM cteHead WHERE blocking_session_id != 0)

    UNION ALL

    SELECT h.head_blocker_session_id, blocked.session_id, blocked.blocking_session_id, blocked.wait_type,
    blocked.wait_time, blocked.wait_resource, h.statement_start_offset, h.statement_end_offset,
    h.plan_handle, h.sql_handle, h.most_recent_sql_handle, [Level] + 1
    FROM cteHead AS blocked
    INNER JOIN cteBlockingHierarchy AS h ON h.session_id = blocked.blocking_session_id and h.session_id!=blocked.session_id --avoid infinite recursion for latch type of blocking
    WHERE h.wait_type COLLATE Latin1_General_BIN NOT IN ('EXCHANGE', 'CXPACKET') or h.wait_type is null
    )
SELECT bh.*, txt.text AS blocker_query_or_most_recent_query 
FROM cteBlockingHierarchy AS bh 
OUTER APPLY sys.dm_exec_sql_text (ISNULL ([sql_handle], most_recent_sql_handle)) AS txt;


SELECT [s_tst].[session_id],
[database_name] = DB_NAME (s_tdt.database_id),
[s_tdt].[database_transaction_begin_time], 
[sql_text] = [s_est].[text] 
FROM sys.dm_tran_database_transactions [s_tdt]
INNER JOIN sys.dm_tran_session_transactions [s_tst] ON [s_tst].[transaction_id] = [s_tdt].[transaction_id]
INNER JOIN sys.dm_exec_connections [s_ec] ON [s_ec].[session_id] = [s_tst].[session_id]
CROSS APPLY sys.dm_exec_sql_text ([s_ec].[most_recent_sql_handle]) AS [s_est];


SELECT table_name = schema_name(o.schema_id) + '.' + o.name
, wt.wait_duration_ms, wt.wait_type, wt.blocking_session_id, wt.resource_description
, tm.resource_type, tm.request_status, tm.request_mode, tm.request_session_id
FROM sys.dm_tran_locks AS tm
INNER JOIN sys.dm_os_waiting_tasks as wt ON tm.lock_owner_address = wt.resource_address
LEFT OUTER JOIN sys.partitions AS p on p.hobt_id = tm.resource_associated_entity_id
LEFT OUTER JOIN sys.objects o on o.object_id = p.object_id or tm.resource_associated_entity_id = o.object_id
WHERE resource_database_id = DB_ID()
AND object_name(p.object_id) = 'INVENTDIM';

https://learn.microsoft.com/ru-ru/sql/relational-databases/system-stored-procedures/sp-lock-transact-sql?view=sql-server-ver16

sp_lock 276

Declare @T Table (spid int, dbid int, ObjId int, IndId int,	Type nvarchar(10), Resource nvarchar(20), Mode nvarchar(10), Status nvarchar(100))
Insert @T Exec sp_lock 264
Select *
, object_name(T.ObjId) as ObjectName
,(SELECT name FROM sys.indexes WHERE object_id = T.ObjId and index_id = T.IndId) as IndexName
from @T T
order by T.ObjId

Результирующий набор sp_lock содержит по одной строке для каждой блокировки, удерживаемой сеансами, указанными в параметрах @spid1 и @spid2 . Если ни @spid1 , ни @spid2 не указаны, результирующий набор сообщает о блокировках для всех сеансов, активных в данный момент в экземпляре компонента Компонент Database Engine.

spid smallint Идентификатор сеанса ядра СУБД для процесса, запрашивающего блокировку.

dbid smallint Числовой идентификатор базы данных, в которой удерживается блокировка. Для идентификации базы данных можно использовать функцию DB_NAME().

ObjId int Числовой идентификатор объекта, на который удерживается блокировка. Для идентификации объекта можно использовать функцию OBJECT_NAME() в связанной базе данных. Значение 99 является особым, и означает блокировку на одной из системных страниц, используемых для записи распределенных страниц в базе данных.

IndId smallint Числовой идентификатор индекса, для которого удерживается блокировка.

Type nchar(4) Типы блокировки:
RID = Блокировка на одну строку в таблице, задаваемой идентификатором строки (RID — row ID);
KEY = Блокировка внутри индекса, которая защищает диапазон ключей в сериализуемых транзакциях; PAG = Блокировка данных или индексной страницы;
EXT = Блокировка на экстент.
TAB = Блокировка на целую таблицу, включая все данные и индексы;
DB = Блокировка на базу данных;
FIL = Блокировка на файл базы данных;
APP = Блокировка на ресурс приложения;
MD = Блокировка на метаданные или данные о каталоге; HBT = блокировка кучи или B-дерева (HoBT). Эти сведения в SQL Server неполны.
AU = Блокировка на единицу распределения (allocation unit). Эти сведения в SQL Server неполны.

Resource nchar(32) Значение, определяющее блокируемый ресурс. Формат значения зависит от типа ресурса, определенного в столбце Тип :
RID: идентификатор в формате fileid:pagenumber:rid, где fileid определяет файл, содержащий страницу, pagenumber — страницу, содержащую строку, а rid — конкретную строку на странице. fileid соответствует столбцу file_id в представлении каталога sys.database_files .
KEY. Шестнадцатеричное число, используемое внутренним компонентом Компонент Database Engine.
PAG: число в формате fileid:pagenumber, где fileid определяет файл, содержащий страницу, а pagenumber — страницу.
EXT: число, определяющее первую страницу в экстенте. Число в формате fileid:pagenumber.
TAB: нет сведений, так как таблица уже определена в столбце ObjId .
База данных. Информация не предоставлена, так как база данных уже определена в столбце dbid .
FIL: идентификатор файла, который соответствует столбцу file_id в представлении каталога sys.database_files .
APP: идентификатор, уникальный для заблокированного ресурса приложения. В формате DbPrincipalId:<first от двух до 16 символов хэшированного значения> строки><ресурса.
MD: зависит от типа ресурса. Дополнительные сведения см. в описании столбца resource_description в sys.dm_tran_locks (Transact-SQL).
HBT: информация не предоставлена. Вместо этого используйте динамическое административное представление sys.dm_tran_locks.
AU: информация не предоставлена. Вместо этого используйте динамическое административное представление sys.dm_tran_locks.

Режим nvarchar(8) Запрашиваемый режим блокировки. Возможны следующие варианты:
NULL = Блокировки нет. Играет роль заполнителя.
Sch-S = Блокировка стабильности схемы. Заверяет, что элемент схемы, такой как таблица или индекс, не будет удален до тех пор, пока сеанс связи удерживает блокировку стабильности схемы на данный элемент схемы.
Sch-М = Блокировка изменения схемы. Должен поддерживаться любым сеансом связи, во время которого предполагается изменить схему данного ресурса. Заверяет, что другие сеансы не имеют ссылок на обозначенный объект.
S = Коллективная блокировка. Удерживающему сеансу предоставлен коллективный доступ к ресурсу.
U = Блокировка обновления. Указывает блокировку обновления, полученную на ресурсы, которые со временем могут быть обновлены. Используется для предотвращения общей формы взаимоблокировки, которая возникает, когда множество сеансов блокируют ресурсы для потенциального обновления в последующее время;
X = Монопольная блокировка. Удерживающему сеансу предоставлен исключительный доступ к ресурсу.
IS = Блокировка с намерением коллективного доступа. Указывает намерение поместить блокировки типа S на некоторые подчиненные ресурсы в иерархии блокировок.
IU = Блокировка с намерением обновления. Указывает намерение поместить блокировки типа U на некоторые подчиненные ресурсы в иерархии блокировок.
IX = Блокировка с намерением монопольного доступа. Указывает намерение поместить блокировки типа X на некоторые подчиненные ресурсы в иерархии блокировок.
SIU = Коллективная блокировка с намерением обновления. Указывает коллективный доступ к ресурсу с намерением получения блокировок обновления на подчиненные ресурсы в иерархии блокировок.
SIX = Коллективная блокировка с намерением монопольного доступа. Указывает коллективный доступ к ресурсу с намерением получения монопольных блокировок на подчиненные ресурсы в иерархии блокировок.
UIX = Блокировка обновления с намерением монопольного доступа. Указывает блокировку обновления ресурса с намерением получения монопольных блокировок на подчиненные ресурсы в иерархии блокировок.
BU = Блокировка массового обновления. Используется для массовых операций.
RangeS_S = Блокировка разделяемого диапазона ключей и разделяемых ресурсов. Указывает на последовательный просмотр диапазона.
RangeS_U = Блокировка разделяемого диапазона ключей и обновляемых ресурсов. Указывает на последовательное сканирование обновления.
RangeI_N = Блокировка вставляемого диапазона ключей и NULL-ресурсов. Используется для проверки диапазонов, перед тем как вставить новый ключ в индекс.
RangeI_S = блокировка преобразования диапазона ключей. Создается перекрытием блокировок RangeI_N и S;
RangeI_U = Блокировка преобразования диапазона ключей, созданная перекрытием блокировок RangeI_N и U;
RangeI_X = Блокировка преобразования диапазона ключей, созданная перекрытием блокировок RangeI_N и X;
RangeX_S = блокировка преобразования диапазона ключей, созданная перекрытием блокировок RangeI_N и RangeS_S.
RangeX_U = Блокировка преобразования диапазона ключей, созданная перекрытием блокировок RangeI_N и RangeS_U.
RangeX_X = Блокировка монопольного диапазона ключей и монопольных ресурсов. Блокировка диалога, используемая во время обновления ключа в диапазоне.