SQL Server 차단 문제 이해 및 해결

아티클
06/20/2023

적용 대상: SQL Server(지원되는 모든 버전), Azure SQL Managed Instance

원본 KB 번호: 224453

목표

이 문서에서는 SQL Server 차단에 대해 설명하고 차단 문제를 해결하는 방법을 보여 줍니다.

이 문서에서 연결이라는 용어는 데이터베이스의 로그온된 단일 세션을 나타냅니다. 각 연결은 여러 DMV에서 SPID(세션 ID) 또는 session_id로 표시됩니다. 이러한 각 SPID는 일반적인 의미의 별도 프로세스 컨텍스트가 아니지만 프로세스라고 하는 경우가 많습니다. 대신 각 SPID는 지정된 클라이언트에서 단일 연결 요청을 서비스하는 데 필요한 서버 리소스 및 데이터 구조로 구성됩니다. 단일 클라이언트 애플리케이션에 하나 이상의 연결이 있을 수 있습니다. SQL Server 관점에서 볼 때 단일 클라이언트 컴퓨터에서 단일 클라이언트 애플리케이션의 여러 연결과 여러 클라이언트 애플리케이션 또는 여러 클라이언트 컴퓨터의 여러 연결 간에는 차이가 없습니다. 원자성입니다. 한 연결은 원본 클라이언트에 관계없이 다른 연결을 차단할 수 있습니다.

참고

이 문서는 Azure SQL Managed Instance를 비롯한 SQL Server 인스턴스에 중점을 줍니다. Azure SQL Database 차단 문제 해결에 대한 자세한 내용은 Azure SQL Database 차단 문제 이해 및 해결을 참조하세요.

차단이란

차단은 잠금 기반 동시성을 사용하는 RDBMS(관계형 데이터베이스 관리 시스템)의 피할 수 없는 디자인 특성입니다. 앞에서 설명한 것처럼 SQL Server의 한 세션에서 특정 리소스에 대한 잠금을 보유하고 두 번째 SPID가 동일한 리소스에서 충돌하는 잠금 유형을 획득하려고 할 때 차단이 발생합니다. 일반적으로 첫 번째 SPID가 리소스를 잠그는 시간 프레임은 작습니다. 소유 세션이 잠금을 해제하면 두 번째 연결은 리소스에 대한 자체 잠금을 획득하고 처리를 계속할 수 있습니다. 여기에 설명된 대로 차단은 정상적인 동작이며 시스템 성능에 눈에 띄는 영향을 주지 않고 하루 동안 여러 번 발생할 수 있습니다.

쿼리 기간 및 트랜잭션 컨텍스트는 잠금이 유지되는 기간과 다른 쿼리에 미치는 영향을 결정합니다. 쿼리가 트랜잭션 내에서 실행되지 않고 잠금 힌트가 사용되지 않는 경우 SELECT 문에 대한 잠금은 쿼리 중에가 아니라 실제로 읽는 시간에만 리소스에 보관됩니다. INSERT 문, UPDATE 문, DELETE 문의 경우 데이터 일관성과 필요한 경우 쿼리를 롤백할 수 있도록 쿼리 중에 잠금이 유지됩니다.

트랜잭션 내에서 실행되는 쿼리의 경우 잠금이 유지되는 기간은 쿼리 유형, 트랜잭션 격리 수준, 쿼리에서 잠금 힌트가 사용되는지에 따라 결정됩니다. 잠금, 잠금 힌트, 트랜잭션 격리 수준에 대한 설명은 다음 문서를 참조하세요.

잠금 및 차단이 시스템 성능에 해로운 영향을 주는 지점까지 유지되는 경우 다음 이유 중 하나로 인해 발생합니다.

SPID는 리소스 집합을 해제하기 전에 오랜 시간 동안 잠금을 유지합니다. 이러한 유형의 차단은 시간이 지남에 따라 자체적으로 해결되지만 성능이 저하될 수 있습니다.
SPID는 리소스 집합에 대한 잠금을 유지하고 해제하지 않습니다. 이러한 유형의 차단은 자체적으로 해결되지 않으며 영향을 받는 리소스에 대한 액세스를 무기한 차단합니다.

첫 번째 시나리오에서는 다른 SPID로 인해 시간이 지남에 따라 서로 다른 리소스를 차단하여 이동 대상을 만들기 때문에 상황이 매우 유동적일 수 있습니다. 이러한 상황은 개별 쿼리로 문제를 좁히기 위해 SQL Server Management Studio를 사용하여 문제를 해결하기 어렵습니다. 반면, 두 번째 상황은 진단하기 쉬울 수 있는 일관된 상태를 초래합니다.

애플리케이션 및 차단

차단 문제가 발생할 때 서버 쪽 튜닝 및 플랫폼 문제에 집중하는 경향이 있을 수 있습니다. 그러나 데이터베이스에만 주의를 기울이면 문제가 해결되지 않을 수 있으며, 클라이언트 애플리케이션 및 제출하는 쿼리를 검사하는 데 필요한 시간과 에너지를 소비할 수 있습니다. 애플리케이션이 만들어지고 있는 데이터베이스 호출과 관련하여 노출하는 가시성 수준에 관계없이 차단 문제는 애플리케이션에서 제출한 정확한 SQL 문을 검사하고 쿼리 취소, 연결 관리, 모든 결과 행 가져오기 등과 관련된 애플리케이션의 정확한 동작을 모두 검사해야 하는 경우가 많습니다. 개발 도구에서 연결 관리, 쿼리 취소, 쿼리 시간 제한, 결과 가져오기 등에 대한 명시적 제어를 허용하지 않는 경우 차단 문제를 해결할 수 없을 수도 있습니다. 특히 성능이 중요한 OLTP 환경의 경우 SQL Server 애플리케이션 개발 도구를 선택하기 전에 이 가능성을 면밀히 검토해야 합니다.

데이터베이스 및 애플리케이션의 디자인 및 생성 단계에서 데이터베이스 성능에 주의하세요. 특히 각 쿼리에 대해 리소스 사용량, 격리 수준, 트랜잭션 경로 길이를 평가해야 합니다. 각 쿼리 및 트랜잭션은 가능한 한 간단해야 합니다. 적절한 연결 관리 분야를 실행해야 합니다. 그렇지 않으면 애플리케이션이 적은 수의 사용자에서 적절한 성능을 가질 수 있는 것처럼 보일 수 있지만 사용자 수가 증가하면 성능이 크게 저하될 수 있습니다.

적절한 애플리케이션 및 쿼리 디자인을 통해 SQL Server 차단이 거의 없는 단일 서버에서 수천 명의 동시 사용자를 지원할 수 있습니다.

