Как найти запрос, который все еще удерживает блокировку?

Запрос sys.dm_tran_locks DMV показывает нам, какие сеансы (SPID) содержат блокировки на таких ресурсах, как таблица, страница и строка.

Для каждой полученной блокировки есть ли способ определить, какой оператор SQL (удалить, вставить, обновить или выбрать) вызвал эту блокировку?

Я знаю, что столбец most_recent_query_handle sys.dm_exec_connections DMV дает нам текст последнего выполненного запроса, но несколько раз другие запросы выполнялись до того же сеанса (SPID) и по-прежнему удерживают блокировки.

Я уже использую процедуру sp_whoisactive (от Adam Machanic), и она отображает только запрос, который находится во входном буфере на данный момент (думаю, DBCC INPUTBUFFER @spid), который не всегда (и в моем случае обычно никогда) - это запрос, который приобрел блокировку.

Например:

  1. открытая транзакция /сеанс
  2. выполнить инструкцию (которая содержит блокировку ресурса)
  3. выполнить другой оператор в том же сеансе
  4. откройте другую транзакцию /сеанс и попробуйте изменить ресурс, заблокированный на шаге 2.

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

Этот вопрос исходил из анализа с использованием функции Blocked Process Reports , чтобы найти основную причину блокировки сценариев в процессе производства. Каждая транзакция запускает несколько запросов, и в большинстве случаев последняя (которая отображается на входном буфере в BPR) редко остается той, у которой есть блокировка.

У меня есть следующий вопрос: Рамки для эффективной идентификации блокирующих запросов

10 голосов | спросил tanitelle 9 MarpmFri, 09 Mar 2018 21:13:55 +03002018-03-09T21:13:55+03:0009 2018, 21:13:55

2 ответа


10

SQL Server не хранит историю команд, которые были выполнены 1,2 . Вы можете определить, какие объекты имеют блокировки, но вы не можете обязательно видеть, что вызвало эти блокировки.

Например, если вы выполните это утверждение:

BEGIN TRANSACTION
INSERT INTO dbo.TestLock DEFAULT VALUES

И посмотрите на текст SQL через самый последний дескриптор sql, вы увидите, что этот оператор действительно появляется. Однако, если сеанс сделал это:

BEGIN TRANSACTION
INSERT INTO dbo.TestLock DEFAULT VALUES
GO
SELECT *
FROM dbo.TestLock;
GO

Вы увидите только оператор SELECT * FROM dbo.TestLock;, даже если транзакция не была выполнена, а INSERT блокирует считыватели от таблицы dbo.TestLock.

Я использую это для поиска незафиксированных транзакций, которые блокируют другие сеансы:

/*
    This query shows sessions that are blocking other sessions, including sessions that are 
    not currently processing requests (for instance, they have an open, uncommitted transaction).

    By:  Max Vernon, 2017-03-20
*/
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED; --reduce possible blocking by this query.

USE tempdb;

IF OBJECT_ID('tempdb..#dm_tran_session_transactions') IS NOT NULL
DROP TABLE #dm_tran_session_transactions;
SELECT *
INTO #dm_tran_session_transactions
FROM sys.dm_tran_session_transactions;

IF OBJECT_ID('tempdb..#dm_exec_connections') IS NOT NULL
DROP TABLE #dm_exec_connections;
SELECT *
INTO #dm_exec_connections
FROM sys.dm_exec_connections;

IF OBJECT_ID('tempdb..#dm_os_waiting_tasks') IS NOT NULL
DROP TABLE #dm_os_waiting_tasks;
SELECT *
INTO #dm_os_waiting_tasks
FROM sys.dm_os_waiting_tasks;

IF OBJECT_ID('tempdb..#dm_exec_sessions') IS NOT NULL
DROP TABLE #dm_exec_sessions;
SELECT *
INTO #dm_exec_sessions
FROM sys.dm_exec_sessions;

IF OBJECT_ID('tempdb..#dm_exec_requests') IS NOT NULL
DROP TABLE #dm_exec_requests;
SELECT *
INTO #dm_exec_requests
FROM sys.dm_exec_requests;