차단 문제 해결

어떤 차단 상황에 있든 관계 없이 잠금 문제를 해결하는 방법은 동일합니다. 이러한 논리적 구분은 이 문서의 나머지 구성을 나타냅니다. 개념은 헤드 블로커를 찾아 해당 쿼리가 수행하는 작업과 차단하는 이유를 식별하는 것입니다. 문제가 있는 쿼리(즉, 장기간 잠금을 유지하는 경우)가 식별되면 다음 단계는 차단이 발생하는 원인을 분석하고 확인하는 것입니다. 원인을 파악한 후 쿼리와 트랜잭션을 재설계하여 변경할 수 있습니다.

문제 해결 단계:

주 차단 세션(헤드 블로커) 식별
차단을 일으키는 쿼리 및 트랜잭션 찾기(장기간 잠금을 유지하는 경우)
장기간 차단이 발생하는 원인 분석/이해
쿼리 및 트랜잭션을 다시 설계하여 차단 문제 해결

이제 적절한 데이터 캡처를 통해 주 차단 세션을 정확히 찾아내는 방법에 대해 알아보겠습니다.

차단 정보 수집

데이터베이스 관리자는 SQL Server에서 잠금 및 차단 상태를 지속적으로 모니터링하는 SQL 스크립트를 사용하여 차단 문제 해결의 어려움에 대처합니다. 이 데이터를 수집하려면 기본적으로 두 가지 방법이 있습니다.

첫 번째는 DMO(동적 관리 개체)를 쿼리하고 시간 경과에 따른 비교를 위해 결과를 저장하는 것입니다. 이 문서에서 참조하는 개체 중 일부는 DMV(동적 관리 뷰)이며 일부는 DMF(동적 관리 함수)입니다.

두 번째 방법은는 확장 이벤트(XEvents) 또는 SQL Profiler 추적을 사용하여 실행 중인 내용을 캡처하는 것입니다. SQL Trace 및 SQL Server Profiler가 더 이상 사용되지 않으므로 이 문제 해결 가이드는 XEvents에 중점을 줍니다.

DMV에서 정보 수집

차단 문제를 해결하기 위해 DMV를 참조하는 것은 차단 체인 및 SQL 문의 헤드에서 SPID(세션 ID)를 식별하는 것을 목표로 합니다. 차단 중인 피해 SPID를 찾습니다. SPID가 다른 SPID에 의해 차단되는 경우 리소스를 소유한 SPID(차단 SPID)를 조사합니다. 소유자 SPID도 차단되고 있나요? 체인을 탐색하여 헤드 블로커를 찾은 다음 체인이 잠금을 유지하는 원인을 조사할 수 있습니다.

이렇게 하려면 다음 방법 중 하나를 사용하면 됩니다.

SQL Server Management Studio(SSMS) 개체 탐색기에서 최상위 서버 개체를 마우스 오른쪽 단추로 클릭하고, 보고서를 확장하고, 표준 보고서를 확장한 다음, 작업 – 모든 차단 트랜잭션을 선택합니다. 이 보고서는 차단 체인의 헤드에서 현재 트랜잭션을 보여 줍니다. 트랜잭션을 확장하면 보고서에 헤드 트랜잭션에서 차단한 트랜잭션이 표시됩니다. 이 보고서에서는 차단하는 SQL 문과 차단된 SQL 문도 보여줍니다.
SSMS 활동 모니터를 열고 차단 기준 열을 참조합니다. 활동 모니터에 대한 자세한 내용은 여기를 참조하세요.

더 자세한 쿼리 기반 메서드는 DMV를 통해 사용할 수도 있습니다.

sp_who 및 sp_who2 명령은 모든 현재 세션을 표시하는 이전 명령입니다. DMV sys.dm_exec_sessions은 더 쉽게 쿼리 및 필터링을 할 수 있는 결과 집합에 더 많은 데이터를 반환합니다. 다른 쿼리의 핵심에서 sys.dm_exec_sessions를 찾을 수 있습니다.
특정 세션이 이미 식별된 경우 세션에서 제출한 마지막 문을 찾는 데 DBCC INPUTBUFFER(<session_id>)를 사용할 수 있습니다. 쿼리 및 필터링이 더 쉬운 결과 집합에서 sys.dm_exec_input_buffer DMF(동적 관리 함수)를 통해 유사한 결과를 반환하여 session_id 및 request_id를 제공할 수 있습니다. 예를 들어 session_id 66 및 request_id 0에서 제출한 가장 최근 쿼리를 반환하려면 다음을 수행합니다.

SELECT * FROM sys.dm_exec_input_buffer (66,0);

sys.dm_exec_requests 및 blocking_session_id 열을 참조합니다. blocking_session_id = 0이면 세션이 차단되지 않습니다. sys.dm_exec_requests이 현재 실행 중인 요청만 나열하는 동안 모든 연결(활성 여부)이 sys.dm_exec_sessions에 나열됩니다. 다음 쿼리의 sys.dm_exec_requests 및 sys.dm_exec_sessions 간에 이 일반적인 조인을 기반으로 빌드합니다. 쿼리가 sys.dm_exec_requests에 의해 반환 되려면 SQL Server를 통해 적극적으로 실행되어야 합니다.
sys.dm_exec_sql_text or sys.dm_exec_input_buffer DMV를 사용하여 현재 실행 중인 쿼리와 현재 SQL 일괄 처리 텍스트 또는 입력 버퍼 텍스트를 찾으려면 이 샘플 쿼리를 실행합니다. sys.dm_exec_sql_text의 text 열에서 반환된 데이터가 NULL이면 쿼리는 현재 실행되고 있지 않습니다. 이 경우 sys.dm_exec_input_buffer의 event_info 열에는 SQL 엔진에 전달된 마지막 명령 문자열이 포함됩니다. 이 쿼리를 사용하여 session_id당 차단된 session_id 목록을 포함하여 다른 세션을 차단하는 세션을 식별할 수도 있습니다.

WITH cteBL (session_id, blocking_these) AS 
(SELECT s.session_id, blocking_these = x.blocking_these FROM sys.dm_exec_sessions s 
CROSS APPLY    (SELECT isnull(convert(varchar(6), er.session_id),'') + ', '  
                FROM sys.dm_exec_requests as er
                WHERE er.blocking_session_id = isnull(s.session_id ,0)
                AND er.blocking_session_id <> 0
                FOR XML PATH('') ) AS x (blocking_these)
)
SELECT s.session_id, blocked_by = r.blocking_session_id, bl.blocking_these
, batch_text = t.text, input_buffer = ib.event_info, * 
FROM sys.dm_exec_sessions s 
LEFT OUTER JOIN sys.dm_exec_requests r on r.session_id = s.session_id
INNER JOIN cteBL as bl on s.session_id = bl.session_id
OUTER APPLY sys.dm_exec_sql_text (r.sql_handle) t
OUTER APPLY sys.dm_exec_input_buffer(s.session_id, NULL) AS ib
WHERE blocking_these is not null or r.blocking_session_id > 0
ORDER BY len(bl.blocking_these) desc, r.blocking_session_id desc, r.session_id;