;WITH IsolationLevels AS 
(
    SELECT v.*
    FROM (VALUES 
              (0, 'Unspecified')
            , (1, 'Read Uncomitted')
            , (2, 'Read Committed')
            , (3, 'Repeatable')
            , (4, 'Serializable')
            , (5, 'Snapshot')
        ) v(Level, Description)
)
, trans AS 
(
    SELECT dtst.session_id
        , blocking_sesion_id = 0
        , Type = 'Transaction'
        , QueryText = dest.text
    FROM #dm_tran_session_transactions dtst 
        LEFT JOIN #dm_exec_connections dec ON dtst.session_id = dec.session_id
    OUTER APPLY sys.dm_exec_sql_text(dec.most_recent_sql_handle) dest
)
, tasks AS 
(
    SELECT dowt.session_id
        , dowt.blocking_session_id
        , Type = 'Waiting Task'
        , QueryText = dest.text
    FROM #dm_os_waiting_tasks dowt
        LEFT JOIN #dm_exec_connections dec ON dowt.session_id = dec.session_id
    OUTER APPLY sys.dm_exec_sql_text(dec.most_recent_sql_handle) dest
    WHERE dowt.blocking_session_id IS NOT NULL
)
, requests AS 
(
SELECT des.session_id
    , der.blocking_session_id
    , Type = 'Session Request'
    , QueryText = dest.text
FROM #dm_exec_sessions des
    INNER JOIN #dm_exec_requests der ON des.session_id = der.session_id
OUTER APPLY sys.dm_exec_sql_text(der.sql_handle) dest
WHERE der.blocking_session_id IS NOT NULL
    AND der.blocking_session_id > 0 
)
, Agg AS (
    SELECT SessionID = tr.session_id
        , ItemType = tr.Type
        , CountOfBlockedSessions = (SELECT COUNT(*) FROM requests r WHERE r.blocking_session_id = tr.session_id)
        , BlockedBySessionID = tr.blocking_sesion_id
        , QueryText = tr.QueryText
    FROM trans tr
    WHERE EXISTS (
        SELECT 1
        FROM requests r
        WHERE r.blocking_session_id = tr.session_id
        )
    UNION ALL
    SELECT ta.session_id
        , ta.Type
        , CountOfBlockedSessions = (SELECT COUNT(*) FROM requests r WHERE r.blocking_session_id = ta.session_id)
        , BlockedBySessionID = ta.blocking_session_id
        , ta.QueryText
    FROM tasks ta
    UNION ALL
    SELECT rq.session_id
        , rq.Type
        , CountOfBlockedSessions =  (SELECT COUNT(*) FROM requests r WHERE r.blocking_session_id = rq.session_id)
        , BlockedBySessionID = rq.blocking_session_id
        , rq.QueryText
    FROM requests rq
)
SELECT agg.SessionID
    , ItemType = STUFF((SELECT ', ' + COALESCE(a.ItemType, '') FROM agg a WHERE a.SessionID = agg.SessionID ORDER BY a.ItemType FOR XML PATH ('')), 1, 2, '')
    , agg.BlockedBySessionID
    , agg.QueryText
    , agg.CountOfBlockedSessions
    , des.host_name
    , des.login_name
    , des.is_user_process
    , des.program_name
    , des.status
    , TransactionIsolationLevel = il.Description
FROM agg 
    LEFT JOIN #dm_exec_sessions des ON agg.SessionID = des.session_id
    LEFT JOIN IsolationLevels il ON des.transaction_isolation_level = il.Level
GROUP BY agg.SessionID
    , agg.BlockedBySessionID
    , agg.CountOfBlockedSessions
    , agg.QueryText
    , des.host_name
    , des.login_name
    , des.is_user_process
    , des.program_name
    , des.status
    , il.Description
ORDER BY 
    agg.BlockedBySessionID
    , agg.CountOfBlockedSessions
    , agg.SessionID;

Если мы установим простой тестовый стенд в SSMS с несколькими окнами запросов, мы увидим, что мы можем видеть только последний активный оператор.

В первом окне запроса запустите это:

CREATE TABLE dbo.TestLock
(
    id int NOT NULL IDENTITY(1,1)
);
BEGIN TRANSACTION
INSERT INTO dbo.TestLock DEFAULT VALUES

Во втором окне запустите это:

SELECT *
FROM  dbo.TestLock

Теперь, если мы запустим запрос незаблокированных транзакций блокировки сверху, мы видим следующий вывод:

╔═══════════╦═══════════════════════════════╦══ ══════════════════╦═══════════════════════════════ ══════════╗
║ SessionID ║ ItemType ║ BlockedBySessionID ║ QueryText ║
╠═══════════╬═══════════════════════════════╬═════ ═══════════════╬══════════════════════════════════ ═══════╣
║ 67 ║ Транзакция ║ 0 ║ НАЧАЛО ОПЕРАЦИИ ║
║ ║ ║ ║ INSERT INTO dbo.TestLock DEFAULT VALUES ║
║ 68 ║ Запрос сеанса, задача ожидания ║ 67 ║ SELECT * ║
║ ║ ║ ║ FROM dbo.TestLock ║
╚═══════════╩═══════════════════════════════╩═════ ═══════════════╩══════════════════════════════════ ═══════╝

(Я удалил некоторые неуместные столбцы с конца результатов).

Теперь, если мы изменим первое окно запроса на это:

BEGIN TRANSACTION
INSERT INTO dbo.TestLock DEFAULT VALUES
GO
SELECT *
FROM dbo.TestLock;
GO

и запустите второе окно запроса:

SELECT *
FROM  dbo.TestLock

Мы увидим этот результат из запроса блокирующих транзакций:

╔═══════════╦═══════════════════════════════╦══ ══════════════════╦════════════════════╗
║ SessionID ║ ItemType ║ BlockedBySessionID ║ QueryText ║
╠═══════════╬═══════════════════════════════╬═════ ═══════════════╬════════════════════╣
║ 67 ║ Транзакция ║ 0 ║ SELECT * ║
║ ║ ║ ║ FROMdbo.TestLock; ║
║ 68 ║ Запрос сеанса, задача ожидания ║ 67 ║ SELECT * ║
║ ║ ║ ║ FROM dbo.TestLock ║
╚═══════════╩═══════════════════════════════╩═════ ═══════════════╩════════════════════╝

1 - не полностью true. Существует кеш процедур, в котором может содержать оператор, ответственный за блокировку. Однако может быть нелегко определить, какой оператор является фактической причиной блокировки, поскольку в кеше может быть много запросов, которые касаются соответствующего ресурса.

В приведенном ниже ниже примере показан план запросов для тестовых запросов, поскольку кеш процедур не очень занят.

SELECT TOP(30) t.text
    , p.query_plan
    , deqs.execution_count
    , deqs.total_elapsed_time
    , deqs.total_logical_reads
    , deqs.total_logical_writes
    , deqs.total_logical_writes
    , deqs.total_rows
    , deqs.total_worker_time
    , deqs.*
FROM sys.dm_exec_query_stats deqs
OUTER APPLY sys.dm_exec_sql_text(deqs.sql_handle) t 
OUTER APPLY sys.dm_exec_query_plan(deqs.plan_handle) p
WHERE t.text LIKE '%dbo.TestLock%'  --change this to suit your needs
    AND t.text NOT LIKE '/\/\/\/\/EXCLUDE ME/\/\/\/\/\'
ORDER BY 
    deqs.total_worker_time DESC;

Результаты этого запроса могут позволить вам найти виновника, но имейте в виду, что проверка кэша процедур, как это, может быть довольно сложной в загруженной системе.

2 SQL Server 2016 и выше предлагают Query Store , в котором сохраняется полная история выполненных запросов.

ответил Max Vernon 9 MarpmFri, 09 Mar 2018 21:41:17 +03002018-03-09T21:41:17+03:0009 2018, 21:41:17
4

Чтобы дополнить ответ Макса , я нашел ниже полезные утилиты:

Я использую beta_lockinfo, когда хочу глубоко погрузиться в блокирование и проанализировать, что и как возникло блокирование - что чрезвычайно полезно.

  

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

ответил Kin 10 MarpmSat, 10 Mar 2018 21:58:16 +03002018-03-10T21:58:16+03:0009 2018, 21:58:16

Похожие вопросы

Популярные теги

security × 330linux × 316macos × 2827 × 268performance × 244command-line × 241sql-server × 235joomla-3.x × 222java × 189c++ × 186windows × 180cisco × 168bash × 158c# × 142gmail × 139arduino-uno × 139javascript × 134ssh × 133seo × 132mysql × 132