Microsoft 지원이 제공하는 보다 정교한 샘플 쿼리를 실행하여 차단 체인과 관련된 세션의 쿼리 텍스트를 포함하여 여러 세션 차단 체인의 헤드를 식별합니다.

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;

장기 실행 또는 커밋되지 않은 트랜잭션을 포착하려면 sys.dm_tran_database_transactions, sys.dm_tran_session_transactions, sys.dm_exec_connections, 및 sys.dm_exec_sql_text를 포함하여 현재 열려 있는 트랜잭션을 보기 위해 다른 DMV 집합을 사용합니다. 트랜잭션 추적과 관련된 여러 DMV가 있습니다. 여기에서 더 많은 트랜잭션에 대한 DMV를 참조하세요.

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];

SQL Server의 스레드/작업 계층에 있는 sys.dm_os_waiting_tasks 참조. 이렇게 하면 요청이 현재 발생하는 SQL wait_type 대한 정보가 반환됩니다. sys.dm_exec_requests와 마찬가지로 활성 요청만 sys.dm_os_waiting_tasks에 의해 반환됩니다.

참고

시간에 따른 집계된 대기 통계를 포함하여 대기 유형에 대한 자세한 내용은 DMV sys.dm_db_wait_stats를 참조하세요.

쿼리에 의해 배치된 잠금에 대한 자세한 내용은 sys.dm_tran_locks DMV를 사용합니다. 이 DMV는 프로덕션 SQL Server 인스턴스에서 대량의 데이터를 반환할 수 있으며 현재 보유하고 있는 잠금을 진단하는 데 유용합니다.

sys.dm_os_waiting_tasks의 INNER JOIN으로 인해 다음 쿼리는 sys.dm_tran_locks의 출력을 현재 차단된 요청, 대기 상태 및 잠금으로만 제한합니다.

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) = '<table_name>';

DMV를 사용하면 시간에 따른 쿼리 결과를 저장하면 지정된 시간 간격 동안 차단을 검토하여 지속형 차단 또는 추세를 식별할 수 있는 데이터 요소가 제공됩니다. 이러한 문제를 해결하기 위한 CSS용 이동 도구는 PSSDiag 데이터 수집기를 사용하는 것입니다. 이 도구는 "SQL Server 성능 통계"를 사용하여 시간이 지남에 따라 위에서 참조한 DMV에서 결과 집합을 수집합니다. 이 도구는 지속적으로 발전하고 있으므로 GitHub의 DiagManager 최신 공개 버전을 검토합니다.

확장 이벤트에서 정보 수집

위의 정보 외에도 서버에서 활동 추적을 캡처하여 SQL Server 차단 문제를 철저히 조사해야 하는 경우가 많습니다. 예를 들어 세션이 트랜잭션 내에서 여러 명령문을 실행하는 경우 제출된 마지막 명령문만 표시됩니다. 그러나 이전 명령문 중 하나는 잠금이 여전히 유지되는 이유일 수 있습니다. 추적을 사용하면 현재 트랜잭션 내의 세션에서 실행된 모든 명령을 볼 수 있습니다.

SQL Server 추적을 캡처하는 방법에는 두 가지가 있습니다. 확장 이벤트(XEvents) 및 프로파일러 추적 그러나 SQL 서버 프로파일러를 사용하는 SQL 추적은 더 이상 사용되지 않습니다. XEvents는 관찰된 시스템에 더 많은 다양성과 영향을 줄여 주는 뛰어난 최신 추적 플랫폼이며, 해당 인터페이스는 SSMS로 통합됩니다.

XEvent 프로파일러 메뉴 아래에 개체 탐색기에 나열된 미리 만들어진 확장 이벤트 세션이 SSMS를 시작할 준비가 되어 있습니다. 자세한 내용은 XEvent 프로파일러를 참조하세요. 또한 SSMS 사용자 지정 확장 이벤트 세션을 만들 수도 있습니다. 확장 이벤트 새 세션 마법사를 참조하세요. 차단 문제를 해결하기 위해 일반적으로 다음을 캡처합니다.

범주 오류:
- 주의
- Blocked_process_report**
- Error_reported(채널 관리자)
- Exchange_spill
- Execution_warning

**차단된 프로세스 보고서가 생성되는 임계값 및 빈도를 구성하려면 sp_configure 명령을 사용하여 초 단위로 설정할 수 있는 차단된 프로세스 임계값 옵션을 구성하세요. 기본적으로 차단된 프로세스 보고서는 생성되지 않습니다.

범주 경고:
- Hash_warning
- Missing_column_statistics
- Missing_join_predicate
- Sort_warning
범주 실행:
- Rpc_completed
- Rpc_starting
- Sql_batch_completed
- Sql_batch_starting
범주 잠금
- Lock_deadlock
범주 세션
- Existing_connection
- 로그인
- 로그아웃

일반적인 차단 시나리오 식별 및 해결

위의 정보를 검사하여 대부분의 차단 문제의 원인을 확인할 수 있습니다. 이 문서의 나머지 부분에는 이 정보를 사용하여 몇 가지 일반적인 차단 시나리오를 식별하고 해결하는 방법에 대한 설명이 있습니다. 이 문서에서는 차단 스크립트(앞에서 참조)를 사용하여 차단 SPID에 대한 정보를 캡처하고 XEvent 세션을 사용하여 애플리케이션 작업을 캡처했다고 가정합니다.

차단 데이터 분석

blocking_these 및 session_id을 사용하여 차단 체인의 헤드를 결정하기 위해 DMV sys.dm_exec_requests 및 sys.dm_exec_sessions의 출력을 검사합니다. 이렇게 하면 차단된 요청과 차단 중인 요청이 가장 명확하게 식별됩니다. 차단 및 차단 중인 세션을 자세히 살펴봅니다. 차단 체인에 공통 또는 루트가 있나요? 공통 테이블을 공유할 가능성이 높으며 차단 체인에 관련된 세션 중 하나 이상이 쓰기 작업을 수행하고 있습니다.

차단 체인의 헤드에 있는 SPID에 대한 정보는 DMV sys.dm_exec_requests 및 sys.dm_exec_sessions의 출력을 조사하세요. 다음 열 레이블을 찾으세요.

sys.dm_exec_requests.status
이 열에는 특정 요청의 상태가 표시됩니다. 일반적으로 절전 모드 상태는 SPID가 실행을 완료했으며 애플리케이션이 다른 쿼리 또는 일괄 처리를 제출하기를 기다리고 있음을 나타냅니다. 실행 가능 또는 실행 상태는 SPID가 현재 쿼리를 처리 중임을 나타냅니다. 다음 표에서는 다양한 상태 값에 대해 간략하게 설명합니다.

상태	의미
배경	SPID는 교착 상태 검색, 로그 기록기 또는 검사점과 같은 백그라운드 작업을 실행합니다.
절전 상태	SPID가 현재 실행되고 있지 않습니다. 이는 일반적으로 SPID가 애플리케이션의 명령을 기다리고 있음을 나타냅니다.
실행 중	SPID는 현재 스케줄러에서 실행 중입니다.
실행 가능	SPID는 스케줄러의 실행 가능한 큐에 있으며 스케줄러 시간을 얻기 위해 대기합니다.
일시 중단됨	SPID는 잠금 또는 래치와 같은 리소스를 기다리고 있습니다.

sys.dm_exec_sessions.open_transaction_count
이 열은 이 세션의 열린 트랜잭션 수를 알려줍니다. 이 값이 0보다 큰 경우 SPID는 열려 있는 트랜잭션 내에 있으며 트랜잭션 내의 문에서 획득한 잠금을 보유할 수 있습니다.
sys.dm_exec_requests.open_transaction_count
마찬가지로 이 열은 이 요청의 열린 트랜잭션 수를 알려줍니다. 이 값이 0보다 큰 경우 SPID는 열려 있는 트랜잭션 내에 있으며 트랜잭션 내의 문에서 획득한 잠금을 보유할 수 있습니다.
sys.dm_exec_requests.wait_type, wait_time 및 last_wait_type
sys.dm_exec_requests.wait_typeNULL이면 요청은 현재 아무 것도 기다리지 않고 last_wait_type값은 요청이 마지막으로wait_type 발생한 것을 나타냅니다. sys.dm_os_wait_stats에 대한 자세한 내용과 가장 일반적인 대기 유형에 대한 설명은 sys.dm_os_wait_stats를 참조하세요. 이 wait_time 값을 사용하여 요청이 진행 중인지 확인할 수 있습니다. sys.dm_exec_requests 테이블에 대한 쿼리가 sys.dm_exec_requests의 이전 쿼리에서 wait_time 값보다 작은 wait_time 열의 값을 반환하면 이는 이전 잠금이 획득 및 해제되었고 이제 새 잠금을 기다리고 있음을 나타냅니다(0이 아닌 wait_time로 가정). 이는 요청이 대기 중인 리소스를 표시하는 출력 wait_resource 간의 sys.dm_exec_requests 비교를 통해 확인할 수 있습니다.

sys.dm_exec_requests.wait_resource 이 열은 차단된 요청이 대기 중인 리소스를 나타냅니다. 다음 표에서는 일반적인 wait_resource 형식과 그 의미를 나열합니다.

리소스	형식	예제	설명
표	DatabaseID:ObjectID:IndexID	TAB: 5:261575970:1	이 경우 데이터베이스 ID 5는 pubs 샘플 데이터베이스이고 `object_id` 261575970은 타이틀 테이블이고 1은 클러스터형 인덱스입니다.
페이지	DatabaseID:FileID:PageID	PAGE: 5:1:104	이 경우 데이터베이스 ID 5는 pubs이고, 파일 ID 1은 기본 데이터 파일이며, 104페이지는 제목 테이블에 속하는 페이지입니다. 페이지가 속한 object_id를 식별하려면 sys.dm_db_page_info 동적 관리 함수를 사용하고 `wait_resource`에서 DatabaseID, FileId, PageId를 전달합니다.
키	DatabaseID:Hobt_id(인덱스 키의 해시 값)	키: 5:72057594044284928 (3300a4f361aa)	이 경우 데이터베이스 ID 5는 Pubs이고, Hobt_ID 72057594044284928 object_id 261575970 index_id 2(타이틀 테이블)에 해당합니다. 카탈로그 뷰를 `sys.partitions` 사용하여 특정 `index_idobject_id`및 에 `hobt_id` 연결합니다. 인덱스 키 해시를 특정 키 값으로 해제하는 방법은 없습니다.
행	DatabaseID:FileID:PageID:Slot(row)	RID: 5:1:104:3	이 경우 데이터베이스 ID 5는 pubs이고, 파일 ID 1은 기본 데이터 파일이고, 페이지 104는 제목 테이블에 속하는 페이지이고, 슬롯 3은 페이지에서 행의 위치를 나타냅니다.
컴파일	DatabaseID:FileID:PageID:Slot(row)	RID: 5:1:104:3	이 경우 데이터베이스 ID 5는 pubs이고, 파일 ID 1은 기본 데이터 파일이고, 페이지 104는 제목 테이블에 속하는 페이지이고, 슬롯 3은 페이지에서 행의 위치를 나타냅니다.

sys.dm_tran_active_transactionssys.dm_tran_active_transactions DMV에는 커밋 또는 롤백을 기다리는 트랜잭션의 전체 그림을 위해 다른 DMV에 조인할 수 있는 열린 트랜잭션에 대한 데이터가 포함되어 있습니다. 다음 쿼리를 사용하여 sys.dm_tran_session_transactions를 포함한 다른 DMV에 조인된 열린 트랜잭션에 대한 정보를 반환합니다. 트랜잭션의 현재 상태 transaction_begin_time및 기타 상황 데이터를 고려하여 차단의 원본이 될 수 있는지 여부를 평가합니다.

SELECT tst.session_id, [database_name] = db_name(s.database_id)
, tat.transaction_begin_time
, transaction_duration_s = datediff(s, tat.transaction_begin_time, sysdatetime()) 
, transaction_type = CASE tat.transaction_type  WHEN 1 THEN 'Read/write transaction'
                                                WHEN 2 THEN 'Read-only transaction'
                                                WHEN 3 THEN 'System transaction'
                                                WHEN 4 THEN 'Distributed transaction' END
, input_buffer = ib.event_info, tat.transaction_uow     
, transaction_state  = CASE tat.transaction_state    
            WHEN 0 THEN 'The transaction has not been completely initialized yet.'
            WHEN 1 THEN 'The transaction has been initialized but has not started.'
            WHEN 2 THEN 'The transaction is active - has not been committed or rolled back.'
            WHEN 3 THEN 'The transaction has ended. This is used for read-only transactions.'
            WHEN 4 THEN 'The commit process has been initiated on the distributed transaction.'
            WHEN 5 THEN 'The transaction is in a prepared state and waiting resolution.'
            WHEN 6 THEN 'The transaction has been committed.'
            WHEN 7 THEN 'The transaction is being rolled back.'
            WHEN 8 THEN 'The transaction has been rolled back.' END 
, transaction_name = tat.name, request_status = r.status
, tst.is_user_transaction, tst.is_local
, session_open_transaction_count = tst.open_transaction_count  
, s.host_name, s.program_name, s.client_interface_name, s.login_name, s.is_user_process
FROM sys.dm_tran_active_transactions tat 
INNER JOIN sys.dm_tran_session_transactions tst  on tat.transaction_id = tst.transaction_id
INNER JOIN Sys.dm_exec_sessions s on s.session_id = tst.session_id 
LEFT OUTER JOIN sys.dm_exec_requests r on r.session_id = s.session_id
CROSS APPLY sys.dm_exec_input_buffer(s.session_id, null) AS ib;

기타 열

sys.dm_exec_sessions 및 sys.dm_exec_request의 나머지 열은 문제의 근본 원인에 대한 인사이트를 제공할 수도 있습니다. 유용성은 문제의 상황에 따라 달라집니다. 예를 들어 문제가 특정 클라이언트(hostname), 특정 네트워크 라이브러리(client_interface_name)에서만 발생하는지, SPID가 제출한 마지막 배치가 sys.dm_exec_sessions의 last_request_start_time인 경우, sys.dm_exec_requests에서 start_time을 사용하여 요청이 실행된 기간 등을 확인할 수 있습니다.

일반적인 차단 시나리오

아래 표는 일반적인 증상을 가능한 원인에 매핑합니다.

wait_type, open_transaction_count 및 status 열은 sys.dm_exec_request에서 반환된 정보를 참조하고 다른 열은 sys.dm_exec_sessions에서 반환될 수 있습니다. "해결됨?" 열은 차단이 자체적으로 resolve 것인지 또는 명령을 통해 KILL 세션을 종료해야 하는지 여부를 나타냅니다. 자세한 내용은 KILL(Transact-SQL)을 참조하세요.

시나리오	Wait_type	Open_Tran	상태	해결?	기타 증상
1	NULL이 아님	>= 0	실행 가능	예, 쿼리가 완료되면	`sys.dm_exec_sessions`, `reads`, `cpu_time` 및/또는 `memory_usage` 열은 시간이 지남에 따라 증가합니다. 완료되면 쿼리 기간이 높아질 것입니다.
2	NULL	>0	절전 상태	아니요, 하지만 SPID는 종료될 수 있습니다.	이 SPID에 대한 확장 이벤트 세션에서 쿼리 시간 제한 또는 취소가 발생했음을 나타내는 주의 신호가 표시될 수 있습니다.
3	NULL	>= 0	실행 가능	아니요. 클라이언트가 모든 행을 가져오거나 연결을 닫을 때까지 해결되지 않습니다. SPID는 종료될 수 있지만 최대 30초가 걸릴 수 있습니다.	open_transaction_count = 0이고 트랜잭션 격리 수준이 기본(READ COMMITTED)인 동안 SPID가 잠금을 유지하는 경우 이것이 가능한 원인일 수 있습니다.
4	경우에 따라 다름	>= 0	실행 가능	아니요. 클라이언트가 쿼리를 취소하거나 연결을 닫을 때까지 해결되지 않습니다. SPID는 종료될 수 있지만 최대 30초가 걸릴 수 있습니다.	차단 체인의 헤드에 있는 SPID에 대한 `sys.dm_exec_sessions`의 `hostname` 열은 차단하는 SPID 중 하나와 동일합니다.
5	NULL	>0	롤백	예.	이 SPID에 대한 확장 이벤트 세션에서 쿼리 시간 제한 또는 취소가 발생했거나 단순히 롤백 문이 실행되었음을 나타내는 주의 신호가 표시될 수 있습니다.
6	NULL	>0	절전 상태	결국. Windows NT 세션이 더 이상 활성 상태가 아닐 경우 연결이 끊어집니다.	`sys.dm_exec_sessions`의 `last_request_start_time` 값이 현재 시간보다 훨씬 이전입니다.

자세한 차단 시나리오

시나리오 1: 실행 시간이 긴 일반적으로 실행되는 쿼리로 인한 차단

이 시나리오에서는 적극적으로 실행 중인 쿼리가 잠금을 획득했으며 잠금이 해제되지 않습니다(트랜잭션 격리 수준의 영향을 받습니다). 따라서 다른 세션은 잠금이 해제될 때까지 대기합니다.

해결 방법:

이러한 유형의 차단 문제에 대한 해결 방법은 쿼리를 최적화하는 방법을 찾는 것입니다. 이 차단 문제 클래스는 성능 문제일 수 있으므로 이를 그렇게 처리해야 합니다. 느리게 실행되는 특정 쿼리 문제 해결에 대한 자세한 내용은 SQL Server에서 느리게 실행되는 쿼리 문제를 해결하는 방법을 참조하세요. 자세한 내용은 성능 모니터링 및 조정을 참조하세요.

SQL Server 2016에 도입된 쿼리 저장소의 SSMS에 기본 제공되는 보고서도 가장 비용이 많이 드는 쿼리 및 차선의 실행 계획을 식별하는 데 적극 권장되고 유용한 도구입니다.

다른 사용자를 차단하는 장기 실행 쿼리가 있고 최적화할 수 없는 경우 OLTP 환경에서 전용 보고 시스템으로 이동하는 것이 좋습니다. Always On 가용성 그룹을 사용하여 데이터베이스의 읽기 전용 복제본을 동기화할 수도 있습니다.

참고

쿼리 실행 중 차단은 행 또는 페이지 잠금이 테이블 잠금으로 에스컬레이션되는 시나리오인 쿼리 에스컬레이션으로 인해 발생할 수 있습니다. Microsoft SQL Server는 잠금 에스컬레이션을 수행할 시기를 동적으로 결정합니다. 잠금 에스컬레이션을 방지하는 가장 간단하고 안전한 방법은 트랜잭션을 짧게 유지하고 잠금 에스컬레이션 임계값을 초과하지 않도록 비용이 많이 드는 쿼리의 잠금 공간을 줄이는 것입니다. 과도한 잠금 에스컬레이션을 감지하고 방지하는 방법에 대한 자세한 내용은 잠금 에스컬레이션으로 인한 차단 문제 해결을 참조하세요.

시나리오 2: 커밋되지 않은 트랜잭션이 있는 절전 모드 SPID로 인한 차단

이러한 유형의 차단은 트랜잭션 중첩 수준(@@TRANCOUNT, sys.dm_exec_requests에서 open_transaction_count)이 0보다 큰 명령을 대기 중이거나 대기 중인 SPID로 식별할 수 있습니다. 애플리케이션에서 쿼리 시간 제한을 경험하거나 필요한 수의 ROLLBACK 및/또는 COMMIT 문을 실행하지 않고 취소를 실행하는 경우에 발생할 수 있습니다. SPID가 쿼리 시간 제한 또는 취소를 받으면 현재 쿼리 및 일괄 처리를 종료하지만 트랜잭션을 자동으로 롤백하거나 커밋하지는 않습니다. SQL Server는 단일 쿼리가 취소되어 전체 트랜잭션이 롤백되어야 한다고 가정할 수 없기 때문에 애플리케이션이 이를 담당합니다. 쿼리 시간 제한 또는 취소는 확장 이벤트 세션에서 SPID에 대한 주의 신호 이벤트로 표시됩니다.

커밋되지 않은 명시적 트랜잭션을 보여 주려면 다음 쿼리를 실행합니다.

CREATE TABLE #test (col1 INT);
INSERT INTO #test SELECT 1;
GO
BEGIN TRAN
UPDATE #test SET col1 = 2 where col1 = 1;

그런 다음, 동일한 창에서 이 쿼리를 실행합니다.

SELECT @@TRANCOUNT;
ROLLBACK TRAN
DROP TABLE #test;

두 번째 쿼리의 출력은 트랜잭션 개수가 1임을 나타냅니다. 트랜잭션에서 획득한 모든 잠금은 트랜잭션이 커밋되거나 롤백될 때까지 계속 유지됩니다. 애플리케이션이 트랜잭션을 명시적으로 열고 커밋하는 경우 통신 또는 기타 오류로 인해 세션과 해당 트랜잭션이 열린 상태로 남을 수 있습니다.

이 문서의 앞부분에 있는 스크립트를 사용하여 sys.dm_tran_active_transactions 인스턴스 전체에서 현재 커밋되지 않은 트랜잭션을 식별합니다.

해결 방법:

이 차단 문제 클래스는 성능 문제일 수 있으므로 이를 그렇게 처리해야 합니다. 쿼리 실행 시간을 줄일 수 있는 경우 쿼리 시간 제한 또는 취소가 발생하지 않을 수 있습니다. 애플리케이션이 시간 초과를 처리하거나 시나리오가 발생할 경우 취소할 수 있는 것이 중요하지만 쿼리 성능을 검사하는 것도 도움이 될 수 있습니다.
애플리케이션은 트랜잭션 중첩 수준을 제대로 관리해야 하거나 이러한 방식으로 쿼리를 취소한 후 차단 문제가 발생할 수 있습니다. 다음과 같은 사항을 고려해야 합니다.
- 클라이언트 애플리케이션의 오류 처리기에서 클라이언트 애플리케이션이 트랜잭션이 열려 있다고 생각하지 않더라도 오류 다음에 IF @@TRANCOUNT > 0 ROLLBACK TRAN을(를) 실행합니다. 일괄 처리 중에 호출된 저장 프로시저가 클라이언트 애플리케이션이 알지 못하는 사이에 트랜잭션을 시작할 수 있으므로 열린 트랜잭션을 확인해야 합니다. 쿼리 취소와 같은 특정 조건은 프로시저가 현재 명령문을 넘어서 실행되지 않도록 하므로 프로시저에 IF @@ERROR <> 0을 확인하고 트랜잭션을 중단하는 논리가 있더라도 이러한 경우 이 롤백 코드는 실행되지 않습니다.
- 웹 기반 응용 프로그램과 같이 연결을 열고 풀로 다시 연결을 해제하기 전에 몇 가지 쿼리를 실행하는 응용 프로그램에서 연결 풀링을 사용하는 경우, 연결 풀링을 일시적으로 비활성화하면 클라이언트 응용 프로그램이 오류를 적절하게 처리하도록 수정될 때까지 문제를 완화하는 데 도움이 될 수 있습니다. 연결 풀링을 비활성화하면 연결을 해제하면 SQL Server 연결의 물리적 연결이 끊어져 서버가 열려 있는 트랜잭션을 롤백하게 됩니다.
- 연결을 위해 SET XACT_ABORT ON를 사용하거나 트랜잭션을 시작하고 오류 후 정리하지 않는 모든 저장 프로시저에서 사용하십시오. 런타임 오류가 발생하는 경우 이 설정은 열려 있는 트랜잭션을 중단하고 클라이언트에 대한 제어를 반환합니다. 자세한 내용은 SET XACT_ABORT(Transact-SQL)를 검토하세요.

참고

연결 풀에서 다시 사용할 때까지 연결이 다시 설정되지 않으므로 사용자가 트랜잭션을 연 다음 연결 풀에 대한 연결을 해제할 수 있지만 몇 초 동안 다시 사용되지 않을 수 있으며 이 기간 동안 트랜잭션이 열린 상태로 유지됩니다. 연결을 다시 사용하지 않으면 연결 시간이 초과되고 연결 풀에서 제거될 때 트랜잭션이 중단됩니다. 따라서 클라이언트 애플리케이션이 오류 처리기에서 트랜잭션을 중단하거나 SET XACT_ABORT ON을(를) 사용하여 이러한 잠재적 지연을 방지하는 것이 가장 좋습니다.

주의

SET XACT_ABORT ON 다음에는 오류를 유발하는 명령문 다음에 오는 T-SQL 명령문이 실행되지 않습니다. 이는 기존 코드의 의도된 흐름에 영향을 줄 수 있습니다.

시나리오 3: 해당 클라이언트 애플리케이션이 모든 결과 행을 완료로 가져오지 않은 SPID로 인한 차단

서버에 쿼리를 보낸 후 모든 애플리케이션은 모든 결과 행을 즉시 가져와야 완료됩니다. 애플리케이션이 모든 결과 행을 가져오지 않으면 테이블에 잠금을 남겨 다른 사용자를 차단할 수 있습니다. SQL 문을 서버에 투명하게 제출하는 애플리케이션을 사용하는 경우 애플리케이션은 모든 결과 행을 가져와야 합니다. 구성하지 않은 경우(그리고 그렇게 하도록 구성할 수 없는 경우) 차단 문제를 해결할 수 없습니다. 이 문제를 방지하려면 제대로 작동하지 않는 애플리케이션을 주 OLTP 데이터베이스와 별도로 보고 또는 의사 결정 지원 데이터베이스로 제한할 수 있습니다.

해결 방법:

완료할 결과의 모든 행을 가져오려면 애플리케이션을 다시 작성해야 합니다. 서버 쪽 페이징을 수행하기 위해 쿼리의 ORDER BY 절에서 OFFSET 및 FETCH를 사용하는 것을 배제하지는 않습니다.

시나리오 4: 분산 클라이언트/서버 교착 상태로 인한 차단

기존의 교착 상태와 달리 RDBMS 잠금 관리자를 사용하여 분산 교착 상태를 검색할 수 없습니다. 교착 상태에 관련된 리소스 중 하나만 SQL Server 잠금이기 때문입니다. 교착 상태의 다른 쪽은 SQL Server가 제어할 수 없는 클라이언트 응용 프로그램 수준에 있습니다. 다음 두 섹션에서는 이런 일이 발생할 수 있는 방법과 애플리케이션에서 방지할 수 있는 가능한 방법의 예를 보여줍니다.

예제 A: 단일 클라이언트 스레드를 사용하는 클라이언트/서버 분산 교착 상태

클라이언트에 열려 있는 연결이 여러 개 있고 단일 실행 스레드가 있는 경우 다음과 같은 분산 교착 상태가 발생할 수 있습니다. 여기서 사용된 dbproc라는 용어는 클라이언트 연결 구조를 나타냅니다.

 SPID1------blocked on lock------->SPID2
   /\ (waiting to write results back to client)
   | 
   | |
   | | Server side
   | ================================|==================================
   | <-- single thread --> | Client side
   | \/
   dbproc1 <------------------- dbproc2
   (waiting to fetch (effectively blocked on dbproc1, awaiting
   next row) single thread of execution to run)

위에 표시된 경우 단일 클라이언트 애플리케이션 스레드에는 두 개의 열린 연결이 있습니다. dbproc1에서 SQL 작업을 비동기적으로 제출합니다. 즉, 계속하기 전에 반환할 호출을 기다리지 않습니다. 그런 다음, 애플리케이션은 dbproc2에서 다른 SQL 작업을 제출하고 결과를 대기하여 반환된 데이터 처리를 시작합니다. 데이터가 다시 반환되기 시작하면(dbproc가 먼저 응답하는 경우 모두 dbproc1이라고 가정) 해당 dbproc에서 반환된 모든 데이터를 완료하도록 처리합니다. SPID2가 보유한 잠금에서 SPID1이 차단될 때까지 dbproc1의 결과를 가져옵니다(두 쿼리가 서버에서 비동기적으로 실행되기 때문). 이 시점에서 dbproc1은 더 많은 데이터를 위해 무기한 대기합니다. SPID2는 잠금에서 차단되지 않지만 클라이언트 dbproc2로 데이터를 보내려고 시도합니다. 그러나 애플리케이션에 대한 단일 실행 스레드가 dbproc1에서 사용 중이므로 dbproc2는 애플리케이션 계층의 dbproc1에서 효과적으로 차단됩니다. 관련된 리소스 중 하나만 SQL Server 리소스이기 때문에 SQL Server가 감지하거나 해결할 수 없는 교착 상태가 발생합니다.

예제 B: 연결당 스레드가 있는 클라이언트/서버 분산 교착 상태

클라이언트의 각 연결에 대해 별도의 스레드가 있는 경우에도 다음과 같이 이 분산 교착 상태의 변형이 계속 발생할 수 있습니다.

SPID1------blocked on lock-------->SPID2
  /\ (waiting on net write) Server side
  | |
  | |
  | INSERT |SELECT
  | ================================|==================================
  | <-- thread per dbproc --> | Client side
  | \/
  dbproc1 <-----data row------- dbproc2
  (waiting on (blocked on dbproc1, waiting for it
  insert) to read the row from its buffer)

이 경우는 dbproc2 및 SPID2가 한 번에 행 처리를 수행할 의도로 SELECT 문을 실행한다는 점과 동일한 테이블의 INSERT, UPDATE 또는 DELETE 문에 대해 버퍼를 통해 각 행을 dbproc1에 전달하는 점을 제외하면 예 A와 유사합니다. 결국 SPID1(INSERT, UPDATE 또는 DELETE 수행)은 SPID2(SELECT 수행)가 보유한 잠금에서 차단됩니다. SPID2는 결과 행을 클라이언트 dbproc2에 씁니다. 그런 다음 Dbproc2는 버퍼의 행을 dbproc1에 전달하려고 하지만 dbproc1이 사용 중임을 발견합니다(SPID2에서 차단된 INSERT현재를 완료하기 위해 SPID1에서 대기하는 것이 차단됨). 이 시점에서 dbproc2는 SPID2에 의해 데이터베이스 수준에서 SPID(SPID1)가 차단되는 dbproc1에 의해 애플리케이션 계층에서 차단됩니다. 다시 말하지만, 관련된 리소스 중 하나만 SQL Server 리소스이므로 SQL Server가 검색하거나 해결할 수 없는 교착 상태가 발생합니다.

예제 A와 B는 모두 애플리케이션 개발자가 알고 있어야 하는 기본적인 문제입니다. 이러한 경우를 적절하게 처리하려면 애플리케이션을 코딩해야 합니다.

해결 방법:

쿼리 시간 제한이 제공되고 분산 교착 상태가 발생하면 시간 제한이 발생할 때 끊어집니다. 쿼리 시간 제한 사용에 대한 자세한 내용은 연결 공급자 설명서를 참조하세요.

시나리오 5: 롤백 상태의 세션으로 인한 차단

종료되거나 사용자 정의 트랜잭션 외부에서 취소된 데이터 수정 쿼리가 롤백됩니다. 이는 클라이언트 네트워크 세션 연결 끊김의 부작용으로 또는 요청이 교착 상태의 희생양으로 선택된 경우에도 발생할 수 있습니다. 이는 종종 ROLLBACK command를 나타낼 수 있는 sys.dm_exec_requests 출력을 관찰하여 식별할 수 있으며 percent_complete 열에 진행률이 표시될 수 있습니다.

종료되거나 사용자 정의 트랜잭션 외부에서 취소된 데이터 수정 쿼리가 롤백됩니다. 클라이언트 컴퓨터 다시 시작 및 네트워크 세션 연결 끊김의 부작용으로도 발생할 수 있습니다. 마찬가지로 교착 상태 희생양으로 선택된 쿼리는 롤백됩니다. 데이터 수정 쿼리는 처음에 적용된 변경 내용보다 더 빠르게 롤백할 수 없는 경우가 많습니다. 예를 들어, DELETE 문, INSERT 문 또는 UPDATE 문이 한 시간 동안 실행된 경우 롤백하는 데 적어도 1시간이 걸릴 수 있습니다. 변경 내용을 롤백해야 하거나 데이터베이스의 트랜잭션 및 물리적 무결성이 손상되기 때문에 이 동작이 예상됩니다. 이 문제는 발생해야 하므로 SQL Server는 SPID를 골든 상태 또는 롤백 상태로 표시합니다(즉, 종료되거나 교착 상태의 희생양으로 선택될 수 없음). 이는 ROLLBACK 명령을 나타낼 수 있는 sp_who 출력을 관찰하여 식별할 수 있는 경우가 많습니다. sys.dm_exec_sessions의 status 열은 ROLLBACK 상태를 나타냅니다.

참고

가속 데이터베이스 복구 기능을 사용하는 경우 긴 롤백은 흔치 않습니다. 이 시스템은 SQL Server 2019에 도입되었습니다.

해결 방법:

세션이 변경된 롤백을 완료하기를 기다려야 합니다.

이 작업의 중간에 인스턴스가 종료되면 데이터베이스는 다시 시작할 때 복구 모드에 있으며 열려 있는 모든 트랜잭션이 처리될 때까지 액세스할 수 없게 됩니다. 시작 복구는 기본적으로 트랜잭션당 걸리는 시간이 런타임 복구와 같으며 이 기간 동안 데이터베이스에 액세스할 수 없습니다. 따라서 롤백 상태에서 SPID를 수정하도록 서버를 강제로 중단하는 것은 비생산적인 경우가 많습니다. 가속 데이터베이스 복구를 사용하도록 설정한 SQL Server 2019에서 이런 현상이 발생하면 안 됩니다.

이러한 상황을 방지하려면 OLTP 시스템에서 사용량이 많은 시간 동안 대규모 일괄 쓰기 작업이나 인덱스 만들기 또는 유지 관리 작업을 수행하지 마세요. 가능한 경우 활동이 적은 기간 동안 이러한 작업을 수행합니다.

시나리오 6: 분리된 트랜잭션으로 인한 차단

이는 일반적인 문제 시나리오이며 시나리오 2와 부분적으로 겹칩니다. 클라이언트 애플리케이션이 중지되거나 클라이언트 워크스테이션이 다시 시작되거나 일괄 처리 중단 오류가 발생하면 이러한 모든 작업이 트랜잭션을 열어 둘 수 있습니다. 이 상황은 애플리케이션이 애플리케이션 FINALLY또는CATCH 블록에서 트랜잭션을 롤백하지 않거나 그렇지 않으면 이 상황을 처리하지 않는 경우에 발생할 수 있습니다.

이 시나리오에서는 SQL 일괄 처리 실행이 취소된 동안 애플리케이션에서 SQL 트랜잭션을 열어 둡습니다. SQL Server 인스턴스의 관점에서 볼 때 클라이언트는 여전히 존재하는 것처럼 보이며 획득된 모든 잠금은 계속 보존될 수 있습니다.

분리된 트랜잭션을 보여 주려면 존재하지 않는 테이블에 데이터를 삽입하여 일괄 처리 중단 오류를 시뮬레이션하는 다음 쿼리를 실행합니다.

CREATE TABLE #test2 (col1 INT);
INSERT INTO #test2 SELECT 1;
go
BEGIN TRAN
UPDATE #test2 SET col1 = 2 where col1 = 1;
INSERT INTO #NonExistentTable values (10)

그런 다음, 동일한 창에서 이 쿼리를 실행합니다.

SELECT @@TRANCOUNT;

두 번째 쿼리의 출력은 트랜잭션 개수가 1임을 나타냅니다. 트랜잭션에서 획득한 모든 잠금은 트랜잭션이 커밋되거나 롤백될 때까지 계속 유지됩니다. 일괄 처리가 쿼리에 의해 이미 중단되었으므로 이 쿼리를 실행하는 애플리케이션은 여전히 열려 있는 트랜잭션을 정리하지 않고 동일한 세션에서 다른 쿼리를 계속 실행할 수 있습니다. 세션이 종료되거나 SQL Server 인스턴스가 다시 시작될 때까지 잠금이 유지됩니다.

해결 방법:

이 조건을 방지하는 가장 좋은 방법은 특히 예기치 않은 종료의 경우 애플리케이션 오류/예외 처리를 개선하는 것입니다. 애플리케이션 코드에서 Try-Catch-Finally 블록을 사용하고 예외가 발생한 경우 트랜잭션을 롤백해야 합니다.
트랜잭션을 시작하고 오류가 발생한 후 정리하지 않는 저장 프로시저 또는 세션에 대해 SET XACT_ABORT ON 사용을 고려하세요. 배치를 중단하는 런타임 오류가 발생하는 경우 이 설정은 열려 있는 모든 트랜잭션을 자동으로 롤백하고 제어를 클라이언트에 반환합니다. 자세한 내용은 SET XACT_ABORT(Transact-SQL)를 검토하세요.
리소스를 적절하게 정리하지 않고 연결이 끊어진 클라이언트 애플리케이션의 분리된 연결을 확인하려면 KILL 명령을 사용하여 SPID를 종료할 수 있습니다. KILL(Transact-SQL)을 참조하세요.

이 KILL 명령은 SPID 값을 입력으로 사용합니다. 예를 들어 SPID 9를 종료하려면 다음 명령을 실행합니다.

KILL 99

참고

KILL 명령 검사 사이의 간격으로 인해 KILL 명령이 완료되기까지 최대 30초가 걸릴 수 있습니다.

SQL Server 차단 문제 이해 및 해결

목표

차단이란

애플리케이션 및 차단

차단 문제 해결

차단 정보 수집

DMV에서 정보 수집

확장 이벤트에서 정보 수집

일반적인 차단 시나리오 식별 및 해결

차단 데이터 분석

일반적인 차단 시나리오

자세한 차단 시나리오

시나리오 1: 실행 시간이 긴 일반적으로 실행되는 쿼리로 인한 차단

시나리오 2: 커밋되지 않은 트랜잭션이 있는 절전 모드 SPID로 인한 차단

시나리오 3: 해당 클라이언트 애플리케이션이 모든 결과 행을 완료로 가져오지 않은 SPID로 인한 차단

시나리오 4: 분산 클라이언트/서버 교착 상태로 인한 차단

예제 A: 단일 클라이언트 스레드를 사용하는 클라이언트/서버 분산 교착 상태

예제 B: 연결당 스레드가 있는 클라이언트/서버 분산 교착 상태

시나리오 5: 롤백 상태의 세션으로 인한 차단

시나리오 6: 분리된 트랜잭션으로 인한 차단

참고 항목

피드백

피드백

추가 리소스